SSIS Script Component Source: How to add error output

Question

I have created a script component (i.e., data flow) that is of the source variety. How do I create an error output that captures both the errorColumnName and errorDescription?

The only implementation of creating an error output for a script component that I have found assumes that the component is a transformation. The key difference between the two, at least from what I can tell, is that the transformation has access to the Process_Input(Row), whereas the source component is creating a row, therefor Process_Input and its Row member are not available.

Context: The source component fails on certain columns that are string types with an error that they exceed the buffer. But, no information is provided with respect to either the error column name or the error value. I can identify the error column(s) by sending the data to a flat file output, but I would prefer not to have to do it this way.

Hadi · Accepted Answer · 2018-03-10 20:25:03Z

1

I don't think there is a way to do this in SSIS without writing your own error handling logic inside the script component. You can check the following article to get more information:

Simulating an Error Output for the Script Component

answered Mar 10, 2018 at 20:25

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

J Weezy Over a year ago

Have you tried successfully to create the error handling logic? That is my question.

Hadi Over a year ago

@JWeezy yes, in many cases i written my own error handling logic in the ssis script component. I enclosed every column manipulation code inside a Try...Catch clause, in the Catch section i Thrown a new exception in which i specified the error message and error column, then all the whole code was enclosed inside a Try Catch clause, in the catch section i redirect the error to a Second Output Buffer that i created in the script component.

J Weezy Over a year ago

Can you please provide the code in the catch block? And, to be sure, you have done this for a script component of the source type, instead of transformation?

J Weezy · Accepted Answer · 2018-03-27 20:59:56Z

0

So, it does not yet appear to be possible to redirect an error row with both the ErrorColumnName and ErrorDescription. A workaround for this is a two step process.

Set the output column width for all varchar/nvarchar fields to the max width of 8,000.
Add a Data Conversion component and have it convert all of the fields listed in step 1 to the correct width as it is set on the database.

You can either set the component to fail or redirect the error rows. Redirecting may be better so that it is possible to catch all error rows on the first run as opposed to piecemealing it one at a time.

answered Mar 27, 2018 at 20:59

J Weezy

4,0695 gold badges44 silver badges102 bronze badges

Collectives™ on Stack Overflow

SSIS Script Component Source: How to add error output

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related