Post reply

(DTS) - multiple dynamic connections

  • November 20, 2001 at 4:56 am

    #104530

    What I am attempting to do is the following:-

    - Perform an ExecuteSQLTask which will select rows from a table, which will return a few company numbers.

    - Then perform a data transform task which will use another SQL query with a parameter being the company number, this task will transform some columns into an excel spreadsheet. I would like this data tranform task to repeat itself by creating a new excel document for every new company in the first query...

    I have tried to do this by using a Dynamic Properties Task and making the DataSource property of the Excel File Connection equal to a global variable and then in a Work Flow ActiveX script read the first queries companynumbers and set the global variables for the companynumber and the excelFileName. I have attempted this, it does run but say the first query returns 2 companies, this package will only create the first excel file which was obviously what the excel connection was set to manually the first time..????

    Can anyone help.Thanks

  • November 20, 2001 at 6:04 am

    #420233

    Hi

    Although all possible via DTS, I tend not to use it as a "solution for anything that needs to be scheduled", although this will (perhaps) slowly change over time. Anyhow, I would take another approach, look at creating a small DLL in VB and pass parameters to it via a DTS active-x call and use the globals as described, then alter the properties of te DTS task to force it to repeat itself (the task that is).

    Just ideas..

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • November 20, 2001 at 6:22 am

    #420234

    You could store the results of the ExecuteSQL task in a recordset and loop through the recordset in the package. (Peterson's book explains how to set up a loop in SQl 2000 using activeX scripts - http://www.sdgcomputing.com/dtsbook.htm has the code). Each time you have a new company number ie a new record, you stick it in a global variable, continue the rest of the package as you explained, then get the next company number, until the end of the loop.

    Paul Ibison

    Paul.Ibison@btinternet.com


    Paul Ibison
    Paul.Ibison@replicationanswers.com

Viewing 3 posts - 1 through 2 (of 2 total)

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