Menu
I am redirecting rows from a flat file source to a flat file destination. The default metadata in the redirected rows are:
What I get in the output flat file is the source row (nice) and the error code (not nice, ex. -1071628249) and the error column (not nice since it's the internal ID of the column).
How can I transform the rows to output the error message (e.g. "The data was truncated.") and the column name as defined in the flat file source?
In other words, instead of ...,-1071607675,10 I'd like to see:
...,The data was truncated,Firstname
or alternatively (if the previous is not possible);
...,DTS_E_FLATFILESOURCEADAPTERSTATIC_TRUNCATED,Firstname.
878
Go to the Mapping tab and map the souce and destination columns as below: Go to the Error Output tab, and select 'Redirect row' in the Error column as below and click OK. Go back to the Data Flow Task to configure the Script Component. Right click on the GetErrorDetails task and select the Edit option.
Drag a Flat File Destination onto the Data Flow and connect the red error path output from the OLE DB Destination onto the Flat File Destination. When the Configure Error Output window pops up, change the Error handling dropdown to Redirect Row and click OK to save your changes.
Adding OnError Event Handler to SSIS Package If you click on the Event Handlers tab in the package, you will see a drop down for the different Event Handlers as shown below. I am going to select OnError for the Package and add an Execute SQL Task to the Event Handlers section as shown below.
Error message list is in the following location: MSDN, Integration Services Error and Message Reference https://docs.microsoft.com/en-us/sql/integration-services/integration-services-error-and-message-reference?view=sql-server-ver15
And column Id Number can be found in SSIS's Data Flow Task: select the task component that generates the error, Advanced Editor, 'Input and Output Properties' tab, External Columns Properties.
165
Part of the question (adding the error description) can be achieved with a script component. This is described in Enhancing an Error Output with the Script Component.
It seems that the Dougbert blog has a solution to adding the column name, but it's far from simple. I'm amazed this is so difficult to do in SSIS; you'd think it was a basic need to know the name of the source and column.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
