Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Thursday, May 26, 2016 12:47 PM
Points: 68, Visits: 266
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 23, 2016 6:13 AM
Points: 15,149, Visits: 13,035
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 SQLKover.

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: Thursday, May 26, 2016 12:47 PM
Points: 68, Visits: 266
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 23, 2016 6:13 AM
Points: 15,149, Visits: 13,035
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 SQLKover.

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: Thursday, May 26, 2016 12:47 PM
Points: 68, Visits: 266
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 23, 2016 6:13 AM
Points: 15,149, Visits: 13,035
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 SQLKover.

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

Add to briefcase

Permissions Expand / Collapse