July 14, 2009 at 3:18 pm
I have a question on error handling. This may be confusing so bear with me and let me know if I didn't make something clear.
I am moving data from one table to another. Sometimes I get dirty data that won't fit into one or more of the destination columns. The source is Oracle with numeric columns and the destination columns are SQL Server 2005 decimal(19, 8). When that happens I would like to change the values of the bad data to 0 and still send the row through because there are a lot of other columns that we need the data of so we need the record to go through. I would also like to log the error in an error table that would have the key field, the date/time, the column name with the bad data, and the bad data (probably have to convert it from numeric to char), and if there was more then one column of bad data then to have one row per bad column.
I have tried a couple of different things so far:
1. used a derived column to change the value to 0 if it is too big and send the row through, but this didn't give me any error rows to process.
2. used a conditional split to send the good data through and bad data to a derived column to set it to 0 and to send it back to the destination table, but again I couldn't figure out how to log the data in an error table, one row per bad column.
I'm not sure if I am close and going in the right direction or am way off in what I am trying to accomplish.
Any help or direction would be greatly appreciated.
Thank you.
July 14, 2009 at 9:37 pm
Most of the data flow components have a error output pipeline that you can redirect that row to, in that path you can fix the data, after that you can use a multicast to split the error row out into two more pipelines, one is used to get it back into the main pipeline and the other to the error table. You then use a union all with the main pipeline and the one from the error row back together..
CEWII
July 14, 2009 at 10:16 pm
Your idea number 2 sounds like it should work OK, except you'll need a multicast before you send the output to the log table destination: one output to the log table, one back to the destination table.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 15, 2009 at 8:48 am
Thanks for the help. I didn't know about the multicast transformation and that is exactly what I need.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply