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.

score 0 · Answer 1 · answered Mar 10 '18 at 20:25

0

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 '18 at 20:25

Hadi

36,233
13
65
124

Have you tried successfully to create the error handling logic? That is my question. – J Weezy Mar 12 '18 at 19:48
@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. – Hadi Mar 12 '18 at 20:34
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 Mar 12 '18 at 20:36

score 0 · Accepted Answer · answered Mar 27 '18 at 20:59

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.

SSIS Script Component Source: How to add error output

2 Answers2