Post reply

Looping through a table in DTS

  • November 20, 2003 at 8:15 am

    #107102

    I have a table countries and a table companies.

    I have a DTS package which exports a single countries companies to Excel and then zips the file and puts it on an intranet server.

    I now want to create an individual for every country in the countries table.

    I guess that I need to step through the countries table and update a global variable with the country code. How do I step through a table in DTS? or is there a better way?

  • November 20, 2003 at 3:21 pm

    #482975

    Take a look at the rowset return parameter in the ExecuteSQL task.

    You can execute an SQL statement to return a list of countries and then assign that to a global variable. Then you use this global variable just like an ADO recordset.

    Take a look at the following link,

    http://www.sqldts.com/?298

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • November 21, 2003 at 2:40 am

    #483023

    Declare a cursor and use the fetch next statement.

    Matthias

  • November 21, 2003 at 10:09 am

    #483159

    Thanks for your replies, I went for the loop through a Rowset solution as it keeps everything in a DTS package.

    Took me some time to fathom out how this works. Workflow scripts and output parameters are well hidden! But I guess that's my fault for jumping in the deep end. And given time I should work my way through the SQLDTS site which looks good.

    I guess I will have to spend time studying the SQLDTS site which looks a good resource.

  • November 21, 2003 at 10:50 am

    #483170

    Could do with a little bit more help please.

    I adpated the example to step through my table and create a string.

    Now I need to adapt this package so that it loops through my ActiveX Script and Transform Data Task.

    What is the best way of doing this?

    - Add a global variable which stores my country code?

    - Add a workflow script to all the tasks in my loop as per example to test for oRs.EOF

    ?

  • November 23, 2003 at 4:44 pm

    #483270

    You don't need to add a workflow script to every task. Just the one after your Transform task. In that task you could use a workflow script or a task script.

    Take a look at the looping article at,

    http://www.sqldts.com/?246

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

Viewing 6 posts - 1 through 5 (of 5 total)

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