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) |
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
feel free to reach your dream
0 comments:
Post a Comment