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:
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.
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
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.