Post reply

How to modify all DTS Packages by using T-SQL?

  • February 19, 2004 at 8:10 pm

    #107277

    I have many DTS Packages in SQL Server. FYI, there are also many other DTS Packages owned by other applications.

    I recently found out all DTS Packages needed to add one global variable to it and delete one old global variable. Is there any system store proc to do this kind of tiring job without going inside the DTS Packages one by one?



    Regards,
    kokyan

  • February 20, 2004 at 6:37 am

    #494905

    There is no way to do this using T-SQL.

    You can do it using visual basic.  This http://www.sqldts.com/?208 explains how to run a dts package.  You can run a similar program to load the dts package, then add the global variable, then save.

    Note that your diagrams will all change after you have run such a program.

    Russ

    Russel Loski, MCSE Business Intelligence, Data Platform

  • February 20, 2004 at 6:59 am

    #494911

    Well, There is a bridge if you are willing to use

    sp_OA stored procedures you can do it in TSQL [Instantiating the DTS.Package (or DTS.Package2) Object].

    But I really think that vbscript more appropriate for the task!

    As Russel posted your package layout will be GONE!


    * Noel

  • February 20, 2004 at 7:30 am

    #494920

    I spend time organizing the layout of my DTS packages and would not want them arbitrarily changed.  If yours are organized for ease of understanding them then your best option is to open each one and make the change manually.

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

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

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