Variable Scope - ( SSIS Expressions and Variables )

Syntax

In SSIS, variables can have either package scope or task scope.

Package Scope

@[Namespace::PackageName]::VariableName

Task Scope

@[Namespace::PackageName::ObjectName::TaskName::VariableName] 

To assign a value to a variable:

@[Namespace::PackageName]::VariableName = Value

To concatenate two variables:

@[Namespace::PackageName]::Variable1 + @[Namespace::PackageName]::Variable2

To add two variables:

@[Namespace::PackageName]::Variable1 + @[Namespace::PackageName]::Variable2

Example

@[User::ID] = 1
@[User::Name] = "John"
@[User::FullName] = "John Doe"

"User " + (DT_WSTR, 10)@[User::ID] + " is " + @[User::Name] + ", full name : " + @[User::FullName]

Output

User 1 is John, full name : John Doe

Explanation

• We have created three variables @[User::ID], @[User::Name], and @[User::FullName]. The first variable holds an integer, while the other two variables hold strings.
• In the example, we are concatenating all three variables into a single string

Use

• Variables are used to store values that can be used throughout the SSIS package or in specific tasks.
• They can be used to manipulate data or control the flow of the package.

Important Points

• SSIS variables support a wide range of data types, including integers, strings, dates, floats and even objects.
• Variables can be assigned at runtime using expressions or by using script tasks.
• Variables can be used to enable or disable control flow tasks based on the package's outcome.

Summary

In SSIS, the scope of variables can have either package scope or task scope. Variables are used to store values that can be used throughout the package or in specific tasks. They support a wide range of data types and can be assigned at runtime using expressions or by using script tasks. Variables can be used to manipulate data or control the flow of the package.