visual-studio-2012ssis

Identifying and removing unused connection managers in SSIS package


I'm looking for a way to identify unused connections in a package I've been writing. I don't see a way to check each one for usage in the design surface, and there doesn't seem to be any sort of build warning or "fxcop for SSIS" that would flag them on build. Searching on technet was noisy and didn't seem to produce anything relevant. Is there a way to do this automatically, or do I have to crossreference all of my sources and destinations with the connections?


Solution

  • Bit tedious, but if you look at the SSIS XML "Code" (right click on the package, View Code), you will find the DTS:ConnectionManager node.

    Each connection will have a DTSID.

    If you search for any references in the rest of the file for that particular DTSID, and find none, chances are, it's not being used.

    All of the items that use connections that I've seen so far (which isn't everything) refer to connections using a connectionManagerID="{DTSID}" attribute.

    This might make for an interesting little validator tool / plug in...

    To view the "Code", the package should not be set to EncryptAllWithPassword -- perhaps EncryptAllWithUserKey has a similar effect, but I can't remember. Otherwise all you'll be seeing is encrypted gibbrish.