Post reply

DTS To Create/Export Multiple Excel Files

  • July 19, 2005 at 9:31 am

    #167891

    Your guidance please on how to accomplish the following:

    I have a server table (tbl_a) containing unique names.  For each name, I would like to alter a DTS task (using ActiveX Scripting, I assume) to modify a "Select * From tbl_b Where tbl_b.User = tbl_a.Name", then Export the result set to an Excel file (uniquely named for each user.)

    While I've found some supportive documents on how to create a global rowset and loop through it, and have used the Export Wizard to create a "one-time" export, I have not been able to integrate these successfully.

    Am I on the right track? 

  • July 19, 2005 at 10:30 am

    #575561

    I have written packages to do such a thing, but they are not easy to import and require a lot of changes to function on a different server.

    The best thing you can do is as follows:

    Explore the Dynamic properties task. It allows you to change a dts parameter at runtime. You then need to use a global variable for your where clause. This can also be set in the configuration file.

    Set a global variable for the where clause.

    Set a global variable for the excel filename.

     

    See this article to get you started. It helped me a lot.

    http://www.databasejournal.com/features/mssql/article.php/3073161

     

    Once you have this working, you can write a simple vba app to call the package via a command line. Then you can loop through all the values in the where clause calling the dts package each time.

    If you get stuck, come back onto the forum.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • July 19, 2005 at 10:48 am

    #575569

    Thank you for the response.  The article looks good.  I'll let you know of my progress.

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

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