October 15, 2009 at 1:00 am
Hi there my name is Ryan. I have some question for SSIS.
I have to built some SSIS package that transfer oracle data to SQL server database. The transfer process is quite simple just using queries that retrieve table from oracle and the transfer it to SQL Server. But the requirement is what concerns me. I have to built some package that if any error occured and the the data that was transferred some how become incorrect I must rerun the package again, when doing rerun I have to make sure the data is not duplicating, that's the problem, how can I make the data is not duplicating when I run the package manually after some let's say an error occur or something bad happen, can you guys give me some advice. Can you guys help me please. Thanks 🙂 .
Regards.
October 15, 2009 at 8:38 am
If all of the data from your source is transferred to the destination regularly, the simple answer would be to truncate the destination tables before executing the data flow tasks.
You could also add a Lookup to the data flow right after your source query that will query the destination table for existing records. You'll want to ignore the rows from the source where a match is found in the lookup and process only those source rows where an existing record has not been found.
October 15, 2009 at 8:42 am
One possibility is to redirect error rows into a separate error table for review. If you have key fields on the data that you are importing, maintain these keys in the error table, and then you can join on them for attempted re-processing.
October 15, 2009 at 9:45 am
I think you want to look at transactions in SSIS, all succeed or all fail..
http://www.mssqltips.com/tip.asp?tip=1585
CEWII
October 15, 2009 at 12:49 pm
I would go with a combination of the above suggestions. First, design your packages with the appropriate transaction behavior to meet your needs. Then, use a lookup transformation inside your data flows to ensure that you are only inserting new rows.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply