Integration Services Learning (5): Container

Containers are a very important part of the integration services package. They can intuitively divide and organize tasks in the control flow, so that the package structure is concise, easy to manage, and easy to maintain. There are three types of containers in integration services: sequencecontainers, for loop, and foreach loop. The following describes how to use these three containers. (1) sequencecontainers ).A sequence container is a very simple but widely used container. It can structure the task flow components of the control flow, and put some business-related task components, put the package in a sequence container to make the entire package look more neat and beautiful, just like the bookcases and wardrobes in our house. We put different types of things in it and add them to our favorites, easy to use. For example, an integration Sevices package contains financial data and business data. There are many task components in the entire package. If you do not manage them by group, the entire package will look messy and difficult to manage and maintain. On the contrary, if we put the tasks that process financial data into a sequence container and put the tasks that process business data into another sequence container, the entire package looks clear, it is clear which tasks are to process financial data and business data.

In addition, if you click the arrow-like symbol in the upper right corner, you can fold these containers to make the entire package clearer and clearer.

(2) For Loop.

The for loop component is similar to the for loop in programming languages. When people need to execute the same job or a series of jobs repeatedly, they need to use the for loop, it can be used for both limited cycles and "Unlimited cycles". For example, when the error data is greater than or equal to 5, the package stops running, and notify the administrator, for example, to monitor whether new files are generated in a folder, whether network connections are normal, and whether various metrics (CPU and memory) of the Monitoring Server are normal, whether a specific event occurs can be set to an infinite loop.

The use of a for loop is very simple. It mainly has three expressions that control the execution and termination of the loop respectively,

 

According to, the value of the expression is completed, and the for loop is completed (@ errorcount is a user-defined variable. You need to define the variable according to the content of the previous lesson before using it here ). Then, drag the task that needs to be executed repeatedly into the for loop container.

　　(3) foreach loop.

In the. NET programming language, there is a loop called foreach. In terms of semantics, there is no difference between the two loops. In integration services, the foreach loop is the most important loop and the most frequently used loop. It is often used for every element in a set object, all scenarios to be processed. For example, enumerate all files in a folder, enumerate a record in a datatable, and enumerate each node in a tree. The following describes the two most common types of foreach: foreach file enumerator and foreach ADO enumeration.

　　Foreach file enumerator.

In general, we need to process every file in a folder cyclically. In this case, we need to use the foreach file enumerator. For example, we mentioned that iislog import has many iislog files, A lot of such files are generated every day. If you manually process them one by one, it will be unrealistic and the workload will be very large. If we use the foreach file enumerator, it will be very simple ., Open the foreach loop Editor:

　　Enumerator: Foreach Enumeration type. In a foreach loop, there are many enumeration types, each of which represents a use scenario, and their use methods are also different. For the meaning and usage of each Enumeration type, see the official documentation: http://technet.microsoft.com/zh-cn/library/ms187670 (SQL .90). aspx here is a very detailed description.

Select the "foreach file enumerator" and enter the folder location in the configuration items of the enumerator (note that the folder location is not the file location ), enter the file name wildcard (for example *. log

*. Txt, etc.). You can use the default settings. to traverse subdirectories in a folder, check the "traverse subfolders" option.

Next, select "variable ing ",

 

Click "OK" to return to the control flow Development panel.

Finally, in the Connection Manager, find the flat file connection mapped to the log file (iislog) and set the connectionstring = User: logfile of the expressions attribute.

Even if the foreach file enumerator is complete, F5 can achieve the desired effect. The result is as follows:

 

"File System Task"Is to copy the processed files to another folder, or delete the files to avoid repeated execution.

　　Foreach ADO enumerator: Records of each row in an enumeration table or table.

This enumerator is also widely used in the daily development process. Similar to the tsql cursor, select a batch of data, store it in the ADO record set, and then process it one row at a time.

Assume that a commercial company has a chain store in all parts of the country or throughout the city. Each store is sold and received through pos. In order to ensure fast and stable operation of front-end sales, pos systems generally adopt the C/S mode, and data and system programs are stored locally. In order to keep abreast of the sales and operation of each specialty store, the middle and senior executives of the group company need to synchronize the data of the store to the data center of the headquarters on a regular or irregular basis.

If we create a link for each store database and create another synchronization task, as the store increases, the synchronization task also increases, synchronization tasks with similar functions will become more and more, and each time a specialty store is added, it will be very difficult to manage an ETL package.

In my experience, I have created a table in the data center of the headquarters to configure the connection credendb (connectionstring) of the databases of each specialty store. In the same step, I first select the connectionstring of each database, then dynamically create a DB connection. In this way, the program is much smaller and more stable. Let's take a look at how to achieve this:

First, define two variables, one for storing connectionstring (Bytes type) and the other for storing ADO record set (object type ).

 

Add an SQL task to the control flow. The configuration is as follows:

　　Connection: Select the data center connector.

　　Sqlstatement: Enter the following SQL statement to retrieve all configuration records.

Code

Select 'data source = '+ strdbserverip +'; user id = '+ strdbuserid +
'; Initial catalog = humanresource; provider = sqlncli10; Auto translate = true;'

Connectionstring

From dbconfig

Resultset: Select"Complete result set".

Switch to "result set" and configure the result set options. (Note: User: dataset must be an object-type variable, and other variables are unavailable)

 

Click "OK" to complete the connection configuration of each dB. The following describes how the foreach ADO loop applies this result set.

Open the foreach loop editor and select "foreach ADO enumeration" in enumerator ",

 

If you select an incorrect option, you may not be able to achieve the desired effect.

　　Variable ing,

 

The last step is to map the variables to the ole db connection. From the Connection Manager, select dB connection and right-click connectionstring = User: connectionstring In the configured expressions attribute,

 

The entire foreach ADO loop has been completed, and F5 is all as you wish, and it will appear in front of you once again.

The three commonly used containers are described. Of course, there are some other containers, such as a package, which is a container and a group (select a batch of tasks in the control flow, right-click the "Group" menu ", this batch of tasks can be placed in a group) is also a container, but these containers are very simple, skip this and do not elaborate.

In inegration services, containers can also be nested. Each container can be nested with each other. They can have their own scopes and namespaces. If we use them well, it will provide a lot of convenience for structured development of integration services.

 

Guidance:

SSIS Learning (1) Overview

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

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

Integration
Services Learning (4): variables and expressions