brian.geregach (4/16/2013)
First off, my appologies, I am a newbie to programming and SQL.Setup:
I have an SSIS package that inserts rows to a database. On occasion I get a constraint error on one of my reference tables. So far it is because of missing data.
What I want to do:
I want to redirect the row, grab the data and insert it into the reference table then reprocess the row.
Questions:
A) is this possible? I have looked at doing a lookup task prior to the row being inserted to the database. I have also looked at using a sql task in my error handling, but having issues with the code (see B).
B) I need help with the code. How do I pull the information from that single row then insert it into the database? Do I use a variable? Is this a select statement?
Any help would be greatly appreciated.
Thank you
Brian
There is more than one way to solve this. Here are three:
1) (Pure SSIS)
Use a lookup task to check for existence. When matched then continue as currently. When not matched:
a) Insert a suitable row into the reference table using a OLEDB command
b) Insert to child table as usual.
2) (Hybrid)
Use a lookup as above. When matched, all OK. When not matched
a) Direct to a staging table
b) At the end of the dataflow, run a stored proc to process and insert the rows in the reference and staging tables as required.
3) (Use SSIS as little as possible)
Use the solution suggested by Mansfield.
I would expect (2) to be the fastest, (1) is the most elegant and (3) is probably easiest to set up, maintain and troubleshoot - for SQL folks anyway. In my opinion, of course.
One problem with (1) is that you cannot use a cached lookup - so performance is likely to be average at best.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.