Containers and data streams in SSIs-Brief Introduction

Container

A container is an object that provides a structure for one or more tasks. For example, you can execute a loop until the boundary condition is reached, or organize a series of tasks logically. A container can also contain other containers. Containers and tasks are also placed in the Control Flow Label. There are four types of containers: Task host, sequence, for loop, and foreach loop.

 

Task host containers

The task host is the default container of a single task. You cannot find the icon of this tool in the toolbar. In fact, even if you do not select a container, a single task is still contained in a task host. The structure of SSIS extends variable and transaction control through the task host.

 

SequenceContainer

The sequence container can process sub-tasks in a package and divide the package into smaller fragments that are easier to control. The following are some sequence container applications:

• Classify tasks so that some of the tasks that are no longer needed cannot be executed.
• Narrow down the variable range in a container
• Set attributes of all tasks in the container
• To ensure that a task is successfully executed before executing the next task.

Drag and Drop a sequence container in the control flow on the toolbar, 4-1, a sequence container on the left, including two tasks, and a foreach loop container on the right, tasks in the right container can be executed only after the tasks in the left container are successfully executed.

 

Figure 4-1

 

ForCircular container

For Loop containers are similar to otherProgramThe same language is used to create a loop. In this loop, SSIS sets an initial value, and continuously reassigns values in the loop, only when the boundary conditions are not met.

Double-click to open the for loop container editing page 4-2. Initexpression select item to set the initial condition of the loop. The evalexpression option is assigned a value for each loop. Once it is set to false, the loop stops. Assignexpression can be used to change the expression value during each loop.

Figure 4-2

Now let's test the usage of a circular container. In this example, We will test how to execute a script five times in a loop. This is a very simple example and some other functions can be added.

1. Create an SSIS project and change the default package to forloop. dtsx
2. Open this package, create a new variable counter, right-click on the panel and select variables, and click Add variable. The default variable type is int32.
3. Drag and Drop a loop container from the toolbox, double-click it to open the editing page, and set initexpression@ Counter = 0, The counter value of the variable is set to 0 at the beginning of the loop, and the evalexpression option is set@ Counter <5 and @ counter = @ counter + 1When the condition @ counter is met, <5 indicates that the loop is always executed, and the auto-increment value is 1. In the Name option, enter iterate through a script, and the last 4-3
4. Drag and Drop a script task in the loop container, double-click the script task, and name it pop up the iteration
5. On the script task editing page 4-4, enterCounterTo input a parameter variable to the task.
6. Click design script to open the Visual Studio editing environment. In the mian () method, enter the followingCodeThe Code displays the counter variable value in the pop-up dialog box based on the variable.

 
Public sub main () dim variables as variables if DTs. variables. contains ("counter") = true then DTs. variabledispenser. lockoneforread ("counter", variables) end if msgbox ("You Are In iteration:" & CSTR (variables ("counter "). value) DTs. taskresult = DTs. results. successential D sub
 

Note: Some codes in vs2008 are DTS. taskresult = scriptresults. Success.

7. Exit the Visual Studio editing environment and return to the original editing interface. Run the package result 4-5. Five pop-up boxes are displayed, from 0 to 4. The color of the task changes to green, then the right side turns yellow. The execution cycle turns green again.

Figure 4-3

Figure 4-4

 

ForeachCircular container

A foreach loop container is a powerful container that can perform cyclic operations on object sets. Get the related values in the object set during the cyclic operation. The object set type can be file, etc., which are listed below. You can also map values in an object set to variables. The object type varies according to the enumerator attribute settings in the tag collection. These attributes are as follows:

• For each file enumerator:Cyclically operate objects in a specified folder
• For each item enumerator:Perform cyclic operations on the set of manually set objects
• For each ADO enumerator:Cyclically operate rows in tables in An ADO set
• For each ADO. Net schema rowset enumerator:Perform cyclic operations on an ADO. Net Model
• For each from variable enumerator:Cyclically operate a group of SSIS Variables
• For each nodelist enumerator:Cyclically operate a group of XML node objects
• For each SMO enumerator:Manage a group of SQL statements.

Let's take an example similar to the for loop container. In this example, we will list all the files in a folder and output the file name information in the dialog box.

1. Create a new project and rename the default package to foreachloop. dtsx
2. drag a foreach loop container, double-click it to open the editing page, and rename it as iterate through files in the General tab.
3. In the collection tag, the default value of the newspaper enumerator attribute is foreach file enumerator, and a folder with fewer files is selected in the folder attribute. In this example, the file is not modified, only read the file information and keep the default value ". ", last interface 4-5
4. go to the variable ing label page and select in the variable column, The add variable dialog box is displayed. Name the variable filename. The default value of the variable in the index column is 0. Because only the file in a folder is operated cyclically, the default value 0 remains unchanged. Click OK to exit the editing interface.
5. drag and drop a script task to the foreach loop container and double-click it to open the editing page and rename it as read files
6. go to the script tag, set the variable filename in the readonlyvariale attribute, click design to open the Visual Studio editing environment, and use the following code to replace the code in the main () method

 
 Public sub main () dim variables as variables if DTs. variables. contains ("counter") = true then DTs. variabledispenser. lockoneforread ("counter", variables) end if msgbox ("You Are In iteration:" & CSTR (variables ("counter "). value) DTs. taskresult = DTs. results. successential D sub 
 
 note: In vs2008, part of the s code is DTS. taskresult = scriptresults. success 
 
 7. click OK to exit the editing page and run the package to get the result 4-6 (the pop-up content varies with the specific settings) 
 
 Figure 4-6 
 
 Figure 4-7