[Translation] stairway to integration services level 6-SSIS workflow management basics

Introduction

In the previous chapter, we learned how to incrementally load data. In this article, we manage SSIS workflows through precedence constraints.

 

Add an SSIS package

 

Figure 1

Change the new package1.dtsx to precedence. dtsx:

Figure 3

 

Add a script task

Drag a script task to control flow:

Figure 5

Script task is estimated to be the most powerful component in SSIs. You can create many functions. Similarly, you can use script task to control SSIS workflow.

Right-click the script task and click "edit ":

Figure 6

Click general and change the name to "script Task 1 ":

Figure 8

Return to the script page and click readonlyvariables property:

Figure 9

Click the ellipsis to display a list of variables. Select the "System: taskname" variable:

Figure 10

After clicking OK, The readonlyvariables attribute is displayed as follows:

Figure 11

Click "Edit script" to open the script editing page. Figure 12:

Figure 12

Drag the scroll axis until you see sub main (). Then add the following code (I like to use C #.... So all my images will be updated to C ):

Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString        MsgBox(sTaskName & " completed.")

 

Figure 13

In SSIs, variables have a range and are attributed to a specificNamespaces. Each SSIS variable has a namespace. By default, there are two namespaces: system and user. taskname belong to system namespace.

Press F5 to test:

Figure 14

 

Add the Second Script task

Copy script Task 1:

Figure 15

Rename the copied script Task 1 to "script Task 2 ":

Figure 16

Run the following command:

Figure 17

 

Use Cases 0 and 1

Previously, it was a simple use case. this example shows how a workflow runs without a priority constraint. I call it case 0 ("Use Case 0 "). we add a priority constraint between script Task 1 and script Task 2. let's take a look at Case 1. the priority constraint is a green tip extended by script task1. 18:

Figure 18

Click priority constraint and drag it to script Task 2:

Figure 19

Run the package again. For example, run script Task 1 first:

Figure 20

After clicking the OK button, script Task 1 is executed, and script Task 2 is executed:

Figure 21

Note: script task2 is executed after script Task 1 is completed. The other one is that the default type of the Priority constraint is onsuccess. That is to say, onlySuccessfulAfter script Task 1 is executed, the priority constraint is verified.

This is very important. Priority constraints (Originates)Script Task 1 (Originates)Finally (Terminates) Script Task 2. Prioritize the tasks whose start point is the arrow link, and the tasks pointed by the arrow are the end points.

The priority constraint verifies one or more conditions. If the condition is true, the terminal task will be executed.

 

"Single thread"

"Single thread" is to execute one thing at a time. with priority constraints, You can implement "single thread" in the SSIS control flow ". priority constraint is the only method in control flow that sets the execution sequence of a workflow.

Can non-deterministic workflows be set in SSIs control flow? Right-click the priority constraint between script Task 1 and script Task 2, and then click Delete. click the blank area and press F4 to bring up the properties panel. maxconcurrentexecutables is used to set the number of concurrent tasks that can be executed by colleagues in the current SSIS control flow. the default value is-1. the default number of concurrent threads is the number of CPU cores + 2. for example, if you have a 4-core CPU, You can execute six tasks at the same time.

We set the attribute to 1. When we run the package again, we can see that only one script task is executed at a time:

Figure 22

Original Text connection: Http://www.sqlservercentral.com/articles/Integration+Services+ (SSIS)/91649/

Resource:My_first_ssis_project_after_step_6.zip

[Translation] stairway to integration services level 6-SSIS workflow management basics