Problem
What is a SQL Server Integration Services (SSIS) expression? How can we use SSIS expressions to modify the SSIS package properties during run time? What types of objects can invoke an expression?
Solution
Developing a static SSIS package is a relatively easy task, but the SSIS package cannot be used without modification in development, test and production environments. The name of the server, database name, source file folder location, the business conditions, etc. could vary based on the target server, so it is necessary to evaluate the value at run time. SSIS expressions help us to achieve this functionality. Let’s learn about some of the basic functionality for expressions and their usage.
SQL Server Integration Services Expressions
An expression is a code snippet which returns a value. This could contain symbols, identifiers, literals, functions and operators. An expression can be simple or complex enough to hold multiple expressions. They are very handy to dynamically update properties at run time.
Expressions are based on the expression language and expression evaluator. Once the expression is complete, the expression evaluator will parse the expression to validate the expression rules.
SSIS Expression Use Cases
Control Flow
- Variable values can be derived from other variables using expressions.
- Precedence constraints can make use of expressions to validate a condition.
- SSIS Tasks and Containers can also make use of expressions to set a property at run time.
Data Flow
- Expressions can be used to direct rows in a conditional split transformation. In this transformation, the expression must evaluate to true or false.
- A new column can be created with the help of an expression in the derived column transformation.
Visual Representation of an Expression
In the SQL Server Data Tools (SSDT) a small image will be displayed on the Task / Container / Connection Manager to denote that this object is using an expression.
In the below example, an expression has been used in the Connection Manager.
SSIS Expression Builder
Expression builder is a tool which helps us build an expression. This tool can be invoked from the expression property of a Task or Container. Also, this tool is available for the conditional split transformation and derived column transformation.
The Expression Builder will list all available variables and parameters based on the scope. In addition, this tool displays all SSIS functions (Math, String, Type casting, etc.). This is very useful for an SSIS developer who can drag and drop, then build the expression based on variables, functions and constants.
SSIS Expression for Variables
A variable value can be derived based on the expression. In the below example, the variable “ParentFolder” holds the value of the root folder for all data files. Another variable “ProductFileName” has been defined to have the absolute value of the file name.
As the root folder has already been stored in another variable, the value of the product data file can be derived as shown below.
As per the image below, the value of the variable “ProductFileName” can be derived from the variable ParentFolder. This is done by clicking the button next to the variable. The ParentFolder variable can be dragged and dropped from the variable list and the name of the file can be added in the expression. The expression can be validated using the “Evaluate Expression” button. The expression will be validated and the value will be displayed as shown below.
Expression for SSIS Connection Manager
In this example, let’s setup an expression for a Connection Manager. As the name of the database and the server will be different based on the target environment, two new variables have been created to store the values.
Now the ConnectionString can be built at run time based on the value of these variables. Select the Connection Manager and select the property window to add an expression.
On the expression property, click on the button. This will open another dialog box to choose the property for the Connection Manager.
Select “ConnectionString” as the property and click on the expression button. This will load the Expression Builder to build the expression. By adding the values of the server and database variables a connection string can be built as shown below.
Expression for an SSIS Container
Let’s assume that we have a root folder and we would like to iterate through the files in the folder to look for the CSV file then load the data into the table. The ForEach Loop container is one of the best options to accomplish this task. Let’s see how the expression can be used to setup the folder and file extension at run time.
The location of the folder may vary based on the environment (Dev, Test and Prod), hence it is preferable to setup the location of the folder and the file extension at run time.
Now select the “Directory” property and click on the expression button.
Now add the Folder variable name in the expression window and evaluate as shown below.
Once the expression has been added, the expression can be seen in the main window as shown below.
SSIS Expression for a Container
In this example, let’s setup an expression for a SQL Task based on the value of the parameter. We will be using the AdventureWorks database. A SQL task has been setup to count the number of products based on a given product category name. A variable has been created to store the product category name.
Now let’s add a SQL task to execute the SQL query. Once you setup the connection, select the expression tab to setup the SQL query as an expression.
Now click on the expression and select the “SQLStatementSource” and click on the button to invoke the expression builder.
On the expression builder, type the below query and add the variable for the ProductCategoryName as shown below.
Once the SQL query has been added, click OK to save the changes. Once done a small icon will be displayed in the right top corner of the SQL task to denote that a expression has been used in the task.
The SQL statement will contain the generated SQL statement.
Points to remember
The SQL statement generated by the expression is visible in the SQL statement, however the displayed query will be overwritten at run time by the expression. If you see a small icon (expression reminder) then you should update the SQL query in the expression.
Summary
The information listed in this tip will help you to kick-start the use of expressions in SSIS package development. Once you are familiar with the basics you can extend the expression with the help of functions and operators.
Next Steps
- Read more about SSIS expressions here.
- Examples for complex expressions can be found here.
- Complete list of operators & functions can be found here and here.
- Check out the SSIS resources on MSSQLTips.com.
