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
541 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

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…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

864 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

23 Experts available now in Live!

Get 1:1 Help Now