Update table using SSIS

Question

I am trying to update a field in a table with data from another table, based on a common key. If it were in straight SQL, it would be something like:

Update EHSIT
set e.IDMSObjID = s.IDMSObjID
from EHSIT e, EHSIDMS s
where e.SITENUM = s.SITE_CODE

However, the two tables are not in the same database, so I'm trying to use SSIS to do the update. Oh, and the sitenum/site_code are varchar in one and nvarchar in the other, so I'll have to do a data conversion so they'll match.

How do I do it?
I have a data flow object, with the source as EHSIDMS and the destination as EHSIT. I have a data conversion to convert the unicode to non-unicode. But how do I update based on the match? I've tried with the destination, using a SQL Command as the Data Access mode, but it doesn't appear to have the source table. If I just map the field to be updated, how does it limit it based on fields matching?

I'm about to export my source table to Excel or something, and then try inputting from there, although it seems that all that would get me would be to remove the data conversion step.

Shouldn't there be an update data task or something? Is it one of those Data Flow transformation tasks, and I'm just not figuring out which it is?

Answer 1

You can use the SQLCommand (with parameters) but you'll effectively be writing an UPDATE for each row. I've found it better to write a staging table on the destination side (either all the data from the remote side or the data determined by SSIS to be changed) and use a single SQL UPDATE to perform the update.

Answer 2

You can always use the full database name.

UPDATE server1.databasename.EHSIT
SET E.IDMSOBJID = S.IDMSOBJID
FROM server1.databasename.EHSIT E, server2.databasename.EHSIDMS S
WHERE E.SITENUM = S.SITE_CODE



[serverpath].[database].[databaseobject]

Answer 3

I found that using a slowly changing dimension, with the business key as the value you are matching on and the other attributes you want updated as changing values seems to work.

Devtron, I have tried this and get errors about the server path, there are a lot of /'s in it. I was also told that this is highly looked down upon, especially if it is an operation that occurs a lot.