Creating parameterized SSIS packages

Now that we have learnt how to create a simple SSIS Package, let us see how we can make it adaptive. What I mean by adaptive is, how we can maintain the package with minimal changes in the future. Before we talk about how we handle this, let us talk about the scenarios where we might want to change the package.

• Package needs to be moved from one environment to another
• The password needs to be changed for SQL Server
• You wish to send a mail with the number of records processed/error
• You wish to send a mail to a new recipient
• FTP Location has changed
• Logic of a query has changed (this needs more thinking as it might also need change in the logic)
• There can be a lot more scenarios than these.

So now the question is how do we achieve this?

Folks who have done development in any other language or TSQL should have guessed the answer. Yes, we make use of variable in SSIS.

How do we set up variables in SSIS?

When you open a SSIS Package, you may see the Variables panel to the left of the Control flow panel as in figure below:



In case you are unable to view this, you need to go to Menus -> View-> Other Windows-> Variables as shown in figure below:



Another quick way is to click anywhere on the blank area of control flow or data flow and select Variables from the shortcut menu. You will be able to see the variable panel to the left hand side like shown in figure below.



In the variables panel, you will see it as a blank panel with a few buttons on top. By default, you will have 5 buttons as shown in the figure below. Each button is described below with reference to their position from the left.

1. New Variable: Click this to create a new variable
2. Delete Variable: This button is enabled only when you have an existing user variable. Select a variable that you have created and click this button to delete it.
3. Show System Variable: This button is used to toggle view between the list of system variables and user defined variables. You can click this and look at the number of system variables that exists in the package before you do anything in the package.
4. Show All Variable: The variable pane displays the variables based on which container you had last focus on the control flow or the data flow. In case you are in the data flow the variables in the data flow scope will be visible to you. Now, in case you want to view the entire variable you need to click this button, or go to the control flow and click outside the containers in blank space and then go to the variables pane. I guess clicking the above button is simpler.
5. Choose Variable Columns: Click to open the Choose Variable Columns dialog box where you can change the column selection. Use it to select the columns whose properties will display in the Variables window.

Once you create a new variable you will see the panel like the one in Fig 5. Here you see 4 columns by default, namely:

• Name: As the name suggest it shows the name of the variable, this is an editable field
• Scope: This is set automatically and you cannot change the scope of a variable once created.
• Data Type: This is an editable column with default value as Int32. It is a drop down with all possible data types in SSIS. Change this as per your need.
• Value: This will contain the value of the variable. It may be predefined or set by expressions (will talk about this in the net section), or SSIS configuration based on the requirement.

Note: Scope of the variable is set based on the container you have focus before clicking the new variable button. Though BIDS Helper can help you change the scope of the variable very easily that’s the 6th button provided by BIDS Helper on top of the variable pane.

Where to use Variables in SSIS Package?

You can use variables in SSIS in the following places:

• Setting us any connection string
• Setting up row count (one of the available transforms to get the rows of incoming data) transform in the data flow task
• Used in For Each Loop, For Loop, Parameter mapping in Execute SQL Task.
• Script task
• There are many more places where you can make use of variables and you will come to know that as and when you require them.

How do we set Variable values dynamically?

Having created variables in our package and used them, how can we set the variables values dynamically? For example, we need to get all the files names depending on the date of the package execution.

Let us take a scenario:

The package is scheduled to execute daily with no manual intervention. If the date of package execution is odd pick file one else pick file two.

To handle this situation we would have to have a variable store the date of package execution, but can we provide the value of the variable manually? No, as the package is automated and scheduled to run, it will execute automatically daily.

Hence we would need to use Expressions here to set up the value of the variable on the fly, when the variable is used that time the value would be set. This solves our first problem getting the date on the fly, now how do we select the file names based on the date would be taken up later.

To set the value of the variable by expressions follow the below steps:

• Select the variable you want to set up dynamically, right click and click Properties OR directly select the variable and click F4 button on your keyboard.
• On completing the above step a properties window would open on the right side of your BIDS.
• Here select the property EvaluateAsExpression and set the value to True
• Next, go to the property Expression and click the small button towards the end the one highlighted in the figure below

• On clicking this button an Expression Builder window will pop up which is shown in the figure below

• Expand the Variables section to view all the variables in the package, you could use them to create your expression. On the top right hand side you can see the various functions available for use.
• We need to get today’s date so we go to the Data/ Time functions, select GETDATE() and drag drop it to the Expression section below.
• Next, click on the Evaluate Expression button to validate that the expression is returning valid data. If the date returned is fine, click the OK button to close this window.
• Now if you go back to the Variables properties -> Expressions you will notice that now it has the value GETDATE(). When the package will execute this value will be calculated and set and we could use this as per our needs in the package.

To get detailed explanation in SSIS Expression please read my blog post SSIS- Expressions.

Points to note while using Variables:

• Check the scope of the variable
• Do not use excessive variables, delete the unwanted or unused variables
• Number of variables is inversely proportional to the manageability of the package :)
• While using a template or copy pasting a package remove unwanted variables
• Use Bids Helper for tracking variables with expressions(helps save a lot of time while debugging)