Solved

How do I use SSIS to update a MSSQL table from different MSSQL Server without using Linked Servers?

Posted on 2008-10-03
11
540 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:dmeenan
  • 6
  • 5
11 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22638308
Create a new SSIS Project
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!
0
 

Author Comment

by:dmeenan
ID: 22650327
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?
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22652708
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?
0
 

Author Comment

by:dmeenan
ID: 22653817
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"  
0
 

Author Comment

by:dmeenan
ID: 22710690
No one has added to entered this conversation in days.  I'd set this as abandoned.

D
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 22

Expert Comment

by:PedroCGD
ID: 22730684
Dear Friend,
I'm arriving today from one week holidays. Do you still need help?
Regards,

Pedro
www.pedrocgd.blogspot.com
0
 

Author Comment

by:dmeenan
ID: 22736104
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
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 500 total points
ID: 22739409
Dear Friend,
Check the package I made for you.
Create a new SSIS Project and add the SSIS package that it's attached to it.
Update de connections to Server A and B and test
Regards!
CREATE TABLE [dbo].[TABLE_A](

	[Row] [int] NULL,

	[Value] [char](1) NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[TABLE_B](

	[Row] [int] NULL,

	[Value] [char](1) NULL

) ON [PRIMARY]

Open in new window

Package-EE33-dtsx.txt
0
 

Author Comment

by:dmeenan
ID: 22746485
Pedro,

Perfecto!  This was exactly what I needed!  Thank you so much.
0
 

Author Closing Comment

by:dmeenan
ID: 31502867
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!
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22747472
VERY GOOD!!!!!!!!!
:-)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now