Shulou(Shulou.com)05/31 Report--

This article mainly introduces "introduction to SQL Server2008 SSIS variables". In daily operation, I believe many people have doubts about the introduction of SQL Server2008 SSIS variables. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "introduction to SQL Server2008 SSIS variables". Next, please follow the editor to study!

A brief introduction to SSIS variables

SSIS (SQLServerIntegrationServices,SQLServer Integration Services) variables are divided into two types, one is a system variable, a user-defined variable. System variables include very useful information about packages, containers, tasks, or event handlers. For example, at run time, the MachineName system variable contains the name of the computer included in the run, and the time when the StartTime variable package starts running. System variables are read-only. In SSIS, user variables are variables that users declare as needed during development. User variables can be used in scripts, in expressions used by precedence constraints, For loop containers, derived column transformations, and conditional split transformations, and in property expressions that update property values.

Variables declared in various programming languages generally have scope, and SSIS variables are no exception, and SSIS variables also have scope. According to the scope of action, variables are divided into package variables and component variables. Package variables can be called in any component of the package, and component variables can only be valid in components that declare variables. You can see the scope of the variable in the variable's window.

You can see that the scope of the I variable is the entire Package1 package, while the scope of the myconfig variable is the data flow task component.

II. Declaration, assignment and use of SSIS user variables

1. Declare variables

To declare a variable is very simple, if you want to declare a package variable, just click the Control flow tab, and then right-click in the blank space in the package development area to select the variable command, or the New variable button to create a new variable, enter the name, select the data type, and assign the initial value.

If you want to declare the variables used by a data flow task component, just double-click the data flow task component, right-click in the blank space of the flow control tab to select the variable name, and create a new variable command in the variables window. create a new variable in the variable window, when the scope of the variable is the flow task component of your choice.

2. Assignment

In actual development, in addition to assigning values to variables when they are declared, there are two ways to assign values to variables, one is to assign values to variables by executing SQL task components, and the other is to assign values to variables through script components.

In executing the SQL task, the component method is to first set the database connection properties of the component, then enter the SQL statement to fetch data from the database, and set the result set returned by the component to a single row. Click "New" in the result set interface, enter the name of the column you just returned by SQL in the column of the result set, and select the variable you want to assign in the variable name column.

Using the script component to assign variables is relatively simple, only need to set the script component's ReadOnlyvariable or ReadWriteVariable, set the name of the variable to their value (multiple variables are separated by commas), the difference between them is that the former can only be read in the script component, or can read and write. Then pass through the script component

Dts.Varables (variable name) .Value= value

3. The use of variables

Variables are widely used in ssis, and the author introduces two typical applications.

(1) parameters for executing SQL task components

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

SELECT*FROMTABLE_aWHERE date field

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.