CODEWITHSUNDEEP
ssissql-updateprimary-key
Dee
Dee

Reputation: 111

SSIS- Update few columns of a row for which the primary key already exists

The following is an example to better explain my scenario. My database table has following columns Column -1: Operating_ID (which is the primary key) Column -2: Name Column -3: Phone Column -4: Address Column -5: Start Date Column -6: End Date

The values for the columns 1,2,3,4 come from an extract and this extract is pushed to the database daily using SSIS data flow task

The values for the columns 5 and 6 are user inputted from a web applicxation and saved to the database.

Now in SSIS process instead of throwing violation of primary key error, i need to update Columns 2,3,4 if the primary key i.e column 1 already exists. First i considered replace but that deletes the user inputted data columns 4,5. I would like to keep the data in columns 4,5 and update columns 2,3,4 when column 1 already exists.

Upvotes: 0

Views: 1058

Answers (2)

HLGEM
HLGEM

Reputation: 96552

This is what I would do. I would put all the data in a staging table. Then I woudl use a data flow to insert the new records and the source of that dataflow would be the staging table with a not exists clause referencing the prod table.

Then I would use an Execute SQL task in the control flow to update the data for existing rows.

Upvotes: 0

Raj More
Raj More

Reputation: 48018

Do a LOOKUP for Operating_ID. Change that lookup from "FAIL ON NOT FOUND" to "REDIRECT ROWS TO NO MATCH"

If match not found, go to INSERT

If match found, go to UPDATE. You can run OLAP commands to update, but if it is a large set of data, you are better off putting into a table, and doing an UPDATE with a JOIN

Upvotes: 1

Related Questions