DTS packages SQL Server 200

  • How would I find out of 400 plus DTS packages which one references a particular table?

    Thank you.

  • If you are talking about the scheduled packages, probably you would like to create a server trace with:

    Events:

    T-SQL Batch Starting

    Columns:

    like textdata, start time and end time

    Filter:

    textdata LIKE %YourTableName%

    and check the trace at what time the batch was executed with your table name and then check what job was executed at this time.

    Regards,Yelena Varsha

  • David Bellach (4/4/2008)


    How would I find out of 400 plus DTS packages which one references a particular table?

    Thank you.

    [font="Tahoma"]You can use the below query to check.. enter the name of the dts in between the two percentages.

    select * from msdb.dbo.sysdtspackages where name like '%%'[/font]

  • Vidhya,

    David needs the table names, not the package name.

    Regards,Yelena Varsha

  • Table names is correct, though this stuff is helpful in other functions I need to do.

  • You could save your DTS packages as Visual basic files (.bas) then search for the table name within the files.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Yes, that is a good solution - save as VB files and search them.

    Does somebody know how to export text for 400 packages?

    Regards,Yelena Varsha

  • To save as Visual Basic? I thought the DTS pacakges were stored possibly binary?

  • There are a number of scripts on the web that can do a bulk export of DTS packages. Most of them (including those in SqlServerFineBuild) are aimed at transferring the packages to a diferent server so you may have to tweak the output definition to get the VB output, but this is easy to do.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Hi David,

    You can save DTS package as VB file - when doing Save as.

    You can open this file with Notepad. Also if you copy all of these files to one file with the help of Copy command, rename it to txt and you will have one nice big file to search.

    Hi Ed,

    Thanks for the response, I will try to find one. Is SQL Server FineBuild free or we have to buy it?

    Regards,Yelena Varsha

  • SqlServerFineBuild is free, but you can make a contribution (it goes direct to charity) if you wish.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Thanks Ed!

    Regards,Yelena Varsha

  • Nigel Rivett's script has been a big help for us. We have been using it for a couple of years and it is very, very good.

    http://www.nigelrivett.net/ScriptDTSProperties.html

    I also found something this morning at SQL Server Central, and it refers to SQLSpec 3.8. Unfortunately I just downloaded it but have not had time to check it out

    http://www.elsasoft.org/default.htm?site=sqlservercentral

  • Thanks!

Viewing 14 posts - 1 through 13 (of 13 total)

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