SSIS Add Error Logging

SQL Server Integration Services

I use a data flow item containing multiple lookups to normalize a flat file input. The error output I save into a seperate table. This table contains the errors from all lookups. I would like to add an additional error code to identify which lookup caused the error.

How can this be done?
riffrackAsked:
Who is Participating?
 
drydenhoggCommented:
In the data flow, before the raw data goes through a lookup, put the derived column transformation within the flow to add a column specifying which lookup you are about to do, if the lookup fails, the output will include the data you added in the lookup.

You probably have a data source object with a green arrow going directly to a lookup object. Basically divert the green arrow to the dervied colum transform, and then from the transform back to the lookup.

Before each subsequent lookup you can alter the value to indicate which lookup you are about to do.

Within the derived column object you can specify a column name, under the derived column you can say '<add as new column>' and in the expression put the value such as 'CustomerID Lookup' or whatever is appropriate for your application.

When a row then errors on a specific lookup, the lookup which it failed on will be in that derived column, which will of been sent to the error output.
0
 
chapmandewCommented:
Are you referring to which particular lookup task caused the error, or what row from the lookup caused the error?
0
 
drydenhoggCommented:
Bit of a hack but throw a column transform in there and add an additional column to the error output from the lookup specifying which lookup it was that failed.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
riffrackAuthor Commented:
hi drydenhogg
I tried to do that but I receive the following error:
"The component does not allow adding columns to this input or output."

hi chapmandew
yes, I am referring to which particular lookup task caused the error
0
 
drydenhoggCommented:
Do it prior to each lookup, so it appears as a column before you hit the error output.
0
 
riffrackAuthor Commented:
Sorry, but its not quite clear to me what you mean.

I am quite new to SSIS, can you explain it step by step?
0
 
chapmandewCommented:
OK, so each task has a succes and failure workflow right (green and red arrows).  For the failure of each task (red arrow), use an ExecuteSQL statement to log the task name , and the error.  Does that make sense?
0
 
drydenhoggCommented:
This is still a bit of a hack though, it's not pretty and will have a performance penalty.
0
 
riffrackAuthor Commented:
Thanks a lot works fine, I was able to place the derived column after the lookup. That was exactly what I was looking for.
0
 
riffrackAuthor Commented:
Performance is not really an issue, as it will run once a month with less than 100'000 records.

Is there a recommended / elegant solution, which you would refer to as a hack?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.