Thursday, May 30, 2013

[SSIS] Finding your connection strings in SQL Server 2008/R2

"Bad artists imitate - great artists steal" Picasso Banksy

I initially wasn't sure if this was worth posting - if you have the same problem, you've probably come across the same links.  But since I don't see a soup-to-nuts version, here goes.

Problem: you're migrating servers, and need to see what SSIS packages might be affected, so you need the connection strings for all of your SSIS packages.  You could open up each package and check the connections - but that's... inefficient.

If you're storing the packages on disk, you could write a short (power)shell script that would scan the SSIS package files.  But if it's in MSDB, you'd need to pull the package XML out of the system tables, then parse the XML.  This is for 2008/R2 (and if you're not using SSISDB in 2012).  In 2012, SSISDB holds the files differently, and I'm awaiting an answer from one of the MS people on the SSIS team (thanks to Jamie Thomson sqlblog.com/blogs/jamie_thomson/‎ for forwarding my tweet to him)

Code cribbed from somebody, possibly Feodor Georgiev  (I found two links, below)
http://sqlconcept.com/2011/12/06/extracting-ssis-package-definition-from-msdb/
https://www.simple-talk.com/content/print.aspx?article=1408

and the XML parse was a question I asked on StackOverflow.  Thanks to Davmos for the help!
http://stackoverflow.com/questions/16824772/sql-server-query-xml-node-dtsconnectionmanager-dtsname-in-t-sql/16825308?noredirect=1#comment24269324_16825308

4 comments:

Waheeds said...

That was very helpful, thank you!

MKE Mike said...

Dude!!! I was looking for exactly this - and you saved me a ton of time today! Thanks.

coremanster said...

Similar works for SSIS 2012 and newer packages too, but the XPATH expressions are different...

Xpath to a specific connection manager:
/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager[x]

...one probably doesn't really want to do:
//DTS:ConnectionManager

...and so on.

bourgon said...

Thanks for that, coremanster!