Integration Services Learning (4): variables and expressions

Variables, as programmers, are a concept that we are very familiar with. From the first day of learning programming, we need to know what variables are? How to define a variable? The scope of use of the variable. Is it necessary to talk about "variables" here? Well, the variables we are talking about today cannot be separated from this category: how to define a variable? How to Use variables? The scope of use of the variable. However, in the process of ETL development, from DTS of SQL Server 97 and SQL Server 2000 to SQL Server integration services 2005 and then to SQL Server integration services 2008, the variable has also undergone great changes, and its role has become more and more important. The components in the SSIS package originally wanted to be independent of each other and separated from each other, so they needed to transmit and exchange information through variables. Just like living in this city, we go home to live in a residential area and take a shuttle bus, but we never get together and never get to know each other. Now, a beautiful Messenger (variable) has suddenly passed information door-to-door, bringing everyone together and increasing communication and communication, since then, this community has become lively and harmonious with each other.

Variables in integration services are very similar to those in other programming languages-they are used for temporary data storage. But there are also some differences: the integration services variable, which is still an object, has its own property and has its own response event. Let's take a look at how to define variables.

Open integration services solution, open the SSIS (s) --- variable (s) menu, and a window will pop up.

 

 

In this window, we can understand the following variables:

(1) classification of variables. In integration services, there are two types of variables: System variables and User-Defined variables. System variables, such as taskid, taskname, createname, and createdate, provide some basic attributes for running the package, which can be used by components or code in the package. These variables are generally read-only. During the running process, they are generated by default according to the running environment of the package and cannot be modified. What about custom variables? Read-only and read-write can be defined in both cases.

(2) scope of the variable. There are both package-based global variables and local variables based on a certain component. This is a big difference from the previous DTS. the variables in the previous DTS package are all package-based global variables.

(3) There is no difference between the variable name, the Data Type and default value of the variable and other programming languages.

(4) Raise change event. This is a new property of integration services. It is a bool value. If its value is true, the variablevaluechangedevent will respond whenever the value of the variable changes. Otherwise, the event will not respond. (Events in SSIs will be discussed later. We will not go deep here ).

In addition to the attributes mentioned above, a variable also has an important attribute-the inheritance of variables. As mentioned above, variables include global variables and local variables, while local variables are visible to a certain component. However, components in integration services are hierarchical, that is, ancestor-level components, if there are child widgets, the Child widgets inherit the variables of the ancestor widget, which can be referenced, modified, or the variablevaluechangedevent event.

 

 

2. expression.

We have discussed the definition of table variables, the namespace of variables, and the scope of the variables, but we have not mentioned how to use variables. This is what we will introduce in this section-expressions.

"In SSIS solutions, there is no way to express the powerful functions of expressions. In SSIs, my favorite function is expression "-- Jamie Thomson

As Jamie said, the expression function is too powerful in SSIs. The attributes, variables, priority constraints, and for/foreach loop containers of components can all be set using expressions. The package becomes more powerful and flexible. Next we will talk about the application of several common expressions.

(1) attribute expression.

In integration services, most components have the expressions attribute,

 

 

Expressions is the attribute that defines the expression for the component. Click "..." to open the "attribute expression Editor" dialog box,

 

 

We can simply compare that most attributes of a text file connection object can be defined by expressions, which is powerful. Expression definition is also very convenient. There is a fully functional "expression generator" window,

 

 

This window has four areas, the "variable" area in the upper left corner, including global variables and local variables visible to this object. The upper-right corner is the "function" and "operator" areas, and the expression editing area is in the middle. The following shows the expression result display area. to verify whether the expression definition is correct, click the "computing expression" button to simulate the calculation result. Don't underestimate this button. It is very useful in debug expressions.

(2) variable expressions.

The definition of a variable expression is exactly the same as that of an attribute expression. Select the "expressions" attribute of the variable, open the Attribute Editor, and edit it.

(3) Priority constraint expression.

Generally, there are multiple task components in the control flow, such as script tasks, data flow tasks, and SQL script tasks. Sometimes we do not need to execute all these tasks, instead, you need to dynamically decide whether to execute the subsequent Task Based on the execution result of the previous task. This requires a priority constraint expression.

 

 

The priority constraint controls the package workflow. It determines whether the package constraints are met, and then selects whether to continue or stop running the package according to the constraints. In the previous DTS, the priority constraint was limited to the three statuses of the previous task: "successful", "failed", and "finished". Currently, in addition to the preceding three statuses, there is an extra "expression and constraint", that is, we can define the constraint by defining the expression, as shown in. If we need to define the number of errors more than five times, the package will fail, stop running. This requires expression constraints.

(4) Cyclic Capacity.

A "for loop" is a container component that allows some components to run the specified number of times repeatedly in this container. In this container, we need to define the initial value, definition expression, and definition assignment statement,

 

 

Common expression applications are the above. Of course there are some other types of expression definitions. We will not list them one by one. The usage methods are similar, as long as we hold the previous several types, these definitions are clear at a glance.

Today we are talking about two things: variables and expressions. These two aspects play a very important role in the SSIS package, as long as we fully and flexibly use them, it can make the package more elastic, flexible, and more vital. If you encounter this problem in your future work, you will not be disappointed.

 

Guidance:

SSIS Learning (1) Overview

SSIS Learning (2): Data Flow task (I)

Integration Services Learning (3): Data Flow task (2)