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
543 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

825 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