Dynamic Package Objects Using Variables, Parameters and Expressions
SSIS includes multiple objects that can be used to create dynamic packages. Below Screen Shoot provides a graphical description of this model in SSIS. SSIS can dynamically set a property in a task or component using an expression, which can be built using a set of building blocks, including variables, parameters, functions, literals, and more. When the package is run, the expression is evaluated and used in the property as each task or component is accessed.
Variables are a key feature in the SSIS package development process. This object contains a value that can be hardcoded, dynamically set once, or modified multiple times throughout the execution of the package. Principally, variables provide a method for objects in a package to communicate with each other. Similar to their use in programming languages, variables are used in specific types of logic, such as iterating through a loop, concatenating multiple values together to create a file directory, or passing an array between objects.
Parameters were first introduced in SQL Server 2012. While similar to a variable in that a parameter can store information and be used in an expression, it has a few different properties and uses that you will want to understand. As demonstrated in below Screen Shot, parameters are set externally. The parameter can then be used in an expression to affect different properties in the package.
SSIS uses two types of parameters: project parameters and package parameters. Project parameters are created at the project level and can be used in all packages that are included in that project. On the other hand, package parameters are created at the package level and can be used only in that package. Project parameters are best used for values that are shared among packages, such as e-mail addresses for error messages. Package parameters are best used for values specific to that package, such as directory locations.
When using the project deployment model , parameters are the best choices to replace package configurations to create a dynamic and more flexible SSIS solution. Using the Required property of the parameter, you can also necessitate the caller of the package to pass in a value for the parameter. If you want to set the value of a property from outside the package, either required or not, parameters are the object to use. On the other hand, if you want to create or store values only within a package, variables are the object to use.
Expressions are the key to understanding how to create dynamic packages in SSIS. One way to think about expressions is to compare them to the familiar model of a spreadsheet cell in a program like Microsoft Excel. A spreadsheet cell can hold a literal value, a reference to another cell on the spreadsheet, or functions ranging from simple to complex arrangements. In each instance, the result is a resolved value displayed in the cell. Figure 5-3 shows these same capabilities of the expression, which can hold literal values, identifiers available to the operation (references to variables or columns), or functions (built-in or user-defined). The difference in the SSIS world is that these values can be substituted directly into properties of the package model, providing powerful and dynamic workflow and operational functionalities.
Starting with SQL Server 2012, it is easy to see when an expression has been set on a property within an object. Expression adorners are special icons that are placed on top of the object icon if the object has an expression. This indicator makes it easier to understand why the package seems to be doing something behind the scenes that you weren’t expecting!
If you understand these visual analogies explaining how expressions, parameters, and variables fit into the SSIS picture, then you are almost ready to dig into the details of how to build an expression. First, however, it’s time to take a look at some of the details about data types, variables, and parameters that cause many of the issues for SSIS package developers.