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
544 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Analysis of table use 7 62
Table create permissions on SQL Server 2005 9 45
How to simplify my SQL statement? 14 55
Parse this column 6 34
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 SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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…

685 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