How do I use SSIS to update a MSSQL table from different MSSQL Server without using Linked Servers?
Using SSIS for SQL 2005, I can't figure out how to update one table by joining to a table on different server using SSIS connections, not linked servers or copying data over into a table for an update. I see some articles like http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1 which indicate that this can be done with a Lookup(?). Any help is appreciated.
Lost in Towson
Lost in Towson
ASKER
Pedro,
I am able to do what you outlined above (as shown in http://www.mssqltips.com/tip.asp?tip=1322), but I want to Update one side of the join, not Insert, and I do not see how to accomplish this. Would I somehow assign the output of the Merge Join to a Connection and then run an update sql task against this connection?
I am able to do what you outlined above (as shown in http://www.mssqltips.com/tip.asp?tip=1322), but I want to Update one side of the join, not Insert, and I do not see how to accomplish this. Would I somehow assign the output of the Merge Join to a Connection and then run an update sql task against this connection?
Hi deeman,
I'm going to holidays in few hours, so, telll me all you need soon..:-)
If you need to make an update in spite if an insert, then use he OLEDB Command in spite of OLEDB Destination.
With OLEDB command you can update each line in the pipeline. You only need to insert the SQL Update statment and use ? when you need a parameter.
EX: Inside OLEDB command you define the connection and next write the stament something like this:
Update Mytable SET Myfiled = ? WHERE MyfieldID=?
Helped?
I'm going to holidays in few hours, so, telll me all you need soon..:-)
If you need to make an update in spite if an insert, then use he OLEDB Command in spite of OLEDB Destination.
With OLEDB command you can update each line in the pipeline. You only need to insert the SQL Update statment and use ? when you need a parameter.
EX: Inside OLEDB command you define the connection and next write the stament something like this:
Update Mytable SET Myfiled = ? WHERE MyfieldID=?
Helped?
ASKER
Basically I have two tables on different servers, call them server_a and server_b. I want to join them and update the value of one field to the value of a field in the other server's table (as shown below), but without (and this is the key) using a linked server (as my little code snippet at the bottom would do). I want to use the SSIS connections and join and run an update.
Server_A.DB_A | Server_B.DB_B
TABLE_A | TABLE_B
--------------------------
Row Value | Row Value
--------------------------
1 NULL | 5 A
2 F | 6 D
3 D | 7 NULL
4 NULL
5 NULL
6 E
7 A
8 NULL
9 B
Update A set A.Value = B.Value
from ServerA.DB_A..TABLE_A A
join ServerB.DB_B..TABLE_B B on B.Row = A.Row
where b.Value is not NULL
From this example, TABLE_A row_id = 5 would have Value set to "A" and TABLE_A row_id = 6 would have Value set to "D"
Server_A.DB_A | Server_B.DB_B
TABLE_A | TABLE_B
--------------------------
Row Value | Row Value
--------------------------
1 NULL | 5 A
2 F | 6 D
3 D | 7 NULL
4 NULL
5 NULL
6 E
7 A
8 NULL
9 B
Update A set A.Value = B.Value
from ServerA.DB_A..TABLE_A A
join ServerB.DB_B..TABLE_B B on B.Row = A.Row
where b.Value is not NULL
From this example, TABLE_A row_id = 5 would have Value set to "A" and TABLE_A row_id = 6 would have Value set to "D"
ASKER
No one has added to entered this conversation in days. I'd set this as abandoned.
D
D
Dear Friend,
I'm arriving today from one week holidays. Do you still need help?
Regards,
Pedro
www.pedrocgd.blogspot.com
I'm arriving today from one week holidays. Do you still need help?
Regards,
Pedro
www.pedrocgd.blogspot.com
ASKER
Yes, I'd still like to know how to use connections in SSIS to UPDATE a table on one server via a join to another server with a where clause. Currently I am using a linked server to directly update server a from server b. Seems like there would be a way to use connections without having to setup linked servers all over the place. Thanks for any direction you can provide.
Dan
Dan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Pedro,
Perfecto! This was exactly what I needed! Thank you so much.
Perfecto! This was exactly what I needed! Thank you so much.
ASKER
Pedro was polite and never condescending. He gave me a concrete and complete solution, providing me with the dtsx package to download and import and test. This is just what I joined EE for!
VERY GOOD!!!!!!!!!
:-)
:-)
Add a dataflow component to your controlflow
Inside dataflow add 2 sources, for example 2 OLEDB Sources for each server
Then, inside the dataflow add a merge join task and link the sources to this task.
After that, add a destination to insert the result from the join created with Merge Join.
Helped?!
Need a example?!
Regards!