Home > SSIS Best Practices > SSIS – Use user variables to control task flow

SSIS – Use user variables to control task flow

There are many usages of user-defined variables in SSIS. I use them primarily for two purposes. One is to control task flow, another is to get values so I can save to the log file.

Here is an example where I am using a user-defined variable varETLMode to control weather the ETL process is for the historical loading or for the daily incremental loading.

User-defined variable:

  • varETLMode

The variable comes from a SQL table:

  • tblamETLMode

image 

  • This is how the variable should be set

image

The goal in SSIS is:

  • if varETLModel == “Initial”, delete the staging tables that stored the Previous data
  • if varETLModel == “Daily”, save data into the staging tables from the target database

image

Here are the steps:

1) click any blank space in the surface of the package (do not click on any tasks), then open the SSIS menu, and then chose Variables.

image

2) Click the Add Variable button to add varETLMode with String as data type, and no value. Close the variable window.

image

3) Create 3 tasks as shown above. Open the first task’s edit window. Make sure that the ResultSet is set to Single row, and the SQLStatement is set as the above.

 

image

On the Result Set tab, click the Add button to add a row shown here. The Result Name must be the same as the column name in the query (there is no drop down so you need to type the name correctly). The variable name box has a drop down. Choose the user-defined variable varETLMode you just created in the Variable window.

image

4) Open the edit window for the Precedence Constraint ( the link on the left), and set it as shown here.

image 

For the 2nd Precedence Constraint ( the link on the right), set it as shown here.

image

End

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: