MSBI SSIS sequence containers

MSBI SSIS package control flow is useful for performing various tasks and designing execution workflow. A control flow container plays an important role in the design of workflows. SSIS Toolbox includes the following containers.

MSBI SSIS control flow

For container loop in MSBI SSIS

With a set number of executions, you may use this container to execute all the inside activities. In the programming language, you should consider it as similar to For loop.

To get in depth knowledge on Microsoft business intelligence, enrich your skills on Msbi online training

Container with for each loop in MSBI SSIS

It works similar to For loop; however, you specify a list to evaluate the number of For loop executions, rather than a fixed number of executions. In the Date Order article you can read more about using SSIS ForEach Loop containers to process files.

Package Series in MSBI SSIS

For SSIS the sequence container is useful for grouping together tasks. With this, you can split the control flux into several logical units. In this post, you'll discuss more on the Sequence jar.

Sequence Tank Description at MSBI SSIS

You may consider a Sequence Container as an SSIS package sub-set. For the tasks specified within a container it acts as a single control point.

You can sum up the advantages of a sequence bin, as seen below:

In a sequence container, you can identify variables within the scope of the tasks

This follows a relationship between parent and child, with the underlying tasks. You may alter a sequence container (parent) property and it is propagated to tasks within (child)

It enables flexibility in the handling of tasks in containers

Practical application of Container Series in MSBI SSIS

Let's be basically exploring the sequence jar.

Suppose you have a routine flow of control to perform the following SQL tasks:

Tasks for MSBI SSIS kit

At the moment you have a similar procedure for every function that runs every day. SQL Server Agent also operates on a set schedule. Now your engineering team has created separate stored procedures for each day of the week, due to some business requirements.

For each day you can create separate SSIS packages and schedule jobs for the SQL agents. This increases the difficulty and versatility of package management:

Separate regular MSBI SSIS kit-7 SSIS packages

The Series Container package in SSIS solves this problem for us. Let's go over the solution.

Drag a sequence container from the SSIS toolbox into the field of the control flow. There are currently no related tasks to it.

Drag a sequence container to manage flow area from SSIS toolbox

Double-click and rename Series Container to Sunday as shown below.

Null series In MSBI SSIS container

Now, inside the Sunday container drag and drop the SQL task 1 You get the following error message that a linked function is not transferred to a new container. Task 1 of SQL linked to other tasks using preceding container:

Email mistakes in MSBI SSIS

You may eliminate the precedence constraints or jointly pick and transfer all SQL tasks in the container.

You will see bold outlines for each task until you pick all of the tasks together:

Choose all the task together

Now transfer them together in SSIS within the Sunday sequence container and resize the container so you can address another sequence container on the screen as well. I renamed these jobs, and gave them a shorter name: Learn more from Msbi online course

Transfer duties to a bin in MSBI SSIS

Create identical copies of the sequence container with correct scripts in the SSIS packet for the rest of the week.

Note: Within the container you do not cover the configuration of the individual tasks. When using this article you will have clear knowledge of the SSIS.

Now my SSIS kit looks like below for every day of the week with a Sequence Container in SSIS.

SSIS kit with sequence container looks as below

Now, if you run the SSIS packet, each sequence container will be executed individually.

You can see in the following screenshot that task 1 fails for each sequence container, and it marks container failure.

Lost communications in MSBI SSIS

This did not execute task 3, since task 3 has many precedents, and all inputs to a task should be valid by default.

Right-click on the preceding and shift to Logical OR:

Previous Publisher in MSBI SSIS

The strong corresponding lines are shifted to dotted lines. Fix the problem and execute the package and you can see that each sequence includes individual runs inside the task:

SSIS settings in MSBI SSIS

Now you have to execute the container Series based on the day of the week. Right-click the kit for this, and add a variable:

System Variables in MSBI SSIS

Click Add variable and assign the variable a name, data type. The vector reach is at packet level by default. For the current weekday you'll use this variable:

Adding Variable in MSBI SSIS

To find the day of the week, add a new execute SQL task and rename:

Add a new SQL function to run, and rename to find the weekday.

Double-click on the job, and the editor window will open. Make the following modifications to this editor.

Set result: Single row

Connection: Specify the connection details for SQL instances

SQL Statement: Copy and paste the T-SQL in this editor

DATENAME(dw, GETDATE)) (DAYWEEK AS;

Project Editor for SQL

Navigate to the Result set and map the SSIS variable query output to:

Set the result set to

Select OK and enter SSIS precedence cap from SQL task to Sunday Sequence container:

Put a limit on precedence

Double click on this restriction of precedence and alter the property as follows:

Process of evaluation: Expression and constraint

Value: Erfolg

Speech to:

@[User::Journal]=="Sunday

Preceding Limit Editor

To check the expression, you can click on the test. For successful validation it gives the following message.

Speech authentication

Click OK and in SSIS you can see the following precedence restriction configuration with Sunday sequence container:

Precedence limit of expression

Now my setup for the SSIS kit looks like on the following screenshot.

Complete Kit

This SSIS kit must flow to:

Firstly, it executes the SQL function Find the weekday

This assigns SSIS attribute the day of the week meaning

You specified expression in the preceding constraint to test for the day of the week

This tests the expression and implements the limitations of the sequence if the expression is valid

Timeline

Here you are going. In the following screenshot, you can see that in SSIS only the container of the Wednesday Series is run.

System execution based on day of the week

An additional property inside SSIS of a series container

You can also deactivate a Sequence Container to prevent execution. Right-click it, and then press Disable:

Disable the Container series

The Series Container is disabled. The role within it also grew grey:

You can also build nested containers for the Series. You've inserted a Sequence Container inside the Sunday Sequence Container in the following screenshot. When task 2 is successful it activates the execution of the nested container:

Nested jar of sequence

Within SSIS bundle, you can collapse or extend a Sequence Container by clicking on the arrow as shown below:

Containers extend or collapse

You can also configure a proprietary sequence container. Few important attributes are as follows.

●        Fail Package On Failure: it tests whether the package fails actions in the event of an executable failure

●        Maximum Error Count: In SSIS, it lists the maximum number of errors within a sequence. Unless the error numbers are less than this value, even in the event of failure a sequence container will be marked as good. The default value here is 1

●        Isolation level: It supports the Serializable isolation level by default

●        Disable: Using this property, you can allow or disable a sequence container in an SSIS package

Package Sequence Properties

Conclusion

In this article, you have seen about sequence containers. This is useful to combine tasks and to establish the workflow of the packages. This container should be exercised and used according to your requirement. You can learn more through Msbi online training Hyderabad