Update Rows in SSIS OLEDB Destination
I have a data flow process where I have an OLEDB Source and an OLEDB Destination like below:
Source merges data from two staging tables and returns a result set (say, 50K rows). These 50K rows are present in the destination table as well but are old data.
SELECT * FROM staging1
UNION
SELECT * FROM staging2
Generally, in the OLEDB destination we insert the returned dataset from the source to destination table, but in my case I have to update the old 50K rows with these new 50K rows.
Kind of a bulk update.
Can anyone please let me know how I can do that? I appreciate your help.
Answer
You can't do a bulk-update in SSIS within a dataflow task with the OOB components.
The general pattern is to identify your inserts, updates and deletes and push the updates and deletes to a staging table(s) and after the Dataflow Task, use a set-based update or delete in an Execute SQL Task. Look at Andy Leonard's Stairway to Integration Services series. Scroll about 3/4 the way down the article to "Set-Based Updates" to see the pattern.
Stage data
Set based updates
You'll get much better performance with a pattern like this versus using the OLE DB Command transformation for anything but trivial amounts of data.
If you are into third party tools, I believe CozyRoc and I know PragmaticWorks have a merge destination component.