SSIS - Variable and Parameter
About
Articles Related
Type
Variables
You can use variables to:
- Set property values for tasks and other objects.
- Store an iterator or enumerator value for a loop.
- Set input and output parameters for a SQL query.
- Store results from a SQL query.
- Implement conditional logic in an expression.
Two kinds of variables: user variables and system variables.
To create a variable, SQL Server - Integration Services (SSIS) > SSIS > Variable
- Scope: The scope is determined by the object that is selected when you create the variable. You can change it with the “Select New Scope” screen.
- Namespace: By default, user variables are defined in the User namespace, but you can create additional namespaces as required. System variables are in the System namespace.
- Raise Change Event: Causes an event to be raised when the variable value changes. You can then implement an event handler to perform some custom logic.
- IncludeInDebugDump: Cases the variable value to be included in debug dump files.
Parameters
You can use parameters to pass values to a project or package at run time. For example, you could use a parameter to specify a database connection string.
Parameters initialization can take place in three places (by order of importance)
| Value | Description |
|---|---|
| Execution value | A value specified for a specific execution of a package. |
| Server default value | A default value assigned to the parameter during deployment. |
| Design default value | A default value assigned to the parameter in the design environment. |
SSIS supports two kinds of parameter (tow scopes):
- Project parameters. They can be used in any packages within the project.
- Package parameters. They are only available within the package for which they are defined.
Parameters are only supported in the project deployment model. When the legacy deployment model is used, you can set dynamic package properties by using package configurations.
Script
Script variable access:
dts.Variables("count")