Find disabled SSIS-tasks using Powershell, part 1

Technical Value

SSIS provides a functionality which is a really helpful and dangerous at the same time: Disabling of Control Flow tasks. It is really helpful during development, because you can disable all the tasks you don't need for you current work. The following example shows you, why this disabling Control Flow tasks is a good idea.


Now let's say you want to edit only the last task and then test it, so you will probably disable all those preprocessing tasks and focus on developing and testing your changes.


After development is done you check-in your changes to source control and then the package will be deployed with the next release or hotfix.

Why is this so dangerous at the same time?

A few days after deployment to production you receive customer complaints that something is wrong with the data. You check the package that is deployed and detect that you forgot to enable those tasks that you had disabled. Now you have to develop a hotfix and of course you have to explain this mess to the customer.

With this bad experience in mind you may now want to check all your packages for disabled tasks.

1st idea: Check all packages in Visual Studio

The most obvious way to do this is to open all packages in Visual Studio and then look for disabled tasks. But for example in my current project we have over 300 dtsx files in 25 subfolders. Checking so many files manually will become pretty annoying, you'll spend hours or maybe days on those checks and maybe you have to discuss with your customer if he wants to pay for such checks with every release.

2nd idea: Check all packages in a text editor

The next idea could be to open all SSIS packages in a text editor like UltraEdit or Notepad++ and search for "Disabled" in all open files. In general this works, because SSIS packages are XML files and if there is a disabled task you'll find the following string:


However, this does still not work well if you have to check a large number of packages, because you have to open them manually (e.g. from several subfolders) in your editor and you might experience some memory problems. And it is still manual work that you have to do.

3rd idea: Let a Powershell script do the work for you

As both ideas don't look that good, I'll show you another way to solve this problem: Create a Powershell script that performs the search for "Disabled" in all packages in all subfolders automatically. The script only needs to do the following steps:

1. Start the script with a parameter to define where your SSIS packages are stored on your client.

2. Use Get-ChildItem to get all dtsx-files in your local path. If you want to search through several subfolders then do a recursive search starting in your top folder. If necessary exclude some subfolders, in my case I excluded "obj", because I had build some of the SSIS projects in Visual Studio before and that would produce duplicate results.

3. Use Get-Content and Select-String "Disabled" -quiet inside a foreach loop to find all packages where one or more disabled tasks appear and print it to console.

  1. param (
  2.   # Required parameter
  3.   [string]$LocalPath = $(Read-Host "Local Path")
  4. )
  6. # Write heading
  7. Write-Host ""
  8. Write-Host "Packages that have disabled tasks: "
  10. # Get a list of all dtsx packages
  11. $SsisPackages = Get-Childitem $LocalPath *.dtsx -Recurse | Where {$_.FullName -notlike "*\obj\*"}
  13. # Loop through all packages
  14. ForEach ($SsisPackage In $SsisPackages)
  15. {
  16.   # Check if package contains disabled tasks
  17.   $ContainsDisabledTask = Get-Content $SsisPackage.FullName | Select-String "Disabled" -quiet
  19.   # Output
  20.   If ($ContainsDisabledTask -eq $True)
  21.   {
  22.     Write-Host " -"$SsisPackage.FullName.Replace($LocalPath,"").TrimStart("\")
  23.   }
  24. }

Now you know which files contain disabled tasks and should be checked and reworked.

This is a first and simple solution to find disabled tasks in SSIS packages, I'll continue working on this, because I'd find it much cooler if I could check the packages that are actually deployed and not those that are stored in version control. Eventhough they should of course be identical there is no guarantee that this is always the case.

Two additional recommendations on disabling tasks

1. Disable tasks only for testing purposes and enable all tasks before you check-in to version control. It makes no sense to bring disabled tasks to production. Your colleagues won't know what this disabled task does and why it is disabled and you'll probably Forget this after a few weeks. If you don't need a task (resp. code in general) any more then delete it.

2. Do not use expressions on tasks to enable resp. disable a packages at runtime. This will always lead to "Disabled" strings in the XML of the packages and it makes the package difficult to read and understand. Use precedence constraints with conditions instead.

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
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