Post reply

Join in DTS package

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

  • May 17, 2009 at 8:04 am

    #996004

    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]

  • May 18, 2009 at 10:50 am

    #996504

    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

  • May 19, 2009 at 6:50 am

    #996892

    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.

  • May 19, 2009 at 7:01 am

    #996909

    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

  • May 20, 2009 at 5:39 am

    #997507

    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.

  • May 20, 2009 at 7:52 am

    #997602

    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

  • June 9, 2009 at 8:55 pm

    #1007164

    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.

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

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