Tag Archives: Control Flow Objects

SQL Server Integration Services – For Loop Container

SSIS November 12, 2014 1 Comment

In my previous article we discussed about the Sequence container. In this article we will implement the For loop container. The objective of this container is that it will iterate through all the tasks inside until the condition is satisfied. For every  iteration the For Loop container evaluates the condition that is specified and repeats the process until the condition stands false.

The For loop container has 3 expressions that determines the number of executions that container would undergo. let us understand these expressions briefly.

InitExpression:

This is an optional expression in the For loop container. It is evaluated before any process is started inside the loop. The major use of this expression is to set an intial value as the name indicates.

EvalExpression:

The second expression when the loop starts. It is also evaluated before any process is executed inside the container, and then again evaluated at the beginning of each loop. This determines if the loop continues or ends.
If the condition stands TRUE, the process is executed again. If the condition stands FALSE, the loop ends.

AssignExpression:

The is the 3rd condition used in the For Loop. It is used to change the value of the variable used in the EvalExpression. This expression is evaluated for each pass through the loop as well, but at the end of the workflow. This condition is optional.

Implementation of the for loop Container

Lets implement the For loop container. I will be using the project I created in the my article.

Step 1: Open the project in BIDS -> Add a Execute SQL task on the UI design surface. Insert the following script in the task

CREATE TABLE Forloop
( id INT IDENTITY(1,1) NOT NULL,
Mydate DATETIME
)

Step 2: Drag and drop a For loop container inside the design UI surface -> Drag and drop a Execute SQL Task inside the For Loop Container -> Insert the following code inside the Execute SQL Task

INSERT INTO Forloop VALUES (GETDATE())

Step 3: Click on the variable tab -> create a variable called counter with data type as Int64 or Int32.
IS44Step 4: Right Click inside the For loop container and click on edit. Fill in the expressions as depicted in the pic below -> Click on OK.
IS45Step 5: Press F5 and make sure the execution succeeds
IS46
What we observe in the database is that the table has been created with 10 rows inserted in the table.
IS47
In this is article I have tried to implement the For Loop container in a very simplified manner. I hope I was able to do that.

 

 

 

 

 

Advertisement

SQL Server Integration Services – Control Flow Objects

SSIS November 6, 2014 1 Comment

In my previous article on SSIS we spoke about Packages,Connections and Data Sources. In this article we will talk about Control Flow Objects and how we can use them.

So what is Control Flow?

Control Flow is the engine which manages the workflow of the tasks created coupled with the control flow containers and constraints. SSIS provides a Design UI which displays a workspace where we can configure different control flow objects.

SSIS provides three types of control flow objects
1) Tasks – Tasks are objects that perform specific work
IS16
2) Containers – Containers help group different tasks
IS17
3) Constraints – Constraints help connecting different tasks and containers. Its also helps to define the order of execution of different tasks.
IS18

A package must contain at least one task that performs a certain operation. When multiple tasks are configured, then a container can be used to group them together.
IS19

 

Container

SSIS provides three types of Containers. Here is a brief description about the three Containers. We will deep dive into the Containers later.

1) Sequence Container : This allows us to group multiple tasks together. The benefits of this feature is that we can control the entire group rather that managing each task separately. It also means we can run different tasks sequentially or parallel. The entire group can collapsed or expanded. The Sequence container is the 3rd container in the toolbox
IS202) For Loop Container : This provides the same functionality as the sequence container but also lets you run tasks multiple times based on the condition provided. The For Loop is the 1st tool in the tool box
IS213) Foreach Loop Container : This allows looping but instead of a condition the looping happens over objects such as files and folders or tables in a database.
IS22

Implementation of Control Flow Task

We will now implement the above by creating and editing a control flow task and executing the package within the SSIS environment. We will be using the same project we created in our previous article for this demo.

Step 1: Open the Project using BIDS -> Select Mypackage.dtsx -> Click on the Toolbox -> Drag and Drop the Execute SQL Task on the Designer Surface. Rename the task as Update Table
IS23 IS24
Step 2: Right Click on the Task – > Click on Edit -> Change the Connection to AdventureWorks -> Click on the SQLStatement and insert the below query – > Click on OK.
IS25 IS26 IS27Query:

UPDATE Production.ScrapReason SET
ModifiedDate = '2014-06-01 00:00:00.000'
WHERE name = 'Drill pattern incorrect'

Step 3: click Start Debugging on the Standard toolbar -> If the execution is correct then the task would be green.
IS28 IS29Step 4: Click on the Progress tab to view the execution details of the task. -> Stop the execution by clciking on the stop debugging button – > Save the project.
IS30 IS31

The objective of this article was an introduction to the concept of Control Flow and Tasks with a small demo. I hope I was able to do that. In my next article I will write about Container with a broader perspective.

 

 

%d bloggers like this: