SQL Server Performance

How to extract tables and SQL used in a DTS package?

Discussion in 'General DBA Questions' started by SARAVANANG, Feb 22, 2010.

  1. SARAVANANG New Member

    Please let me know if there is a sp or table which will list out the table names and SQL included inside a DTS package
  2. satya Moderator

    It depends on how that DTS package is saved for that database, for instance:
    1. Meta Data Services: With this save option, you can maintain historical information about the data manipulated by the package. However, Meta Data Services and the repository database must be installed and operational on your server. You can track the columns and tables that are used by the package as a source or destination. You also can use the data lineage feature to track which version of a package created a particular row. You can use these types of information for decision-support applications.
    2. SQL Server: With this default save option, you can store a package as a SQL Server msdb table, allowing you to: store packages on any instances of SQL Server on your network; keep a convenient inventory of saved packages in SQL Server Enterprise Manager; and create, delete, and branch multiple package versions during the package development process.
    3. Structured Storage File: With this save option, you can copy, move, and send a package across the network without having to store the file in a SQL Server database. The structured storage format allows you to maintain multiple packages and multiple package versions in a single file.
    4. Visual Basic File: With this save option, you can programmatically customize a package created in DTS Designer or the DTS Import/Export Wizard. The option scripts out the package as Visual Basic code, and you can later open the Visual Basic file and modify the package definition in your development environment.
  3. SARAVANANG New Member

    Thanks Satya.
    But I want to extract the SQLs used in each step of DTS packages.We have close to 200 DTS packages.
    We want to consolidate the SQLs used in each of those dts packages.
    Please help me in finding that.
  4. Subhash_Chandra New Member

    Hello Saravanang,
    There is no sp or script to search DTSs. The best way is:
    1. Save all DTSs as Visual Basic Script file (.bas) in a folder. You would have to do it one by one by open a DTS and Save as .bas file.
    2. From a command prompt rename all files from .bas extension to .txt extension. The command would be like REN c:DTS*.bas *.txt
    3. Now you can search the folder for any table or query. That will list all files that would have that table or query.
    Regards,
    Subhash Chandra
    http://SQLReality.com/blog/
  5. SARAVANANG New Member

    Thanks Subhash
  6. satya Moderator

    Hence I have listed the various ways that is helpful to store the DTS package, so look at the current storage pattern of DTS package in the environment then choose appropriate method to extract.

Share This Page