Problem scheduling processing

When I manually run the IS packages to load the data warehouse and then process the dimensions and cubes, everything works fine.

When I schedule the packages as jobs, eventually the data goes wrong. The jobs are scheduled in the correct order.

Any ideas?

Thanks.

If I get your description right, the SSIS packages are not executed in the correct order. Is that so?

For one you can use serveral other ways to process your dimensions and cubes: You can use ascmd command line utility to process exactly the cubes and dimensions. Then wou can schedule a SQL agent job to run ascmd.

You can also use SQL Agent jobs directly to send processing commands to Analysis Serivces.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

eventually the data goes wrong.

What do you mean by "goes wrong" is it possible that it is just not updated, or not fully updated?

One common trap when scheduling jobs is that usually the scheduled instance runs under a different user account. You should double check that this account has all the appropriate priviledges. Sometimes logging on to your workstation with the same account that the SQL Agent is running under and running the packages interactively can help to highlight any issues. You might find things like network drive mappings are not the same, or privileges to access other databases are not the same your own account, or even that the account in question does not have rights to process the SSAS database.

Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);Nobody is doing this?|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

Have you read through this? http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||The specified package could not be loaded from the SQL Server Database /r/n

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

Nobody is doing this?
|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

Have you read through this? http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||The specified package could not be loaded from the SQL Server Database /r/n

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.
|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed
."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SCSC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/defaul...kb;en-us;818899
http://support.microsoft.com/defaul...kb;en-us;829444
http://support.microsoft.com/defaul...kb;en-us;812995
http://support.microsoft.com/defaul...kb;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step fail
ed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>sql

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SC
SC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/default...b;en-us;818899
http://support.microsoft.com/default...b;en-us;829444
http://support.microsoft.com/default...b;en-us;812995
http://support.microsoft.com/default...b;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SCSC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/default.aspx?scid=kb;en-us;818899
http://support.microsoft.com/default.aspx?scid=kb;en-us;829444
http://support.microsoft.com/default.aspx?scid=kb;en-us;812995
http://support.microsoft.com/default.aspx?scid=kb;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>

problem queryplan xml template query

Hi,
I use a xsd schema to load XML with a complex structure from a database for
using it in an ASP webpage. Somehow the database uses quite a long time to
make a query plan for the template. The second time the template is used the
database responses quickly, also for other data (using other selection
criteria).
The queryplan is lost when there are no calls for some time, or when a small
change is made to de database stucture, so the next time the ASP page is
called users receive a timeout error.
I have checked all the relevant indexes from the tables that are used for
creating the XML.
Is there any way to influence the speed/persistance of the queryplan that is
created for a xml template query?
Any help will be appreceated,
Albert JanI don't think there is. What is happening is that the first time the query
is compiled and then cached. If you don't run it for a while, the query plan
will be purged from the cache and the query will be recompiled. The only way
to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
stored proc and call the stored proc. You can use the SQL Profiler to see
what the query is that is being generated.
Best regards
Michael
"Albert Jan" <awonnink@.hotmail.com> wrote in message
news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
> Hi,
> I use a xsd schema to load XML with a complex structure from a database
> for
> using it in an ASP webpage. Somehow the database uses quite a long time to
> make a query plan for the template. The second time the template is used
> the
> database responses quickly, also for other data (using other selection
> criteria).
> The queryplan is lost when there are no calls for some time, or when a
> small
> change is made to de database stucture, so the next time the ASP page is
> called users receive a timeout error.
> I have checked all the relevant indexes from the tables that are used for
> creating the XML.
> Is there any way to influence the speed/persistance of the queryplan that
> is
> created for a xml template query?
> Any help will be appreceated,
> Albert Jan
>|||Hi Michael,
I had hoped I woudn't have to redesign the solution, because I like the
technique using the template query. But maybe I'll just have to.
Thank you for your answer.
Albert Jan
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:emXkJcUOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> I don't think there is. What is happening is that the first time the query
> is compiled and then cached. If you don't run it for a while, the query
plan
> will be purged from the cache and the query will be recompiled. The only
way
> to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
> stored proc and call the stored proc. You can use the SQL Profiler to see
> what the query is that is being generated.
> Best regards
> Michael
> "Albert Jan" <awonnink@.hotmail.com> wrote in message
> news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
to
for
that
>
>

problem queryplan xml template query

Hi,
I use a xsd schema to load XML with a complex structure from a database for
using it in an ASP webpage. Somehow the database uses quite a long time to
make a query plan for the template. The second time the template is used the
database responses quickly, also for other data (using other selection
criteria).
The queryplan is lost when there are no calls for some time, or when a small
change is made to de database stucture, so the next time the ASP page is
called users receive a timeout error.
I have checked all the relevant indexes from the tables that are used for
creating the XML.
Is there any way to influence the speed/persistance of the queryplan that is
created for a xml template query?
Any help will be appreceated,
Albert Jan
I don't think there is. What is happening is that the first time the query
is compiled and then cached. If you don't run it for a while, the query plan
will be purged from the cache and the query will be recompiled. The only way
to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
stored proc and call the stored proc. You can use the SQL Profiler to see
what the query is that is being generated.
Best regards
Michael
"Albert Jan" <awonnink@.hotmail.com> wrote in message
news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
> Hi,
> I use a xsd schema to load XML with a complex structure from a database
> for
> using it in an ASP webpage. Somehow the database uses quite a long time to
> make a query plan for the template. The second time the template is used
> the
> database responses quickly, also for other data (using other selection
> criteria).
> The queryplan is lost when there are no calls for some time, or when a
> small
> change is made to de database stucture, so the next time the ASP page is
> called users receive a timeout error.
> I have checked all the relevant indexes from the tables that are used for
> creating the XML.
> Is there any way to influence the speed/persistance of the queryplan that
> is
> created for a xml template query?
> Any help will be appreceated,
> Albert Jan
>
|||Hi Michael,
I had hoped I woudn't have to redesign the solution, because I like the
technique using the template query. But maybe I'll just have to.
Thank you for your answer.
Albert Jan
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:emXkJcUOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> I don't think there is. What is happening is that the first time the query
> is compiled and then cached. If you don't run it for a while, the query
plan
> will be purged from the cache and the query will be recompiled. The only
way[vbcol=seagreen]
> to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
> stored proc and call the stored proc. You can use the SQL Profiler to see
> what the query is that is being generated.
> Best regards
> Michael
> "Albert Jan" <awonnink@.hotmail.com> wrote in message
> news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
for[vbcol=seagreen]
that
>
>
sql

Problem loading XML Files

I have been trying unsuccessfully to load a directory full of XML files into SQL Server 2005 Standard. I have not been able to make it work, and I would be grateful for any advice.

A simple version of the files that I am trying to load is TEST.NWS just below. I have removed almost all of the content from the files, leaving as a file TEST.NWS. (Hopefully I have not cut out too much.) This version of the test file leaves me with the same error messages I have with the full file. I have a directory with many such files and each file is much too big to edit manually.

Start TEST.NWS--

<?xml version="1.0" encoding="UTF-8"?>

<NewsML Version="1.2">

<NewsComponent xml:lang="en-us" Duid="NC001">

</NewsComponent>

</NewsML>

<?xml version="1.0" encoding="UTF-8"?>

<NewsML Version="1.2">

<NewsComponent xml:lang="en-us" Duid="NC002">

</NewsComponent>

</NewsML>

END-

The schema I am using is here: http://www.newsml.org/dl.php?fn=NewsML/1.2/specification/NewsML_1.2.xsd

I also tried this schema: http://www.nitf.org/IPTC/NITF/3.3/specification/schema/nitf-3-3.xsd

One version of the steps that I follow are here: http://msdn2.microsoft.com/en-us/library/ms139886.aspx At stage 8 of the "how to" recipe, when I click on the Columns, I get an error. The wording of the error depends on which of the two schemas I use. If I instead click next I also get an error. Clearly I am doing something wrong. Any suggestion for how to fix this will be most welcome. Thanks.

mzfrank,

Please you please copy and paste the error messages you are getting and optional the T-SQL script you are running?

Thanks,

Galex

|||

I was not using a script. I was following the MSDN "How to: Extract Data Using the XML Source" it uses SSIS rather than a T-SQL script. (Is this a task better done using T-SQL rather than SSIS?)

If I use NewsML_1.2.xsd as the schema I get:

Error at Data Flow Task [XML Source [1]]: There was an error setting up the mapping. The 'http://www.w3.org/XML/1998/namespace:lang' attribute is not declared.

If I use nitf-3-3.xsd as the schema I get:

Error at Data Flow Task [XML Source [1]]: The XML Source Adapter does not support mixed content model on Complex Types.

If I ask it to infer a schem I get:

Unable to infer the XSD from the XML file. The XML contains multiple namespaces. (Microsoft Visual Studio)

|||

I am having the same issue. I posted in the SSIS forum, but this is what I wrote there:

I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
<xs:attribute name="computerName" type="xs:string" use="optional" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
<xs:attribute name="Model" type="xs:string" use="optional" />
<xs:attribute name="Make" type="xs:string" use="optional" />
<xs:attribute name="BIOS" type="xs:string" use="optional" />
<xs:attribute name="Processor" type="xs:string" use="optional" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
<computerCount QTY="1" />
<computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>

Problem Loading Data From FlatFile Source Data For Column overflowed the disk I/O buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

You can find a lot of posts in this forum about the way SSIS parses flat files. It does that column by column. It means if your column delimiter is missing at column 21 it will continue parsing until that delimiter is found and it will eat all the columns in between and eventually make the data oveflow the reserved buffer.

In this case I would try to find the row where your data is misconigured. You can do that by cutting your file in halfs until you find the place which breaks parsing.

HTH,

Bob

|||

Hi Bob,

I understand the internal working. But then what will be the solution to handle? Again the question is "In case of Bad Data how to clean up the source via SSIS" - already raised by Pradeep here. Kristen's reply is partial. It doesn't solve here as there exist no workaround for skipping these rows. The clue is to use the Ragged right, where the Column size cannot exceed length of 4000 (Limitation to be noted) and splitting hundreds of such columns (if do have) and checking if the length of these individual columns exceed max length and finally mapping them is all not only too laborious but also inefficient.

On the other I tried ignoring or redirecting these rows, but the Flat File Source OLEDB Adapter doesn't seem to handle these rows at all. How will I skip these rows. I am using ForEach Loop loads GBs of data. and probability is that any of the file might contain row corruption.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

Unfortunately, there is no in-box SSIS solution for this. As the current flat file parser cannot identify the broken rows it does not have any way to skip or report errors about them.

You'll have to craft your own solution and there are examples of how other people did it in this forum. It certanly isn't pretty but I cannot offer better, for now.

Thanks,

Bob

Problem Loading Data From FlatFile Source Data For Column overflowed the disk I/O buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

You can find a lot of posts in this forum about the way SSIS parses flat files. It does that column by column. It means if your column delimiter is missing at column 21 it will continue parsing until that delimiter is found and it will eat all the columns in between and eventually make the data oveflow the reserved buffer.

In this case I would try to find the row where your data is misconigured. You can do that by cutting your file in halfs until you find the place which breaks parsing.

HTH,

Bob

|||

Hi Bob,

I understand the internal working. But then what will be the solution to handle? Again the question is "In case of Bad Data how to clean up the source via SSIS" - already raised by Pradeep here. Kristen's reply is partial. It doesn't solve here as there exist no workaround for skipping these rows. The clue is to use the Ragged right, where the Column size cannot exceed length of 4000 (Limitation to be noted) and splitting hundreds of such columns (if do have) and checking if the length of these individual columns exceed max length and finally mapping them is all not only too laborious but also inefficient.

On the other I tried ignoring or redirecting these rows, but the Flat File Source OLEDB Adapter doesn't seem to handle these rows at all. How will I skip these rows. I am using ForEach Loop loads GBs of data. and probability is that any of the file might contain row corruption.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

Unfortunately, there is no in-box SSIS solution for this. As the current flat file parser cannot identify the broken rows it does not have any way to skip or report errors about them.

You'll have to craft your own solution and there are examples of how other people did it in this forum. It certanly isn't pretty but I cannot offer better, for now.

Thanks,

Bob

Problem Loading Data From FlatFile Source Data For Column overflowed the disk I/O buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

You can find a lot of posts in this forum about the way SSIS parses flat files. It does that column by column. It means if your column delimiter is missing at column 21 it will continue parsing until that delimiter is found and it will eat all the columns in between and eventually make the data oveflow the reserved buffer.

In this case I would try to find the row where your data is misconigured. You can do that by cutting your file in halfs until you find the place which breaks parsing.

HTH,

Bob

|||

Hi Bob,

I understand the internal working. But then what will be the solution to handle? Again the question is "In case of Bad Data how to clean up the source via SSIS" - already raised by Pradeep here. Kristen's reply is partial. It doesn't solve here as there exist no workaround for skipping these rows. The clue is to use the Ragged right, where the Column size cannot exceed length of 4000 (Limitation to be noted) and splitting hundreds of such columns (if do have) and checking if the length of these individual columns exceed max length and finally mapping them is all not only too laborious but also inefficient.

On the other I tried ignoring or redirecting these rows, but the Flat File Source OLEDB Adapter doesn't seem to handle these rows at all. How will I skip these rows. I am using ForEach Loop loads GBs of data. and probability is that any of the file might contain row corruption.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

Unfortunately, there is no in-box SSIS solution for this. As the current flat file parser cannot identify the broken rows it does not have any way to skip or report errors about them.

You'll have to craft your own solution and there are examples of how other people did it in this forum. It certanly isn't pretty but I cannot offer better, for now.

Thanks,

Bob