Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find all packages connected to a specific server Expand / Collapse
Author
Message
Posted Thursday, August 29, 2013 11:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:10 PM
Points: 65, Visits: 260
We're having to change one of our servers, now we have to find all SSIS packages that are linked to it. Does anyone know an easy way to do this ... there's got to be an easier way than opening each one and manually looking, right????
Post #1489826
Posted Thursday, August 29, 2013 12:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,562, Visits: 11,372
Which version of SSIS?
Where are the packages stored?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1489849
Posted Thursday, August 29, 2013 1:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:10 PM
Points: 65, Visits: 260
Sorry for omitting that information. We're on SQL server 2008 and all packages are stored in MSDB.
Post #1489861
Posted Thursday, August 29, 2013 1:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,562, Visits: 11,372
OK. I asked because it is easier in SQL Server 2012.

When the packages are stored in MSDB, they are stored as XML in a table.
This means you can query the packages using XML methods in TSQL.

This article might be a good start:
Collecting information about your SSIS packages




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1489866
Posted Thursday, August 29, 2013 2:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:10 PM
Points: 65, Visits: 260
Thanks for the link but it's still working one package at a time to get the information. Granted, once it's done you've got it, but for now I was just looking for a quickie method.

Thanks for the posts!

Post #1489898
Posted Thursday, August 29, 2013 2:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,562, Visits: 11,372
Shaira (8/29/2013)
Thanks for the link but it's still working one package at a time to get the information. Granted, once it's done you've got it, but for now I was just looking for a quickie method.

Thanks for the posts!



Unfortunately there's no quickie for this one.
Once you have the XML query to extract the data you need, you can put it in a cursor to loop over all the packages and store the extracted info into a table.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1489900
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse