Geek Answers Handbook

Refresh a table using SSIS

I am trying to update a field in a table with data from another table based on a shared key. If it were in direct 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 am trying to use SSIS to upgrade. Oh, and sitenum / site_code are varchar in one and nvarchar in the other, so I will have to do the data conversion to fit each other.

How to do it?
I have a data flow object whose source is EHSIDMS and the destination is EHSIT. I have data conversion to convert unicode to non-unicode. But how do I update based on compliance? I tried with the assignment using the SQL command as the data access mode, but does not seem to have the source table. If I just map the field being updated, how does it limit it based on field matches?

I am going to export the source table to Excel or something like that, and then try to execute input from there, although it seems that all I got is to delete the data conversion step.

Should there be an update data job or something else? Is this one of the tasks of transforming a data stream, and I just don’t understand what it is?

+6
sql-server-2005 ssis
source share
4 answers

You can use SQLCommand (with parameters), but you will effectively write UPDATE for each row. I found it better to write an intermediate table on the destination side (either all the data on the remote side, or the data defined by SSIS that needs to be changed), and use one SQL UPDATE to perform the update.

+8
source share

You can always use the fully qualified 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]
+2
source share

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

Devtron, I tried this and got errors on the server path, there are many in it. I was also told that this looks a lot around, especially if it is an operation that happens a lot.

0
source share

I would use a search transformation. No need to create - no need to write SQL code - this is the way SSIS should have been!

0
source share

More articles:


All Articles