Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
549 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

636 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