SSIS - find unused variables in several packages

Question

When working on large SSIS projects containing several packages, the packages can start to get a bit messy with variables that were created but never used or have been made redundant by other changes. I need something that will scan several SSIS packages and list all unused variables.

Answer 1

I have managed to answer my own question by employing some Powershell. The script below uses xpath to get the variable names and then uses regex to find the number of occurrences, if it occurs once it must be because it was defined but never used.

The only caveat is that if you use names for variables that are words that would naturally be present in a dtsx file, then the script will not pick them up. I probably need to expand my script to only do a regex search on spesific nodes in the package.

$results = @()
Get-ChildItem -Filter *.dtsx |
% {
    $xml = [xml](Get-Content $_)
    $Package = $_.Name
    $ns = [System.Xml.XmlNamespaceManager]($xml.NameTable)
    $ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts")
    $var_list = @($xml.SelectNodes("//DTS:Variable/DTS:Property[@DTS:Name = 'ObjectName']", $ns) | % {$_.'#text'})
    $var_list | ? {@([Regex]::Matches($xml.InnerXml, "\b$($_)\b")).Count -eq 1} | 
    % { $results += New-Object PSObject -Property @{
         Package = $Package
            Name = $_}
    }
}
$results