Variables and Parameters in SSIS

 

Variables

Variables store values that a SQL Server Integration Services package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.

You can use variables in Integration Services packages for the following purposes:

• Updating properties of package elements at run time. For example, you can dynamically set the number of concurrent executables that a Foreach Loop container allows.

• Including an in-memory lookup table. For example, a package can run an Execute SQL task that loads a variable with data values.

• Loading variables with data values and then using them to specify a search condition in a WHERE clause. For example, the script in a Script task can update the value of a variable that is used by a Transact-SQL statement in an Execute SQL task.

• Loading a variable with an integer and then using the value to control looping within a package control flow. For example, you can use a variable in the evaluation expression of a For Loop container to control iteration.

• Populating parameter values for Transact-SQL statements at run time. For example, a package can run an Execute SQL task and then use variables to dynamically set the parameters in a Transact-SQL statement.

• Building expressions that include variable values. For example, the Derived Column transformation can populate a column with the result obtained by multiplying a variable value by a column value.

System and user-defined variables

Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.

All variables-system and user-defined-can be used in the parameter bindings that the Execute SQL task uses to map variables to parameters in SQL statements. For more information, see Execute SQL Task and Parameters and Return Codes in the Execute SQL Task.

You can create user-defined variables for all Integration Services container types: packages, Foreach Loop containers, For Loop containers, Sequence containers, tasks, and event handlers. User-defined variables are members of the Variables collection of the container.

If you create the package using SSIS Designer, you can see the members of the Variables collections in the Variables folders on the Package Explorer tab of SSIS Designer. The folders list user-defined variables and system variables.

You can configure user-defined variables in the following ways:

• Provide a name and description for the variable.

• Specify a namespace for the variable.

• Indicate whether the variable raises an event when its value changes.

• Indicate whether the variable is read-only or read/write.

• Use the evaluation result of an expression to set the variable value.

• Create the variable in the scope of the package or a package object such as a task.

• Specify the value and data type of the variable.

The only configurable option on system variables is specifying whether they raise an event when they change value.

Reference: Variables

Package and Project Parameters

Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.

In SQL Server Data Tools you create, modify, or delete project parameters by using the Project.params window. You create, modify, and delete package parameters by using the Parameters tab in the SSIS Designer. You associate a new or an existing parameter with a task property by using the Parameterize dialog box. For more about using the Project.params window and the Parameters tab, see Create Parameters. For more information about the Parameterize dialog box, see Parameterize Dialog Box.

Parameters and Package Deployment Model

In general, if you are deploying a package using the package deployment model, you should use configurations instead of parameters.

When you deploy a package that contains parameters using the package deployment model and then execute the package, the parameters are not called during execution. If the package contains package parameters and expressions within the package use the parameters, the resulting values are applied at runtime. If the package contains project parameters, the package execution may fail.

Parameters and Project Deployment Model

When you deploy a project to the Integration Services (SSIS) server, you use views, stored procedures, and the SQL Server Management Studio UI to manage project and package parameters. For more information, see the following topics.

• Views (Integration Services Catalog)

• Stored Procedures (Integration Services Catalog)

• Configure Dialog Box

• Execute Package Dialog Box

Parameter Values

You can assign up to three different types of values to a parameter. When a package execution is started, a single value is used for the parameter, and the parameter is resolved to its final literal value.

The following table lists the types of values.

PARAMETER VALUES

Value Name	Description	Type of value
Execution Value	The value that is assigned to a specific instance of package execution. This assignment overrides all other values, but applies to only a single instance of package execution.	Literal
Server Value	The value assigned to the parameter within the scope of the project, after the project is deployed to the Integration Services server. This value overrides the design default.	Literal or Environment Variable Reference
Design Value	The value assigned to the parameter when the project is created or edited in SQL Server Data Tools. This value persists with the project.	Literal

You can use a single parameter to assign a value to multiple package properties. A single package property can be assigned a value only from a single parameter.

Executions and Parameter Values

The execution is an object that represents a single instance of package execution. When you create an execution, you specify all of the details necessary to run a package such as execution parameter values. You can also modify the parameters values for existing executions.

When you explicitly set an execution parameter value, the value is applicable only to that particular instance of execution. The execution value is used instead of a server value or a design value. If you do not explicitly set an execution value, and a server value has been specified, the server value is used.

When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.

Reference: Parameters