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.
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
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
Use executesqltask to update the table in the database.
No one has followed this question yet.