Clever Geek Handbook

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.

+3


source to share


2 answers


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
+4


source


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.

0


source






More articles:


All Articles