This is the 13th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Translation reference

Basic SSIS Workflow Management – Level 6 of The Stairway to – The Stairway to Integration Services Integration Services aims to have a comprehensive and clear understanding of SSIS. All contents are carried out on the basis of the original text. Due to various reasons such as version differences and personal doubts, the principle of complete translation is not adopted. Hopefully eventually it will be more conducive to learning and understanding SSIS,

Thanks for your support!

Basic SSIS workflow management

introduce

The previous section focused on implementing incremental loading of data. In this article we will focus on managing workflow using “Precedence Constraints” in SQL Server Integration Services Control Flow.

SSIS control flow consists of tasks, containers, and priority constraints (Tasks, Containers, and Precedence Constraints). Tasks and containers are executable items in the SSIS control flow; Priority constraints govern when or if tasks or containers are executed.

Add a Package

Before starting control flow, open the existing “FirstSSIS” project, right-click the “SSIS Packages “virtual folder in Solution Manager, and click” New SSIS Package “.

When “New Package” is clicked, a new package named “Package1.dtsx” is created and displayed. Right-click package1. DTSX and click rename. The package will enter rename mode and rename it to precedence. DTSX:

Add a script taskScript Task

The script taskScript TaskC# or VB.Net language for programming and more powerful extensions!

As you saw in the first demo, an SSIS package is valid without precedence constraints.

First, add a script task from the Control Flow toolbox to Control Flow. Click Script Task in the toolbox and drag it to Control Flow.

The script task is arguably the most powerful SSIS task in SQL Server Integration Services. You can use it to create many of the features included in other SSIS tasks. You can also use script tasks to add new functionality to the SSIS control flow.

Right click “Script Task” and click “Edit” to open Script Task Editor.

The basics of scripting task functionality are covered below.

First, you may want to check the “ScriptLanguage” property, where you can choose to use Visual Basic or C# as your programming language for each scripting task.

You can change the default language for the Script task from the Script-Language drop – down menu in the SSDT editor, choosing Tools → Options → Business Intelligence Designer → Integration Services Designer.

Click on the General page and change the Name property to “Script Task 1”.

Return to the Script page. Click on the “ReadOnlyVariables” property and the ellipsis on the right to open the list of available Variables. Select the “System::TaskName” variable shown below. Click OK to close the options window.

Here we need to configure the script on the current screen. Click the “Edit Script” button.

Error about opening edit script

Error “failed to load file or assembly”

It was thought that the error was related to not having a full Visual Studio installed, but later tests did not show this problem on other computers without full VS.

The system and SQL Server versions and configurations are the same. System update and SFC check did not resolve this error.

In the script code edit that opens, scroll to the Main function and add the following code:

var sTaskName = Dts.Variables["TaskName"].Value.ToString();
MessageBox.Show(sTaskName + " completed!");
Copy the code

The following information is displayed:

The first line creates a string variable named sTaskName (C#) and assigns it the value of the System::TaskName added to the ReadOnlyVariables attribute of the script task. Get the SSIS variable values (that is, the Dts.Variables section) by accessing the variable set of the Dts namespace. The key of the Variables collection is the name of the SSIS variable (get TaskName here).

Then get the property value, which is an object and needs to get its String value.

This implements reading from an SSIS variable to a “Script Task” variable (in C#)

In SSIS, variables have scope and belong to a namespace. Scope is the container to which SSIS variables belong, and each SSIS variable is associated with a namespace. By default, there are two namespaces — System and User. TaskName belongs to the system NameSpace and is represented by

::

.

Variable names are unique within a given scope and namespace. That is, the TaskName variable in the System namespace, and then, in the User namespace, you can also add a variable called TaskName

Variable names in different namespaces can have the same name. Variable names in the same namespace can not be repeated. Variable names in different namespaces do not affect each other.

The next line displays a message prompt box containing the task name (from the sTaskName variable).

Click the “X” in the upper right corner to close the Script Task Code Editor. The script is automatically saved. Click OK in the Script Task Editor to complete the Script Task configuration.

Let’s test it. Click “Start Debugging” or F5 to run the SSIS package. You will see the following message box:

Stop debugging.

Add the second script task

Adding the TaskName variable seems like a lot of extra work. But there’s a better way. Right-click on “Script Task 1”, click “Copy”, then right-click on the blank part of the control flow other than “Script Task 1”, and click “Paste”. A new script task is added to the control flow. Script Task 1 1. Rename it to “Script Task 2”

Re-executing the SSIS package will display two message boxes.

Use priority constraints (Cases 0 and 1)

The previous example is a “use case” example. The use case demonstrates the absence of precedence constraints. Call this “Use Case 0”.

For the current “Use Case 1” operation, we add a precedence constraint between Script Task 1 and Script Task 2.

Click Script Task 1. The green arrow at the bottom of Script Task 1 is a Precedence Constraint. Click and drag the priority constraint to Script Task 2.

When you run the SSIS package, Script Task 1 is executed first and then Script Task 2.

Another thing to note is that the priority constraint is of type OnSuccess, i.e. the previous Task — Script Task 1 — must be completed and successfully executed before evaluate.

Stop the current debug run.

Here semantics is very important. Priority constraints start with Script Task 1 and end with Script Task 2. The originating task connects to the priority constraint starting point, which has the “half-bubble” property and connects to the originating task at the starting point. An endpoint has an arrow where it connects to the terminating task.

Precedence constraints evaluate. They test one or more conditions, and if the condition is true, the constraint allows the termination task to begin execution.

“Single-threading”

“Single threading” is described as a way of doing one thing at a time. Using a priority constraint in an SSIS control flow is one way to implement a single thread.

Using priority constraints is the only way to complete a Deterministic Workflow in a control flow.

Can NON-Deterministic Workflows be used in SSIS control processes? The answer is “yes”. Right – click the priority constraint that connects Script Task 1 and Script Task 2, and then click delete. Click on the workflow blank and press F4 (or go to “Properties” on the right) to view package properties.

The MaxConcurrentExecutables property controls how many executables can be executed simultaneously in the SSIS control flow. The default value is -1. The default number of concurrent executions allowed by SSIS is equal to the number of detected CUP cores plus 2. For example, if you have a 4-core server, SSIS will allow 6 concurrent executions.

Set the MaxConcurrentExecutables property to 1.

Re-execute the SSIS package. Now only one script task is executed at a time. This order is non-deterministic and therefore unpredictable.

Stop debugging, reset the MaxConcurrentExecutables property to -1, and save.

conclusion

In this article, we add a new SSIS package to the solution, build our first script task, and connect them with the “OnSuccess” priority constraint. We tested reading SSIS variable values from inside SSIS script tasks and viewing and setting the MaxConcurrentExecutables property of SSIS packages.