SSIS - Variable and Parameter

Ssis2

About

Ssis Variable And Parameter

Type

Variables

Ssis 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.

Ssis Variable New Scope

  • 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.

Ssis Project Params

  • 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")





Discover More
Ssis2
SSIS - Checkpoint (Process Restart)

Package niveau Herstartbaarheid van package:herstarten waar je had gefailed (Checkpoint file name xml format) checkpoint = herstartpunt Savecheckpoints If CheckpointFileExist, use it, if doesn't...
Ssis Expression Precedence Constraint
SSIS - Expression

Expressions can include variables and parameters, enabling to set values dynamically based on specific run time conditions for numerous elements, including: Properties Conditional Split transformation...
Ssis2
SSIS - Package

A package defines a workflow of tasks to be executed An unit of task flow execution An unit of deployment (package deployment model) You can execute each package independently, and you can also use...



Share this page:
Follow us:
Task Runner