In my previous blog post about finding disabled SSIS-tasks with Powershell I explained the reason why we need this and I created a script that does the checks for you. However, this script only works with a local copy of the SSIS packages (e.g. from your source control), but not with the SSIS packages that are actually deployed to your server. This blog post will deal with how the script is extended.
Let’s imagine we want to search a folder in the SSIS catalog that contains 3 projects and in total 14 packages like in my example.
We could of course import every SSIS project manually to Visual Studio, save them locally and then use my script from the previous post. But the more projects we have, the more time you’ll spend on downloading the projects. Therefore let’s talk about the new version of the script.
To be able to connect to a server the script gets two new parameters for the name of the server your projects are deployed to and for the folder that you want to check. To define the local folder where the packages from the server should be downloaded to you need another parameter.
Connect to the server
You now have to connect to the SSIS catalog on your server, find the folder that you want to check and then iterate through all the SSIS projects. I will not explain this in detail, because I’m using code that was originally created by my colleague Sandra Erb (thanks for letting me use the code here) in her blog series about automatic deployment of a BI-solution.
Get the packages
Inside the processing loop that iterates through the SSIS folder you copy the SSIS project from the server. It took quite a while to find out how this works properly, because creating a file from a byte stream led to corrupt files. It turned out that the solution is using BCP to download a project.
Extract the packages
When you have copied the projects to your temporary folder you may want to search them, but wait, you have only downloaded ispac-files so far. These files are archives, you cannot perform a full-text-search here.
The solution is easy: Unzip them into subfolders in order to get the actual packages. Subfolders are of course not mandatory, but this will avoid conflicts with duplicate package names.
Search for the „Disabled“ string and report occurencies
Basically the new script now works the same way as the old one did. It iterates through all the files in the local folder, searches for „Disabled“ and then prompts messages to the console. But there is one improvement, because the script now also spools information into a text file.
This file contains only the output that you want inside the file, but not all the other information that was prompted to the console, because for example I found no way to get Unzip completely quiet.
Much better and we only have the information we actually need.
# Required parameter
[string]$SsisServerName = $(Read-Host "PROD SSIS Server Name"),
[string]$SsisFolderName = $(Read-Host "PROD SSIS Folder Name"),
[string]$LocalPathParent = $(Read-Host "Local Path"),
# Parameter with a default value
[string]$SsisDbName = "SsisDb"
# Add a backslash to $LocalPathParent if missing
if (-not $LocalPathParent.EndsWith("\"))
$LocalPathParent = $LocalPathParent + "\"
# Set dependend values
[string]$CurrentExecutionTime = Get-Date -f yyyyMMddHHmmss
[string]$LocalPath = $LocalPathParent+$CurrentExecutionTime
[string]$ResultsFile = $LocalPath+"\ResultsOfCheck.txt"
# Include assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null
# Remember namespaces
$ssisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$ioNamespace = "System.IO"
# Initialize production SSIS objects
$SqlConnectionString = "Data Source=" + $SsisServerName + ";Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionString
$SsisService = New-Object "$ssisNamespace.IntegrationServices" $SqlConnection
$SsisDb = $SsisService.Catalogs[$SsisDbName]
$SsisFolder = $SsisDb.Folders[$SsisFolderName]
# Check if SSIS folder exists
Throw "SSIS-Folder $SsisFolder does not exist!"
# Create new local download folder
New-Item -Path "$LocalPath" -Type directory | Out-Null
# Download and extract all projects
ForEach($Project in $SsisFolder.Projects)
# Prepare download and Unzip
$ProjectName = $Project.Name
$ExtractDirectory = "$LocalPath\" + $ProjectName
$TargetIspacName = $ExtractDirectory + ".ispac"
Write-Host "Downloading and Unzipping project" $ProjectName
# Download project using BCP
bcp "EXEC $SsisDbName.catalog.get_project '$SsisFolderName','$ProjectName'" queryout "$TargetIspacName " -S "$SsisServerName" -T -n | Out-Null
# Extract project using Unzip
Unzip -qq $TargetIspacName -d $ExtractDirectory | Out-Null
# Write heading to file and to console
$Output = "Packages that have disabled tasks: "
$Output > $ResultsFile
# Get a list of all dtsx packages
$SsisPackages = Get-Childitem $LocalPath *.dtsx -Recurse
# Loop through all packages
ForEach ($SsisPackage In $SsisPackages)
# Check if package contains disabled tasks
$ContainsDisabledTask = Get-Content $SsisPackage.FullName | Select-String "Disabled" -SimpleMatch -quiet
# Output to console and file
If ($ContainsDisabledTask -eq $True)
$Output = " - " + $SsisPackage.FullName.Replace($LocalPath,"").TrimStart("\")
$Output >> $ResultsFile
One final remark on this script, it does not delete the local folder where the files where downloaded and unzipped to. But keep in mind that your local storage is always limited, so cleanup the files when your checks are done.