6

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?

thursdaysgeek
  • 7,696
  • 20
  • 78
  • 115

4 Answers4

8

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.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • 1
    Ok, that works. I created a temp table in my destination db, copied data to it, and then used the update statement to get my one field filled. Delete the temp table, and I'm done. Thank you. – thursdaysgeek May 18 '09 at 21:52
  • 1
    Try always to use an staging table, try to think in blocks of data. If you have 1 million records who needs te be updated it is very slow to update each record with an update statement. Therefore you should use 1 single table and statement to do you updates. – JSC May 29 '09 at 07:38
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]
D3vtr0n
  • 2,774
  • 3
  • 33
  • 53
0

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.

Pieces
  • 2,256
  • 5
  • 25
  • 39
  • 2
    Slowly Changing Dimensions will do the job, but is VERY slow on large amounts of data (due to the fact that it does row by row actions - see JSC's comment above) – Ivan Peevski Oct 09 '11 at 23:39
  • Looked down upon? I think not. Using my UPDATE example, you don't even need SSIS to perform the transaction. It also depends on your network setup and your linked Database configurations. – D3vtr0n Nov 13 '12 at 20:45
0

I would use a lookup transformation No need for staging - no need to write SQL code - it's the way SSIS was meant to be!

BIDeveloper
  • 2,628
  • 4
  • 36
  • 51