Need to find out which of the SSIS packages stored in msdb reference a specific shared datasource

Question

Post reply

Need to find out which of the SSIS packages stored in msdb reference a specific shared datasource

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
April 15, 2010 at 9:18 am

#139671

I need to find out which of the SSIS packages stored in msdb reference a specific shared datasource, dtsrc1.
System view sysssispackages does not seem to be the way to go.
Anyone have any other way?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 1

If you can query the system tables then this is clearly the best method but I have no idea if this is possible.

See this as an alternate method.

http://www.databasejournal.com/features/mssql/article.php/3734096/Using-dtutil-to-copy-SSIS-packages-stored-in-SQL-Server.htm

If you can export all your packages out to files you should be be able to search the dtsx files.

I have never done this, there may be a better way but I thought I would offer this as a suggestion.

Good Luck

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

Marios Philippopoulos (4/15/2010)
I need to find out which of the SSIS packages stored in msdb reference a specific shared datasource, dtsrc1.
System view sysssispackages does not seem to be the way to go.
Anyone have any other way?

Thank you, I also think this is the only way;

I just put this out there in case there is another solution and because I find it frustrating that metadata support is so poor in SSIS.

Fortunately I only have about 15 packages in my SSIS solution - all using shared data sources - so I just bit the bullet and searched them in BIDS one by one.

What would I do if I had 100?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

You could build a VB or C# app to open the packages one-by-one and examine the connections collection. I *think* you can read them directly from the server but I haven't done that so I am not going to take a solid position on that.

CEWII

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

Elliott W (4/15/2010)
You could build a VB or C# app to open the packages one-by-one and examine the connections collection. I *think* you can read them directly from the server but I haven't done that so I am not going to take a solid position on that.
CEWII

Thank you, maybe there is a tool out there that does that...

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]