I'm using SSIS to migrate data from one system to another. This is a usual extract, transform, cleanse and load type task.
The error handling is critical to get right. E.g. truncation of data on one column should stop that row being loaded but for other columns I might be happy to carry on loading the row but record a warning.
I'm finding the error disposition a bit limiting. I really feel the need for an 'Issue Warning' disposition which will act the same way as 'Ignore Error' in that the row continues being processed but will in addition copy a row to a warning output so that I can write a message to a log file for someone to manually investigate and correct that item of data post the conversion. Alternatively it would be useful to specify a severity (at a column level) when redirecting error output. This way I can put logic into a downstream component which would treat the error row differently depending on the severity of the error.
Am I missing a trick?
There's no built-in switch for enabling this behavior, but you can accomplish it with creative use of error redirection. I use error redirection or a conditional split to identify rows that either cause warnings or errors, flag them appropriately, then send the errors to a logging table. I send the warnings to a multicast that outputs the rows to both a logging table and to a Union All to put them back into the main flow.|||this will work for me, though it's a shame there isn't a built-in feature...|||
Nick Corrie wrote:
this will work for me, though it's a shame there isn't a built-in feature...
Nick,
That could be a good suggestion to make; you can post it at the connect site: http://connect.microsoft.com/VisualStudio/Feedback
No comments:
Post a Comment