Join in DTS package

Question

Post reply

Join in DTS package

Waseem Jaleel

SSCommitted

Points: 1706
More actions
May 17, 2009 at 7:51 am

#131674

I would like to join 2 tables residing on 2 servers using DTS, without using Linked servers. Please advice..

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

I don't think you can do that without bring the data onto a common server.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

You don't necessarily need to have linked servers to be able to access data on other servers in DTS, but you haven't given enough information to give any more advice. How many tables do you want to join and which servers are they on? Which server will the result table be on?

Greg

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

I haven't used DTS in a while, but I believe you can do a lookup on a separate connection in a data pump task to accomplish a join.

You're posting in a SQL 2005 forum, if you can use SQL Server Integration Services instead of DTS you have several ways of doing it.

pankaj.baluni Ten Centuries Points: 1014 More actions · Answer 4

Hello Waseem,

you can use Opendatasource command (T-SQL)if you don't wanna use linked server.

using Opendatasource you can create an adhoc connectivity whenever you execute that code.

for more information you can check the below link.

hope it gives you some idea...

Personal Ex - I have used Opendatasource to get data from different servers. When the volume is low then it's ok however when we talk about huge data set then you creating an adhoc connectivity and then executing a procedure would not be advisable.

I suggest you to go for linked servers.

🙂

Rgds,
Pankaj

Ol'SureHand SSCrazy Points: 2744 More actions · Answer 5

Scott Coleman (5/19/2009)
I haven't used DTS in a while, but I believe you can do a lookup on a separate connection in a data pump task to accomplish a join.
You're posting in a SQL 2005 forum, if you can use SQL Server Integration Services instead of DTS you have several ways of doing it.

Good point. Still, both DTS & SSIS are there to avoid linked servers or openrowset operations. Their purpose in life is to copy data from different and possibly heterogenous data sources.

SSIS has a purpose-built DataReader object to build an in-memory table - conceivably from different data sources (have not used it). Not sure if there is a similar object in DTS.

At any rate, with both services, you can import data from different servers using data sources and data pumps (or OLEDB data reader tasks) into tables on one of the servers, then run SQL Tasks to execute joins betweem them.

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 6

Excellent post Ol'SureHand!!!

The DTS transform data task sql query connection option allows joins from heterogeneous sources but I agree that you may need to post additional information about what you are trying to accomplish.

- Costa

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

John Dempsey Hall of Fame Points: 3225 More actions · Answer 7

I don't know how this is done in DTS exactly. But I see that you have posted in SQL Server 2005, so I will make the assumption you are using 2005. If this is the case than you should have SSIS available to you allowing you to use the Merge Join component which will allow you to join the datasets.