December 2, 2008 at 10:14 am
I am attempting to export records to a text file with three additional derived columns. I am getting an error after about 200k of about 1.3 million records have been loaded. The error is a data conversion one in which the destination text file cannot accept the data from the source. In this case it is simply a province field which is expected to be 3 characters in length improperly populated with a city name with more than 3 characters.
I have added a data flow destination (OLE DB Destination object) to capture redirected errors. I have set the OLE DB Source error output to redirect all columns on Error to the destination file. My execution of the file still ends in failure at the 200k mark approx. and no records are redirected. I have the OLE DB Source Data access mode set to SQL Command and the OLE DB Destination Data access mode set to Table or view.
Any ideas on what I am doing wrong?
Thanks in advance for the help.
December 2, 2008 at 10:39 am
I have found the problem with using an error redirect on a destination is that it usually throws an error before it has a chance to re-direct the row.
What I would do in this situation is to use a data conversion transformation before the destination, you can then use this to specify the data type and lengths, and add a re-direction for any truncation that may occur. I find this is a lot safer than relying on the destination to reject the rows.
December 2, 2008 at 11:13 am
An SSIS destination will redirect error rows. I use it all the time. There can be an issue if you use Fast Load though.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 2, 2008 at 11:19 am
Thanks Steveb that did the trick
December 2, 2008 at 11:20 am
Hey Jack thanks for your response yes I had the data access mode set to Table or view. Steve's answer was able to resolve my issue.
Cheers,
December 2, 2008 at 11:28 am
Mark,
I think I misread your original post. Were you trying to redirect errors from the Source Component or at the Destination?
I've never had an issue with the a truncation error at the destination causing a redirect of rows to fail. In fact whenever I have had source data defined larger than the destination the Package will not run at all because of a validation error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 2, 2008 at 11:43 am
No I think you read it right my source data had a column defined as a Varchar(50) and the destination expected string of column length 3. The automation would run until it reached around the 200k mark (out of 1.3 million) and then fail. Adding the data conversion object just before the flat file destination object allowed the option to redirect the rows to OLE DB destination object (which I couldn't do with the flat file destination object). It moved 5 rows which were causing the trouble and allowed the rest of the records to be processed.
Regards,
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply