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

Question

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

Stringzz

Ten Centuries

Points: 1266
More actions
February 19, 2011 at 9:16 am

#388898

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?

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

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

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

Stringzz Ten Centuries Points: 1266 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246956 More actions · Answer 3

Have a look in sysssispackages.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Stringzz Ten Centuries Points: 1266 More actions · Answer 4

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.

frank.kress SSC Veteran Points: 281 More actions · Answer 5

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 3 years, 3 months ago by frank.kress. Reason: Formating

Phil Parkin SSC Guru Points: 246956 More actions · Answer 6

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin