Post reply

SQL to know if a database has DTS or not...

  • October 29, 2008 at 5:55 am

    #195148

    Hi All,

    Is there any script (or any other way apart from manual) which can tell me whether a database has DTS package or not.

    Cheers

    A

  • October 29, 2008 at 5:17 pm

    #891546

    I might not understand your question, but DTS packages are only stored in the msdb database.

    Greg

  • October 29, 2008 at 10:15 pm

    #891597

    I have a requirement to know what databases are being used in DTS packages. Is there any way to do this?

    For example a given Database X, is there any sql which may help me in knowing in which DTS packages, this database is being referenced.

    Thanks for your support.

    Cheers

    Amit

  • October 29, 2008 at 10:39 pm

    #891603

    You need to check all DB Connections in your DTS's.

    DTS is just an application.

    It may connect to whatever database it's been set to connect to.

    Database has no way to know which application is intented to establish connection with it.

    _____________
    Code for TallyGenerator

  • October 29, 2008 at 11:25 pm

    #891610

    Hi Sergiy,

    I may be wrong, but I thought that this meta data information is stored somewhere (in some system database or files)...

    Thanks

    A

  • October 30, 2008 at 2:02 am

    #891632

    It's in connection strings for OLE DB drivers DTS uses to connect to different data sources, including SQL Server databases.

    Connection string may be hadcoded in VB code, may be loaded from another database or may be read from text file.

    Of course, all these sources have nothing to do with system tables.

    _____________
    Code for TallyGenerator

  • October 30, 2008 at 3:54 am

    #891668

    With reference to SSCrazy,

    I have got a table which stores the information about the DTS in the msdb database. Below is the query:

    select * from sysdtspackages

    However, i dnt know whether this table is related to other tables as well.

    If anyone has an idea, let me know.

    Cheers

    A.

  • October 31, 2008 at 10:26 am

    #892457

    Connections are part of the package and the package information is stored as binary data in msdb.dbo.sysdtspackages, so there isn't a way to get the information with T-SQL. The only way to see it is to open each package in DTS Designer or save all the packages as VB files and edit them. I think I've seen a script in the script section of this site that purports to save all packages in an instance to files.

    Greg

  • October 31, 2008 at 5:21 pm

    #892629

    If it's just a handful of packages, you can open them to find the connections. However, if you have hundreds or thousands of packages it might be worth the $300 for this tool - http://www.dtspackagesearch.com

    I do not work for this company and have not used their product but a colleague of mine thinks it's great.

  • November 3, 2008 at 5:03 am

    #892929

    Hi All,

    Thanks for yours replies..

    I am still struggling with this.. I tried the Software DTS Package Search. It does give the database name but still it is a manual task to be done which takes lots of time with the thousands of DTS on the servers.

    I strongly believe there must be some way to do this, if not by an sql... Any software you might think will give the answer straight away..

    Cheers

    A.

Viewing 10 posts - 1 through 9 (of 9 total)

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