SSIS OLE DB Command fatal error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS OLE DB Command fatal error

Author

Topic

stephe40
Posting Yak Master

218 Posts

Posted - 2008-03-24 : 13:27:15

I have a pretty simple senario.

In a data flow task, I have a OLE DB Source, which is a simple select statement. That is then connected to a OLE DB Command object, which executes a stored procedure on a different server, with the columns of the source as parameters to the stored procedure. I then have that connected to another OLE DB Command object, that will update the row in the source, marking it processed if there wasn't an error (no errors raised) in the stored procedure.

This seems to work just fine. Rows that had errors will error out, but I have the max allowed errors set very high to prevent the task from stopping. The problem that I have noticed is that if the very last row of the original source has an error, the component will fail with a fatal error and not go back and mark the rows that did succed as processed.

The fatal error looks like this:
Error: 0xC0047022 at OrderItems, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Insert OrderItem" (97) failed with error code 0xC0202009. 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. There may be error messages posted before this with more information about the failure.

At this point I really believe this is a bug in SSIS, because if the last row succeeds, reguardless of how many rows failed in the middle, the last step runs, and updates the source as processed for the rows that did not fail. But if the last row fails, it doesn't execute the next step.

Has anyone experienced this before. I really hope there is a minor configuration change that can be made so I do not have to redesign the package. My only alternative at this point is to redesign the stored procedure to not raise an error, and return any errors in an output parameter, and then use a conditional split to determine what rows to go back and update at the source, and then rather than relying on the package output to see the errors, I'll have to write the errors to a table somewhere.

- Eric

mbrimble
Starting Member

1 Post

Posted - 2008-10-23 : 17:12:35

Eric,
Did you ever solve your problem?
I have observed the same behaviour in one of my SSIS packages.
I am processing many rows and handling all my errors in an event handler that collects all the errors for later processing. I use a script like this;

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()

If (CStr(Dts.Variables("sErrorsVariable").Value) = "") Then
Dts.Variables("sErrorsVariable").Value = "Errors for file " + CStr(Dts.Variables("CurrentDataFilePath").Value) + ". The file has been moved to the ERRORS folder." + vbCrLf + vbCrLf + CStr(Dts.Variables("sErrorsVariable").Value) + vbCrLf + CStr(Dts.Variables("System::ErrorCode").Value) + " " + CStr(Dts.Variables("System::ErrorDescription").Value) + vbCrLf
Else
Dts.Variables("sErrorsVariable").Value = CStr(Dts.Variables("sErrorsVariable").Value) + vbCrLf + CStr(Dts.Variables("System::ErrorCode").Value) + " " + CStr(Dts.Variables("System::ErrorDescription").Value)
End If

Dts.Variables("System::Propagate").Value = False
Dts.TaskResult = Dts.Results.Success

End Sub

End Class

I then do some alternative processing on the task shape if it fails. I send an email alert containing all the errors on the file and then cleanup. This all works fine and the package continues to run. The task where the errors occur is inside a loop which processes subsequent files without stopping but....

As you say if the error occurs on the last insert (or row in my file) I get the following errors appended to my error list;

-1073450974 SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (297) failed with error code 0xC0202009. 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. There may be error messages posted before this with more information about the failure.

-1073450975 SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.

Once again the package continues to run because I have an alternative flow that occurs on error.

I have looked at the OLE_DB shape to try to see why it has problems detecting it has come to the end of the required input rows if the subsequent row insert failed but I cannot see anything to fiddle with. It is almost as if the Dts.TaskResult = Dts.Results.Success is not getting back to the OLE_DB shape.
I have run out of ideas.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources