Errors when attempting to use DataReader destination

Hi,
We have an ASP.Net app that consumes data from a SSIS package. I have proved that this works by building a noddy ASP.Net app against it on my local machine.

Unfortunately the same isn't true of our project-wide solution where we get error "Package failed to execute." In our package log I can see that the things that were failing were various property expressions that are in my package. The expressions are all on the SQLStatementSource property of various Exec SQL tasks in my package.

Here's an example of just such an error:
OnError,FR23011958,CT\aeqz,SQL UPDATE StreamHistory,{30BD52FA-8077-4D40-BFA7-248173C34000},{C84EF8C0-4B15-41FC-B205-9FB6B72884AC},26/10/2005 13:46:31,26/10/2005 13:46:31,-1073647612,0x,The result of the expression ""UPDATE dbo.StreamHistory
SET StreamEndDateID = (Year(GETDATE()) * 10000) + (Month(GETDATE()) * 100) + Day(GETDATE())
, StreamEndTimeID = (datepart(hh, GETDATE()) * 100) + datepart(mi, GETDATE())
, StreamEndTMS = GETDATE()
, StatusDetailID = 1
, Duration = DATEDIFF(ss, StreamStartTMS, GETDATE())
WHERE StreamHistoryID = " + TRIM((DT_STR, 4, 1252) @.[Metadata::StreamHistoryID])

" on property "SqlStatementSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Hopefully you can see that (in theory) this expression should get evaluated regardless of where the package is executed from. Remember, it is not the SQL query that fails to execute - it is the construction of the SQL statement inside an expression that fails.

So simply, has anyone experienced something similar? i.e. Expressions fail to get evaluated when a package is executed from an ASP.Net app (or elsewhere).

I'm wondering if this is a permissions thing...its damned annoying anyway.

ANY experiences at all of doing something similar would be useful.

Thanks
-Jamie

Jamie,

Would you like to share with me your package and ASP app?

BTW - I don't see how this relates to Data Reader destination. Do you mean to put another subject line instead?|||

Runying Mao wrote:

Jamie,

Would you like to share with me your package and ASP app?

BTW - I don't see how this relates to Data Reader destination. Do you mean to put another subject line instead?

Hi Runying,
Well it consumes data via a datareader. I actually wrote that initial post over the space of about an hour. At the beginning of that hour we figured the problem was in the datareader cos of the problems we've had with it lately (which you know all about ), by the end of the hour we'd learnt more about what it was - i.e. problems with expressions. I just forgot to change the thread title that's all.

We've found out more since. We've built a new windows forms app and copied the code from "Running an Existing Package using Visual Basic" in BOL into the app and used it to run our package. It worked fine.

We then copied and pasted that code line for line into a new project in our solution and it didn't work. Same code, same machine, same user credentials...different result. very very strange. So now we're pursuing the assumption that some sort of solution-wide configuration is causing a problem. Hopefully we'll find out more tomorrow but if not....I'l be on here pleading for help!!

-Jamie|||Hi, I'm working with Jamie on this problem and have found a little more about the problem, but am still no nearer a solution!

If I add a new Web site to our solution and copy in the code to call the SSIS package, everything runs correctly.

However, if I then reference one of the other assemblies in our solution the package fails to execute, even though we're not actually calling anything in the referenced assembly.|||Here's what Tim and I eventually found out.

http://blogs.conchango.com/jamiethomson/archive/2005/10/27/2315.aspx

Weird!!

-Jamie

Errors loading a text file into a Sql Server Destination

I am trying to load 14+ million rows from a text file into local Sql Server. I tried using Sql Server destination because it seemed to be faster, but after about 5 million rows it would always fail. See various errors below which I received while trying different variations of FirstRow/LastRow, Timeout, Table lock etc. After spending two days trying to get it to work, I switched to OLE DB Destination and it worked fine. I would like to get the Sql Server Destination working because it seems much faster, but the error messages aren't much help. Any ideas on how to fix?

Also, when I wanted to try just loading a small sample by specifying first row/last row, it would get to the upper limit and then picked up speed and looked like it kept on reading rows of the source file until it failed. I expected it to just reach the limit I set and then stop processing.

[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

--
[SS_DST tlkpDNBGlobal [41234]] Error: The attempt to send a row to SQL Server failed with error code 0x80004005.
[DTS.Pipeline] Error: The ProcessInput method on component "SS_DST tlkpDNBGlobal" (41234) failed with error code 0xC02020C7. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
...
[FF_SRC DNBGlobal [6899]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

-
After first row/last row (from 1 to 1000000) limit is reached:
[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

When trying to do a MaximumCommit = 1000000. Runs up to 1000000 OK then slows down and then error.
[SS_DST tlkpDNBGlobal [41234]] Error: Unable to prepare the SSIS bulk insert for data insertion.

[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

-
When attempting all in a single batch:
[OLE_DST tlkpDNBGlobal [57133]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.SORT temporary run storage: 156362715561984' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".

The commit size is most likely the problem. SQL Server will be storing those rows in memory until it is time to commit them. I suggest trying different commit sizes until you find one that is optimum.

You tried 1M? Try 500,000 rows - if that works, maybe try more, or if it fails try less. You should find a good value fairly quickly.

if this does not help, let us know more.

Donald Farmer

|||Thanks Donald. I set the MaxInsertCommitSize to 10K and it worked fine.|||

Hi, Donald

I have had the same problem, how / where do i set the commit size ?

Thanks

Agbeko

|||

It wasn't available until SP1. You need to have SP1 installed then you will get MaxInsertCommitSize as a property of SQL Server Destination.

Thanks

Sutha

|||

Thanks for pointing that out Sutha. Remember also that commit size has always been available on the OLEDB Destination with the SQL Server provider.

Donald

|||

Hi, Sutha,

I am new to SQL Server world. Dummy question:

refering to SP1, is it for Windows, SQL Server 2005, or Integration Service?

Thanks

Chuan

|||

They are talking about SP1 for SQL Server 2005

Rafael Salas

|||Thanks Rafael|||

Rafael Salas wrote:

They are talking about SP1 for SQL Server 2005

Rafael Salas

Which includes Integration Services by the way.

John, the only dumb questions are the ones that don't get asked.

-Jamie

Errors loading a text file into a Sql Server Destination

I am trying to load 14+ million rows from a text file into local Sql Server. I tried using Sql Server destination because it seemed to be faster, but after about 5 million rows it would always fail. See various errors below which I received while trying different variations of FirstRow/LastRow, Timeout, Table lock etc. After spending two days trying to get it to work, I switched to OLE DB Destination and it worked fine. I would like to get the Sql Server Destination working because it seems much faster, but the error messages aren't much help. Any ideas on how to fix?

Also, when I wanted to try just loading a small sample by specifying first row/last row, it would get to the upper limit and then picked up speed and looked like it kept on reading rows of the source file until it failed. I expected it to just reach the limit I set and then stop processing.

[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

--
[SS_DST tlkpDNBGlobal [41234]] Error: The attempt to send a row to SQL Server failed with error code 0x80004005.
[DTS.Pipeline] Error: The ProcessInput method on component "SS_DST tlkpDNBGlobal" (41234) failed with error code 0xC02020C7. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
...
[FF_SRC DNBGlobal [6899]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

-
After first row/last row (from 1 to 1000000) limit is reached:
[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

When trying to do a MaximumCommit = 1000000. Runs up to 1000000 OK then slows down and then error.
[SS_DST tlkpDNBGlobal [41234]] Error: Unable to prepare the SSIS bulk insert for data insertion.

[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

-
When attempting all in a single batch:
[OLE_DST tlkpDNBGlobal [57133]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.SORT temporary run storage: 156362715561984' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".

The commit size is most likely the problem. SQL Server will be storing those rows in memory until it is time to commit them. I suggest trying different commit sizes until you find one that is optimum.

You tried 1M? Try 500,000 rows - if that works, maybe try more, or if it fails try less. You should find a good value fairly quickly.

if this does not help, let us know more.

Donald Farmer

|||Thanks Donald. I set the MaxInsertCommitSize to 10K and it worked fine.|||

Hi, Donald

I have had the same problem, how / where do i set the commit size ?

Thanks

Agbeko

|||

It wasn't available until SP1. You need to have SP1 installed then you will get MaxInsertCommitSize as a property of SQL Server Destination.

Thanks

Sutha

|||

Thanks for pointing that out Sutha. Remember also that commit size has always been available on the OLEDB Destination with the SQL Server provider.

Donald

|||

Hi, Sutha,

I am new to SQL Server world. Dummy question:

refering to SP1, is it for Windows, SQL Server 2005, or Integration Service?

Thanks

Chuan

|||

They are talking about SP1 for SQL Server 2005

Rafael Salas

|||Thanks Rafael|||

Rafael Salas wrote:

They are talking about SP1 for SQL Server 2005

Rafael Salas

Which includes Integration Services by the way.

John, the only dumb questions are the ones that don't get asked.

-Jamie

Errors loading a text file into a Sql Server Destination

I am trying to load 14+ million rows from a text file into local Sql Server. I tried using Sql Server destination because it seemed to be faster, but after about 5 million rows it would always fail. See various errors below which I received while trying different variations of FirstRow/LastRow, Timeout, Table lock etc. After spending two days trying to get it to work, I switched to OLE DB Destination and it worked fine. I would like to get the Sql Server Destination working because it seems much faster, but the error messages aren't much help. Any ideas on how to fix?

Also, when I wanted to try just loading a small sample by specifying first row/last row, it would get to the upper limit and then picked up speed and looked like it kept on reading rows of the source file until it failed. I expected it to just reach the limit I set and then stop processing.

[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

--
[SS_DST tlkpDNBGlobal [41234]] Error: The attempt to send a row to SQL Server failed with error code 0x80004005.
[DTS.Pipeline] Error: The ProcessInput method on component "SS_DST tlkpDNBGlobal" (41234) failed with error code 0xC02020C7. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
...
[FF_SRC DNBGlobal [6899]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

-
After first row/last row (from 1 to 1000000) limit is reached:
[SS_DST tlkpDNBGlobal [41234]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".

When trying to do a MaximumCommit = 1000000. Runs up to 1000000 OK then slows down and then error.
[SS_DST tlkpDNBGlobal [41234]] Error: Unable to prepare the SSIS bulk insert for data insertion.

[DTS.Pipeline] Error: The PrimeOutput method on component "FF_SRC DNBGlobal" (6899) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

-
When attempting all in a single batch:
[OLE_DST tlkpDNBGlobal [57133]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Could not allocate space for object 'dbo.SORT temporary run storage: 156362715561984' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".

The commit size is most likely the problem. SQL Server will be storing those rows in memory until it is time to commit them. I suggest trying different commit sizes until you find one that is optimum.

You tried 1M? Try 500,000 rows - if that works, maybe try more, or if it fails try less. You should find a good value fairly quickly.

if this does not help, let us know more.

Donald Farmer

|||Thanks Donald. I set the MaxInsertCommitSize to 10K and it worked fine.|||

Hi, Donald

I have had the same problem, how / where do i set the commit size ?

Thanks

Agbeko

|||

It wasn't available until SP1. You need to have SP1 installed then you will get MaxInsertCommitSize as a property of SQL Server Destination.

Thanks

Sutha

|||

Thanks for pointing that out Sutha. Remember also that commit size has always been available on the OLEDB Destination with the SQL Server provider.

Donald

|||

Hi, Sutha,

I am new to SQL Server world. Dummy question:

refering to SP1, is it for Windows, SQL Server 2005, or Integration Service?

Thanks

Chuan

|||

They are talking about SP1 for SQL Server 2005

Rafael Salas

|||Thanks Rafael|||

Rafael Salas wrote:

They are talking about SP1 for SQL Server 2005

Rafael Salas

Which includes Integration Services by the way.

John, the only dumb questions are the ones that don't get asked.

-Jamie

Errorcodes Ole DB destination error

I send my error output (errorcode , errorcolumn) of an Ole DB destination to another Ole DB destination. How do I know which errordescription belongs to an errorcode. For example errorcode "-1071607683"?

http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

-Jamie

|||

Tnx! Is there also a way to do this with the error column name. So get the column name with the column number?

ErrorCode and ErrorColumn in Excel Destination

I am exporting records with errors to Excel using the Excel Destination tool. The ErrorColumn is a numeric. How do I find out which column it is?

If you want to do this manually (i.e. not programmatically), you can open the Advanced UI for the Excel Dest, and go to the "Input and Output Properties" tab. There, under "Excel Destination Input", you can look through the colums. As you click on each one, look for the "ID" property on the right that has the value you found in the ErrorColumn field of the error row.

Let me know if this helps or not.

Thanks,
Mark|||Hi

How would you do this programmatically? Ideally, I'd like to include the actual column name as another output column in my error destination table. Also, is it possible to obtain the error description as well as the ErrorCode (similar to what you might see in the Execution Results pane when debugging)?

Thanks

Rob|||Hi Rob

I want to do something similar but write out the column name to a table along with the data when rejected. Did you ever figure out how to do this?

Thanks

Marcus|||While it's fairly easy to add an ErrorDescription column on top of the ErrorCode, it's NOT so easy to add an ErrorColumnName on top of the ErrorColumn numeric ID value, because of all the ifs ands and buts that determine whether the column name is readily available or not. (For example, did the preceding component have synchronous or asynchronous outputs, and so forth.)

You can run your error output through a Script Component and try looping through the columns in its InputColumnCollection and VirtualInputColumnCollection looking for the ID value that you've got in the ErrorColumn column to see that this information is usually unavailable.

-Doug