Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    28

    Question Unanswered: DTS package and nested OPENQUERY's??

    Is there a way to use nested OPENQUERY's in a DTS package??

    I have the following SQL statement (whichs execute without error) that I would like to make a DTS package out of:

    INSERT tbl_Sku SELECT AX.sku_id, AX.style_id, AX.style_color_id, AX.style_size_id, AY.color_id, AY.size_master_id from OPENQUERY("SVR-1",'SELECT sku_id, style_id, style_color_id, style_size_id FROM merch.dbo.sku') as AX, OPENQUERY("SVR-2",'SELECT sku_id, style_id, color_id, size_master_id FROM ma.dbo.sku') as AY Where AX.sku_id = AY.sku_id


    The problem I am having is the connection object of the DTS package. It will not allow an OPENQUERY inside an OPENQUERY. How do I set up a third comnnection??

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your statement does not have an OPENQUERY inside an OPENQUERY. It has two joined OPENQUERY statements.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2004
    Posts
    28
    I guess I could just repeat the question that I was actually asking. I'm a programmer, not a dba, so my 'nested' terminology was wrong in the last post.

    Does anyone have experience with setting up a DTS package to insert into the target table data from 2 other connections, where the connection inserting into the target table is extracting from yet a third connection?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Either:
    1) Your use of OPENQUERY is unnecessary in a DTS package, since you can just set up a connection object within the package for each remote server.
    or...
    2) Your use of a DTS package is unnecessary, since you could just run this statement from a stored procedure through the Job Scheduler.
    The best advice I can give you on DTS is put as little logic as possible into it. It is a quirky utility that is a hodgepodge of poorly integrated technologies. Use it to load data into staging tables, and then perform your logical processing on the data using stored procedures.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Sep 2004
    Posts
    28
    Well, the dbs's at this contract want me to use DTS packages since all the existing stored procedures I am converting into DTS packages do nothing more than truncate the target table and insert data into it.

    I already have them running about 15 times faster (taking 15 minutes what was taking almost 4 hrs using sp's) so I want to finish creating a DTS package out of the last 2 sp's I am converting since it is doing the exact same thing and I need it to run at the conclution of the sucess of other processes.

    The issue I am having is that I do not know how to use 3 connections to do this. I have one table that is being loaded from tables on two different servers. Although they are currently linked servers, I want to be able to run the existing query using a DTS package. I just do not know how to set up the connections.

    I found some references to transformation lookup and was wondering if anyone reading this has done this before.

  6. #6
    Join Date
    Sep 2004
    Posts
    28
    By the way, let me just say I have no problem setting up 2 connections and running a query, but I am having problems getting data out of two connections and inserting it into a third connection, which is the target table.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by evanheld
    I already have them running about 15 times faster (taking 15 minutes what was taking almost 4 hrs using sp's)
    Then likely the sprocs were poorly written.
    Quote Originally Posted by evanheld
    all the existing stored procedures I am converting into DTS packages do nothing more than truncate the target table and insert data into it
    Also a questionable practice.
    I don't know all the details of what you are doing, but whether by choice or by direction from the "dbs's" (dba's?), your design does not appear to be following best practices. Avoiding stored procedures in favor of heavy DTS processing too often leads to solutions that look like they were designed by Rube Goldberg, as you appear to be finding out.
    I still recommend you DTS from both remote systems into staging tables and then perform your joined insert.
    One question...did the original "slow" stored procedures happen to use either cursors or programmatic looping?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2004
    Posts
    28
    Forget it, I'll try a different forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •