Dynamically Disabling Steps in SSIS

Often I find myself wanting to choose dynamically between different steps in  a package or being able to turn off a step without having to actually modify the procedure.

This can be accomplished dynamically by disabling a step via a variable or parameter.  In this example I have a loop that processes a set of college clubs.  The query loads a list of clubs.  I would like to have a different process for The Physics and Astronomy club.  Maybe eventually each club will have a different process of its own.  One way to make this happen is by controlling what is enabled.  Below is the package.

DisablePackageExample1

The SQL Load List of Files loads a table of the clubs to a source object that is used in the for each loop.   The Name of the club is assigned to  a variable called ClubName.

DisablePackageExample2

I then modify the step called Physics and Astronomy.  Open the properties of the Physics and Astronomy step and add an expression for the Disable feature.DisablePackageExample3

I test to see if the ClubName is not equal to Physics and Astronomy Club. If it isn’t then Disable is set to true and this step will not run.  If the ClubName is set to Physics and Astronomy Club then the step will continue.   Since I only have one other step for all other clubs.  I set the Disable for this step when ClubName is equal to Physics and Astronomy Club.  DisablePackageExample4

Now as I loop through the clubs,  the Physics and Astronomy Club step will only run for that club and All Other Clubs step will run for everything else.

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s