not all packages in MSDB are showing up in msdb.dbo.sysdtspackages system table

  • I am using msdb.dbo.sysdtspackages to transfer packages (via dtutil) from one server to another. However, the sysdtspackages does not have all the packages that are in the MSDB package store in Integration Services. Is there something I need to do to refresh this system table?

  • You are looking in the wrong table. sysdtspackages is used to store DTS packages. Try looking in table sysdtspackages90 instead.

  • happycat59 (2/20/2011)


    Try looking in table sysdtspackages90 instead.

    I am in SQL Server 2008. There is no table sysdtspackages90 in that version. Also, I would have posted this to 2008 forums, but there is no Integration Services sub forum.

  • Have a look in sysssispackages.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil Parkin (2/22/2011)


    Have a look in sysssispackages.

    Thanks Phil, that is the one I needed, it has all the msdb packages. Not sure how I missed it.

  • Hi everyone

    we are using Microsoft SQL Server 2017 Enterprise Edition in a cluster.

    I also look for my dtsx packages in the msdb.dbo.sysdtspackages system table, but they cannot be found there.

    Are these not saved in the cluster?

    I want to list packages in which tables and stored procedures are used. The Integration Service Catalog contains hundreds of projects that contain many packages.

    How or where can I therefore retrieve the metadata?

    I have already searched the WEB and found nothing except for this article. Unfortunately the last answer is already 10 years old.

    Who has an idea.

    I am currently exporting the projects (ispac files) from the Integration Service catalog and unpacking them. With Notepad ++ search the dtsx packages with the search function in text files.

    After the 10th project and a lot of packages, I don't feel like it anymore and am looking for a simple solution.

    Thanks in advance.

    Frank

    • This reply was modified 2 months, 2 weeks ago by  frank.kress. Reason: Formating
  • It is not easy to query the content of packages which have been deployed to SSISDB, because the deployment process also encrypts the package contents.

    But assuming you have all of the packages stored in source control, you can text search across the DTSX files themselves, as they are in XML format.

    There is no simple solution to this one.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 7 posts - 1 through 7 (of 7 total)

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