November 27, 2008 at 5:13 am
Morning,
I have got an SSIS package that I have a small issue with. All I have is a flat file with three data columns which I pick on a daily basis from an ftp site and insert the data in to live table.
The problem I have is that some of the date may all ready be in the table and because the table doesn’t allow duplicates there is a violation error message and also there is a foreign key constraint as well which will give an error. But I still want to process the valid data and if at all possablile put the error data in a separate table.
I did find a work around by importing the data in to a staging table and then inserting the data via a cursor which works but is a very messy way of doing this. I also try putting a try/catch in the cursor to get the error rows to insert but it got its self stuck in a loop.
So any help with this would be great!!!
Regards
November 27, 2008 at 6:54 am
Lee,
What you are looking for is the Lookup transformation. This will allow you check the destination table to see if that value already exists. You will need to configure the Error Output on the lookup transform to redirect rows, and then you'll actually send your error output (red output arrow) to your output table. If you want to capture the duplicate rows for audit purposes, create a new destination of your choice and send the duplicates (green output arrow) to that destination.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply