Thomas Rahier

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

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

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.
th
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:
FindDisabledTasksInSsis03
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.

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.