Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Free SQL Server Webcast > Building Really Fast SQL Server VMs
 

SSIS Variable and Expression Example - Part 1


By:   |   Read Comments   |   Related Tips: More > Integration Services Development

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.

Visual Representation of an Expression in SSIS

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 Builder

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.

SSIS Expression for Variables

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 Builder for SSIS

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.

Expression for SSIS Connection Manager

On the expression property, click on the button. This will open another dialog box to choose the property for the Connection Manager.

Property Expressions Editor

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.

Variables and Parameters in Expression Builder

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.

ForEach Loop Editor in SSIS

Now select the "Directory" property and click on the expression button.

Property Expressions Editor for a Directory

Now add the Folder variable name in the expression window and evaluate as shown below.

Expression Builder for the SSIS Folder Variable

Once the expression has been added, the expression can be seen in the main window as shown below.

Expression in SSIS for the Directory

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.

SSIS Expression for a Container

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.

Execute SQL Task Editor with Expressions

Now click on the expression and select the "SQLStatementSource" and click on the button to invoke the expression builder.

SQL Statement Source in the Property Expressions Editor

On the expression builder, type the below query and add the variable for the ProductCategoryName as shown below.

add the variable for the productcategoryname

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.

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.

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.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Nat Sundar Nat Sundar is working as an independent SQL BI consultant in the UK with a Bachelors Degree in Engineering.

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.



    



Learn more about SQL Server tools