Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I convert the ErrorCode and ErrorColumn in SSIS to the error message and column name?

Tags:

ssis

I am redirecting rows from a flat file source to a flat file destination. The default metadata in the redirected rows are:

  • The original flat file source row
  • The ErrorCode
  • The ErrorColumn

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.

like image 878
Bernhard Hofmann Avatar asked Oct 02 '12 15:10

Bernhard Hofmann


People also ask

How do I find the error column name in SSIS?

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.

How do I redirect error rows to an error output in SSIS?

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.

How do I capture an error message in SSIS?

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.


2 Answers

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.

like image 165
Perdi Estaquel Avatar answered Sep 28 '22 09:09

Perdi Estaquel


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.

like image 41
Bernhard Hofmann Avatar answered Sep 28 '22 08:09

Bernhard Hofmann