SSIS upsert from CSV File Source

Question

I'm trying to determine the most effect way to do an upsert in SSIS with CSV source 100+ columns

1 x CSV Source (loop through multiple CSVs same structure 100 + columns) 1 x Staging Table Destination

I have come up with 3 approaches:

1. Using the SSIS lookup component, if record doesn't exist insert otherwise call some SQL script to do the update (row by row) so could be slow.

2. Calling a stored procedure with a merge statement, so CSV to stored procedure (will be row by row) so slow. Also there will 100+ params

3. Using another staging table that gets cleaned out before insert..Then calling a stored procedure to merge the 2 staging tables

I'm leaning towards approach #3 as it's the simplest and cleanest.

Thoughts?

Answer 1

I think the following can be a good way to upsert in SSIS as well:

1. add the database and csv file as a sources
2. sort their values (in preparation for merge join step)
3. Full outer join DB and csv datasets using (Merge Join)
4. Add conditional split to determine which rows will need insert, update and delete based on the condition that you want
5. Insert the rows using DB destination step, and update and delete using DB command steps

More detailed information can be found here

https://www.learnmsbitutorials.net/insert-update-data-using-merge-join-ssis.php

Answer 2

This is going to depend a lot on the sizes of the data (how much is in the DB, how much is in the file, how many matching rows there will likely be that need updating rather than inserting, and how many entirely matching rows that require neither insert nor update there could be) and to an extent your IO subsystem's capabilities – so any answer in the absence of a bit more detail is going to be either an educated guess or a general case that might not entirely apply.

With that in mind:

1. lookup then insert or update

Unless a large amount of the input rows will result in an update (rather than an insert, or nothing) then this is likely to be the best option because the inserts can be done in bulk.

You might be able to do the updates in bulk to by inserting them into a holding table and doing the update into the target table by an UPDATE TargetTable FROM TargetTable JOIN HoldingTable ON KeyValue=KeyValue. A hybrid of the lookup method and holding table methods. Profile this for your data though: the double write (once to holding, once to final target) and extra read may negate any performance benefit of skipping the per-update round-trip to the DB, and result in a bunch of extra transaction log activity that may affect your backups and such.

2. One merge statement per input row

As you identify, this is likely to be the least efficient way by far because there will be a round-trip to the DB for every single row.

3. Insert all into a holding table, merge all in one step

This may win out as simple and easy to maintain, but be careful of the effect the double-write may have on backups and IO contention generally. If the target is a DB you rebuild every time, so doesn't do log backups or anything like, and the IO impact isn't going to affect other users of this DB or other DBs on the same storage, and you aren't likely to hit some of the as yet unresolved issues with MERGE, then go for it.

4. Use both the target table and CSV as sources, merge in SSIS (suggested in Ahmed's answer)

I wouldn't recommend this generally. In some cases where the amount of data in the target table is small, this might not be terrible (and may even perform better if most of the CSV rows result in no change at all, i.e. no INSERT or UPDATE needed), but it will result in reading out the entire target table and if the SSIS package is not running local to the DB sending all that data over the network, where the lookup approach might only need to read a minority of the existing rows.

If you do go with this idea, pull the data out from the DB already in key order (in the source component … ORDER BY CompareKey and use the advanced source editor to mark the data as sorted by that/those columns) instead. Having a SSIS sort component will force a resort in SSIS where the data should be readable in-order much more efficiently at the other side as the desired order is likely following the table's primary key order.

---

tl;dr: done right 1 is likely the most efficient and therefore what I'd generally recommend. But if 3 is simpler for you to write and maintain and the performance is good enough, maybe pick maintainability over performance. But the best answer depends on a few factors that we don't currently know.