August 29, 2013 at 11:57 am
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????
August 29, 2013 at 12:58 pm
Which version of SSIS?
Where are the packages stored?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 29, 2013 at 1:10 pm
Sorry for omitting that information. We're on SQL server 2008 and all packages are stored in MSDB.
August 29, 2013 at 1:14 pm
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
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 29, 2013 at 2:15 pm
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!
August 29, 2013 at 2:18 pm
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply