How to flexibly use the SQL Server%. SSIS variable

With the continuous promotion and popularization of SQL Server 2005, more and more enterprise decision support projects adopt the SQL Server 2005 Business Intelligence solution. This article briefly introduces the use of SSIS user variables in the process of SSIS Development ETL (Extract-transform-load, data extraction, transformation, loading).

Introduction to SSIS Variables

SSIS (SQL Server Integration Services,sql Server consolidation Services) variables are divided into two types, one is the system variable, the other is a user-defined variable. A system variable contains very useful information about a package, container, task, or event handler. For example, at run time, the MACHINENAME system variable contains the name of the computer on which the package is run, and the StartTime variable contains the time when the package began to run. The system variable is read-only. In SSIS, a user variable is a variable that the user declares on demand during development. User variables can be used in scripts, in expressions that are used by precedence constraints, for loop containers, derived column transformations, and conditional split conversions, and in property expressions that update property values.

Variables declared in various programming languages can be scoped in general, SSIS variables are no exception, and SSIS variables are also scoped. According to the scope of action, variables are divided into package variables and component variables. Package variables can be invoked in any component of the package, and component variables can only be valid in the component that declares the variable. You can see the scope of the variable in the window of the variable (as shown in Figure 1). Begindate is the Testpackage package variable, and I is the variable that converts the data flow component to data.

Figure 1

Ii. declaration, assignment and use of SSIS user variables

1. Affirm variable

The declaration variable is very simple, if you want to declare the package variable, just click on the Control Flow tab and right-click in the package development area to select the variable command (shown in Figure 1), click the new Variable button to create a new variable, enter a name, select the data type, and then complete the initial assignment (Figure 2).

The red box is the new Variable button, and the blue box is the delete button. If you want to declare a variable that is used by a data Flow task component, simply double-click the Data Flow task component, right-click the Flow Control tab, select the variable command, and create a new variable in the variable window, where the variable is scoped to the Flow task component that you selected.

Figure 2

2. Assigning value

In the actual development, in addition to the variable in the time to assign values, the author also groped for two ways to assign values, one is by executing the SQL task component return value to the variable assignment, one is through the script component to assign values to variables.

Using the Execute SQL Task component method is to set the database connection properties of the component first, and then enter the SQL statement from the data to set the result set returned by the component as a single row. Click New in the result set interface, and in the result set, enter the name of the column returned in your SQL statement, and select the variable you want to assign to the variable Name column (the procedure is shown in Figure 3, Figure 4).

Figure 3

Figure 4

The SQL statement in the red box in Figure 3 is very simple, returns a single line, and the result is 1. In Figure 4, assign a row of the returned result column to the user variable I.

Using a Script Component assignment variable is simple, you just need to set the Script component's readonlyvariable or readwritevariable, set the variable's name to their value (multiple variables separated by commas), the difference is that the former in the script component can only read, the latter can read and write. The script component then passes the

Dts.Variables("i").Value = 1

3, the use of variables

Variables are used in SSIS in many places, the author introduces two typical applications.

(1) Execute the parameters of the SQL task component

Assuming that a date type variable startdate is declared, the user needs to pass StartDate as a parameter to the Execute SQL task component by selecting data from a table before the StartDate date. Enter the following command statement where the SQL task component is entered SQL:

SELECT * FROM TABLE_a WHERE 日期字段 < ?

Then add the mapping in the parameter map interface, select the user variable startdate in the variable Name column, select the type Date, and enter the name of the parameter in the parameter Name column. This allows you to pass the startdate variable to the SQL statement of the SQL task component.

(2) Assigning values in script components

You can pass Dts.variables ("I") in the Script component. Value = 1 way to assign to a variable, or you can use a variable in this way. such as Dts.variables ("other_variable"). Value = Dts.variables ("i"). Value+1, this statement can be executed in the Script component, the I variable plus 1 to assign a value to another variable.