SSIS - find unused variables across multiple packages
When working on large SSIS projects containing multiple packages, packages can get a little confusing with other changes that are created but never used or redundant. I need something to scan multiple SSIS packages and list all unused variables.
I was able to answer my question by applying multiple Powershells. The script below uses the xpath to get the variable names and then uses the regex to find the number of occurrences if it occurs when it should be because it was defined but never used.
The only caveat is that if you use variable names that are words that would naturally be present in the dtsx file, then the script will not pick them up. I probably need to expand my script to do regex searches on spesific nodes in a 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
This is a big question, as I have the same concerns about several fairly large SSIS packages. Unfortunately, there is no function outside of the SSIS package that will provide this functionality. See Discussions in the attached link:
CodePlex
But by opening the SSIS package, you can define the use of the variable by applying the steps in the following link:
find-ssis-variable-dependencies-with-bi-xpress
Hope it helps.