question

sharon avatar image
sharon asked

SSIS - updating table

I have Excel sheet file to update customer’s addresses in ‘CustomerAddress’ table. And, I created SSIS package to dump the excel file to temp table. But, how can I update the actual table using only SSIS. Please Help. Thanks.

ssist-sqlexcel
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

you will need to identify the key column in both tables and then join them on that in your update query.

To be more accurate we will need some details about the tables but something like this should do it:

Update Prod_Table
set Prod_Table.colA = Temp_Table.colA,
Prod_Table.colB = Temp_Table.colB,
Prod_Table.colC = Temp_Table.colC
from Prod_Table
INNER JOIN Temp_Table on Prod_Table.KeyCol = Temp_Table.KeyCol
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Daniel Ross avatar image
Daniel Ross answered

You should'nt do updates on tables in the data flow of the package...Doing an OLEDB command update creates individual commands for each row in the data flow.

Do what you are doing, creating a temp table, then create an Execute SQL TASK in the control flow and connect from the data flow task that created the temp table.

Then the SQL will be very similar to what Jack wrote, but this code checks the columns of the temp table and only updates those with different value(s).

Update c
set c.address1=t.address1,
c.address2=t.address2,
c.suburb=t.suburb,
c.state=t.state,
c.postCode=t.postCode
from cusAddress c
inner join temp_address t on c.cus_id=t.cus_id
where c.address1<>t.address1 or c.address2<>t.address2 or c.suburb<>t.suburb
or c.state<>t.state or c.postCode<>t.postCode
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JayaKrishna avatar image
JayaKrishna answered

Use executesqltask to update the table in the database.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.