Learning something new: getting information from SSIS packages with PowerShell

In the series of learning something new, I started with analysing of the SSIS package XML. I know what I want to extract, so let the fun begin. I will use Powershell to get the data from the .dtsx files and save it to the database. The whole script is presented below with comments. For more information scroll down.\n# I will use Out-DbaDataTable and Write-DbaDataTable from dbatools, so import it Import-Module dbatools # find recursively all Executable nodes in .dtsx file function Find-Executables($fileName, $executable) { foreach($item in $executable) { # are we there, yet? (if no - recursion) if($item.Executables) { Find-Executables $fileName $item.Executables.Executable } # if yes - the result $prop = @{ 'refId' = $item.refId 'creationName' = $item.CreationName 'description' = $item.Description 'objectName' = $item.ObjectName 'executableType' = $item.ExecutableType 'objectData' = $item.ObjectData.ExpressionTask.Expression } $prop } } # get all Precedence Constraints; simpler than Executables, because all of them are in single node function Find-PrecedenceConstraints($fileName, $precedenceConstraints) { foreach($item in $precedenceConstraints) { $prop = @{ 'refId' = $item.refId 'from' = $item.From 'to' = $item.To 'logicalAnd' = [boolean]$item.LogicalAnd 'evalOp' = [int]$item.EvalOp 'objectName' = $item.ObjectName 'expression' = $item.Expression 'value' = [int]$item.Value } $prop } } # the data collectors $allPackagesInfo = @() $allExecutables = @() $allPrecedenceConstraints = @() # loop through every .dtsx file in folder; all my packages' names start with 0, so there is an example how to filter it foreach($file in (Get-ChildItem C:\\Users\\brata_000\\Source\\Repos\\SSIS_Graph\\SSIS_Graph\\SSIS_Graph\\* -Include 0*.dtsx)) { # read .dtsx into XML variable [xml] $pkg = Get-Content $file # create hash table with package information $pkgInfo = @{ 'Name' = $file.Name 'Executables' = Find-Executables $file.Name $pkg.Executable 'PrecedenceConstraints' = Find-PrecedenceConstraints $file.Name $pkg.Executable.PrecedenceConstraints.PrecedenceConstraint } # add the table as PSobject to variable with all the package information $allPackagesInfo += New-Object psobject -Property $pkgInfo } # I don't want to confirm TRUNCATE TABLE for Write-DbaDataTable (when I'm running it few times) $ConfirmPreference = 'none' # Having all the information in one place - save it in the database; loop through all the packages (see the filter, again?) $allPackagesInfo | Where-Object -Property Name -Like '0*' | ForEach-Object { $pkgName = $_.Name # all the Executables in the package $_.Executables | ForEach-Object { $d = @{ 'pkgName' = $pkgName 'refId' = $_.refId 'creationName' = $_.creationName 'description' = $_.description 'objectName' = $_.objectName 'executableType' = $_.executableType 'objectData' = $_.objectData } $allExecutables += New-Object psobject -Property $d } # all the Precedence Constraints in the package; casting to proper types will automaticaly create # columns of types other than nvarchar(max) when using -AutoCreateTable on Write-DbaDataTable $_.PrecedenceConstraints | ForEach-Object { $d = @{ 'pkgName' = $pkgName 'refId' = $_.refId 'from' = $_.from 'to' = $_.to 'logicalAnd' = [boolean]$_.logicalAnd 'evalOp' = [int\\]$_.evalOp 'objectName' = $_.objectName 'expression' = $_.expression 'value' = [int]$_.value } $allPrecedenceConstraints += New-Object psobject -Property $d } } # I'm using SQL Server for Linux, connecting to the VM, so I use SQL authentication (for now) - why not use 'sa' then? $cred = Get-Credential sa #save all Executables to the database $allExecutables | Out-DbaDataTable | Write-DbaDataTable -SqlInstance 127.0.0.1:14333 ` -SqlCredential $cred -Database SSISGraph -Schema dbo -Table Executables -AutoCreateTable -Truncate # save all Precedence Constraints to the database $allPrecedenceConstraints | Out-DbaDataTable | Write-DbaDataTable -SqlInstance 127.0.0.1:14333 ` -SqlCredential $cred -Database SSISGraph -Schema dbo -Table PrecedenceConstraints -AutoCreateTable -Truncate To get all the information I loop through all the files. In Control Flow the interesting data are Executable elements and Precedence Constraints. Because one Executable can contain another Executables (think: Sequence/For/ForEach Containers) I use recursion. It’s easier with Precedence Constraints because all of them are located under one XML node. Both functions get $fileName as the first parameter that is not used later. It’s because it’s one of the versions of the script and I didn’t want to remove it as I’m doing more tests for later.\n