Insert rows into SQL Server table with a primary key with SSIS

glop11294484 0
Viewed 35

I have an SSIS package that I use to pass data from an Excel workbook into the SQL Server table:

enter image description here

My Excel file grows constantly with new records, therefore I've defined a primary key in the SQL Server table to avoid inserting duplicates, but basically I'm inserting the whole workbook each time.

I now have a problem, where either the whole package fails completely because it attempts to pass duplicate values into a table with PK, or if I set the Error Output of the destination to "Redirect row", the package gets executed successfully with the following message:

Data Flow Task, SSIS.Pipeline: "OLE DB Destination" wrote 90 rows

but no new rows are actually added to the table.

If I remove the PK constraint and add a trigger to remove duplicates on insert, it would work, but I would like to know the proper way to do this.

ssis sql-server
1 Answers

To make the design "work," with an error table, change your batch commit size to 1 in the OLE DB Destination. What's happening is that it's trying to commit the 90 rows but as there's at least 1 bad row in there, the whole batch fails.

The better approach will be to add a Lookup Component between the data conversion and the destination. The output of the Lookup will be the "No Match Found" output path and that is what will feed into the OLE DB Destination. The logic is that you're going to attempt to lookup the existing key in the target table. The No Match Found is what it sounds like, the row doesn't exist so therefore, shove it into the table and you won't have a PK conflict*.

* I still got a PK conflict but it's not there. In this case, you have duplicates/repeated keys in your source data and the same issue with regard to batch size is obscuring it. We're adding 2 rows with PK 50. PK 50 doesn't exist so it passes the lookup but the default batch size means both of those rows are going to be inserted in a single shot. Which violates referential integrity and then gets rolled back.

billinkc0
Related