23.06.2016

Find disabled SSIS-tasks using Powershell, part 2

Technical Value

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. FindDisabledTasksInSsisPt2_01
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.

New parameters

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. FindDisabledTasksInSsisPt2_02 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. FindDisabledTasksInSsisPt2_03

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. FindDisabledTasksInSsisPt2_04 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. This is the console: FindDisabledTasksInSsisPt2_05 And this is the text file: FindDisabledTasksInSsisPt2_06 Much better and we only have the information we actually need.

  1. #Declare variables
  2. param (
  3.   # Required parameter
  4.   [string]$SsisServerName = $(Read-Host "PROD SSIS Server Name"),
  5.   [string]$SsisFolderName = $(Read-Host "PROD SSIS Folder Name"),
  6.   [string]$LocalPathParent = $(Read-Host "Local Path"),
  7.  
  8.   # Parameter with a default value
  9.   [string]$SsisDbName = "SsisDb"  
  10. )
  11.  
  12. # Add a backslash to $LocalPathParent if missing
  13. if (-not $LocalPathParent.EndsWith("\"))
  14. {
  15.   $LocalPathParent = $LocalPathParent + "\"
  16. }
  17.  
  18. # Set dependend values
  19. [string]$CurrentExecutionTime = Get-Date -f yyyyMMddHHmmss
  20. [string]$LocalPath = $LocalPathParent+$CurrentExecutionTime
  21. [string]$ResultsFile = $LocalPath+"\ResultsOfCheck.txt"
  22.  
  23. # Include assemblies
  24. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null
  25. [System.Reflection.Assembly]::LoadWithPartialName("System.IO")
  26.  
  27. # Remember namespaces
  28. $ssisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
  29. $ioNamespace = "System.IO"
  30.  
  31. # Initialize production SSIS objects
  32. $SqlConnectionString = "Data Source=" + $SsisServerName + ";Initial Catalog=master;Integrated Security=SSPI;"
  33. $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionString
  34.  
  35. $SsisService = New-Object "$ssisNamespace.IntegrationServices" $SqlConnection
  36. $SsisDb = $SsisService.Catalogs[$SsisDbName]
  37.  
  38. $SsisFolder = $SsisDb.Folders[$SsisFolderName]
  39.  
  40. # Check if SSIS folder exists
  41. if(!$SsisFolder)
  42. {
  43.   Throw "SSIS-Folder $SsisFolder does not exist!"
  44. }
  45.  
  46. # Create new local download folder
  47. New-Item -Path "$LocalPath" -Type directory | Out-Null
  48.  
  49. # Download and extract all projects
  50. ForEach($Project in $SsisFolder.Projects)
  51. {
  52.   # Prepare download and Unzip
  53.   $ProjectName = $Project.Name
  54.   $ExtractDirectory = "$LocalPath\" + $ProjectName
  55.   $TargetIspacName = $ExtractDirectory + ".ispac"
  56.  
  57.   # Output
  58.   Write-Host "Downloading and Unzipping project" $ProjectName
  59.    
  60.   # Download project using BCP
  61.   bcp "EXEC $SsisDbName.catalog.get_project '$SsisFolderName','$ProjectName'" queryout "$TargetIspacName " -S "$SsisServerName" -T -n | Out-Null
  62.  
  63.   # Extract project using Unzip
  64.   Unzip -qq $TargetIspacName -d $ExtractDirectory | Out-Null
  65. }
  66.  
  67. # Write heading to file and to console
  68. $Output = "Packages that have disabled tasks: "
  69. Write-Host $Output
  70. $Output > $ResultsFile
  71.  
  72. # Get a list of all dtsx packages
  73. $SsisPackages = Get-Childitem $LocalPath *.dtsx -Recurse
  74.  
  75. # Loop through all packages
  76. ForEach ($SsisPackage In $SsisPackages)
  77. {
  78.   # Check if package contains disabled tasks
  79.   $ContainsDisabledTask = Get-Content $SsisPackage.FullName | Select-String "Disabled" -SimpleMatch -quiet
  80.  
  81.   # Output to console and file
  82.   If ($ContainsDisabledTask -eq $True)
  83.   {
  84.     $Output = " - " + $SsisPackage.FullName.Replace($LocalPath,"").TrimStart("\")
  85.     Write-Host $Output
  86.     $Output >> $ResultsFile
  87.   }
  88. }

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.

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden