SSIS-variables and expressions

Variables and attribute expressions are very important components in SSIs design. They are the main means to improve SSIS flexibility. Variables and attribute expressions are correlated. variables are often used in attribute expressions, so I will introduce them together.

Variable

As early as in SQL Server DTS, the variable concept was introduced, and its definition is like that of otherProgramThe same language plays a role in repeated use of SSIS package in the program and flexible assignment. There is a difference between global face change and local variables in the programming language. This similar definition has been introduced since SSIS 2005.

There are two categories of SSIS variables: Package-level variables and container-level variables. Package
The level variable is similar to the definition of the global variable. It applies to the entire SSIS package.
Can be accessed from any component. A container-level variable is similar to a local variable. Its scope of use is limited to defining its container.
Or a control flow task. The names of variables in the same range cannot be repeated, but the names of variables in different ranges can be repeated!

SSIS variables are defined as follows:System Variables
AndUser-Defined variables
, System variables as the name suggests
It is a built-in system variable. We cannot add or delete system variables. We can only read their values but cannot modify their values. The system variable saves everything with the current package or
Information related to the container, such as the name, Creation Time, And version number of the container. If our package
With the error capture settings, the system variables store error messages. The access to system variables varies according to the tasks in the current package.

User-Defined variables are the most developer-Defined variables. They are often used to dynamically set accessible information in a package, or store dynamic values as intermediate media during processing. When using attribute expressions, we usually need to access system variables or use them to customize variables.

Whether it is system variables or User-Defined variables, they have a useful property: Raise change event. The default value of this property is false.
If its value is set to true, when the SSIS package is set to execute logging
The onvariablevaluechanged event can capture the changes to this variable. That is, by default
Onvariablevaluechanged attribute, if the raise change event of the variable is still the default value false
The change in the running value cannot be recorded by the log.

Attribute expression

Attribute expressions are relatively advanced topics in SSIs package development. They can be our SSIS package.
It becomes quite flexible. Attribute expressions exist in all control flow tasks and data connections. They are used to set certain properties of the task or data connection during the running stage. If a control flow task uses an attribute expression
If its value is set, the value it sets in the attribute expression will overwrite the value assignment in the design stage during the running stage.

Next I will use an example to introduce the use of attribute expressions. In this example, I use a variable to dynamically set the SQL statement value.

• In the previous lesson, I will introduce the example of for loop container.
• A new variable at the for loop container level is added: myname, data type is set to string, and its default value is set to thisname.
• Double-click Execute SQL task in for loop container to open the expression page.
  Insert records in the clients table. Open the attribute expression editor and select sqlstatementsource in the property column,
  Enter the following statement in the expression:

"insert into DBO. Clients (clientname) values ('" + @ [user: myname] + "' + convert (varchar ,?))"