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.


  • 1
    I would use method 3 as well for the same reasons. – Jonathan Fite Mar 17 '17 at 12:57
  • 1
    We use method 3, but you should know that there are a few bugs with the MERGE statement: sqlmag.com/sql-server/merge-statement-tips and mssqltips.com/sqlservertip/3074/… We also have a stored procedure to generate the MERGE statement, and we use a TABLOCK, to avoid multi-user problems. Maybe overkill? – Henrik Staun Poulsen Mar 17 '17 at 13:38
  • Thanks, I've seen that before, are those still issues in SQL Server 2016? Also I wont be running this in parallel with any other job, it will execute on schedule so i hopefully wont have concurrency issues. – davey Mar 17 '17 at 13:45
  • We usually load all the records for all the files into one staging table, then delete the older duplicate files, usually based on some date field. Would that work in your CASE? – John Jun 1 '17 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.