Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Dynamic SQL Server Integration Services SSIS workflow based on variables


By:   |   Last Updated: 2008-04-29   |   Comments (5)   |   Related Tips: More > Integration Services Control Flow Transformations

Problem
When developing SQL Server Integration Services (SSIS) packages there is sometimes the need to only run certain steps or paths in the package execution either based on time period or maybe a parameter value that is passed to the package or queried from the database.  How do you setup an SSIS package to have different execution paths based on a parameter value?

Solution
This is a pretty easy process to setup, but maybe not as intuitive as you might think.  There is often the need to have one package do several things, but only having certain steps run at certain times.  By setting a variable and then based on this value one of many paths can be taken.

Here is an example of a simple package that just has four Execute SQL Tasks.  Each task is just doing a SELECT to illustrate this example, there is nothing unique about this code or this task.  The examples below should be able to be used for any of tasks that are available in SSIS.

For this example there are four Execute SQL Tasks with two different paths that can be taken and then control is passed back to Task 3 to complete the package.

If we execute the package we can see that all four tasks execute and complete.  This is great if this is what you want to do, but for our example we want to only run Task 2a or Task 2b based on a parameter value.

Here we setup a package variable called "test", but this could be called anything. We are making this a Boolean data type with a value of "False".

If we double click on the workflow arrow from Task 1 to Task 2a we get the dialog box below.  Here we are setting the workflow to go down this path based on an Expression value (first dropdown list) and the Expression equals (@test == True).  The @test is the name of the variable we setup above.  This expression is saying that if our variable "test" equals "True" then to go from Task 1 to Task 2a.

We do the same thing for the workflow arrow from Task 1 to Task 2b, but this is set to (@test == False)

After changing both of these and clicking OK the package should look like the following.  You will see the colors of the lines changed from green to blue.

If we execute the package now, we can see that Task 1 and Task 2b run (@test = False), but the package never reaches Task 3.

To fix this problem, we need to double click on the workflow arrow from Task 2a to Task 3 and change the option from "Logical AND" to "Logical OR".  With the Logical OR we are saying that either Task 2a or Task 2b needs to complete and then control passes on to Task 3. 

Once you click OK the package will look like the following. Even though you only changed one of the workflow values, both changed from "Logical AND" to "Logical OR".

At this point if we execute the package again, we see that the flow goes from Task 1 to Task 2b to Task 3 as planned.

To test our package when our variable value equals "True" we just change our variable from "False" to "True"

If we run it now we can see that since our variable is set to "True" control goes from Task 1 to Task 2a to Task3.


To take this a step further, we will store the value for "test" in a table and query the database to get the value.

First we create a table and add one row to the table with a value of 0 (False).

CREATE TABLE [dbo].[packageControls](
[test] [bit] NULL
) ON [PRIMARY]

GO

INSERT INTO dbo.packageControls (test)
VALUES (0) -- FALSE

For Task 1 we change the query to get the data from table packageControls and change the ResultSet to "Single row" as highlighted below.

On the Result Set window we "Add" a new "Result Name" equal to "test" that maps to our variable "User::test".

At this point our package should execute Task 1 to Task 2b to Task 3.

If we update our table and set "test" equal to "True" we should go from Task 1 to Task 2a to Task 3.

UPDATE [dbo].[packageControls]
SET test = -1 -- TRUE

 

Next Steps

  • To make your packages more dynamic setup control paths based on variables that can either be set by querying the database or by being passed to the package.  Take a look at this tip on how to pass variables to a package
  • Take a look at these other SSIS tips


Last Updated: 2008-04-29


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, May 06, 2015 - 7:33:11 AM - Erik Back To Top

Even in 2015 you're article still continues to help people. Thank you!


Wednesday, June 12, 2013 - 7:19:22 AM - Pallavi Singh Back To Top

Superb..Thanks a ton!!!


Thursday, February 07, 2013 - 4:39:09 AM - saravana Back To Top

Fantastic Article . Explaining the concepts clearly . Thanks a lot :)


Tuesday, June 26, 2012 - 9:28:31 PM - Yuki Back To Top

I really learned a lot from this author.  His articles are the best!


Thursday, March 01, 2012 - 5:42:20 AM - Rajasekhar Back To Top

super


Learn more about SQL Server tools