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

This article focuses on "what is the SSIS variable in SQLServer". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what is the SSIS variable in SQLServer"?

Develop ETL (Extract-Transform-Load) in SSIS, the process of data extraction, transformation, and loading. We need to define our own variables.

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.

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

At this point, I believe you have a deeper understanding of "what is the SSIS variable in SQLServer". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.