Tag Archives: Integration

SQL Server Integration Services – Sequence Container

SSIS November 8, 2014 1 Comment

In this article we will understand how we can use a Sequence Container with a demo. In my previous article I wrote about Control Flow Objects in SSIS.

Sequence Container and its benefits

Sequence container groups related tasks in a package and helps in understanding the complexity of a package in a simpler way. The objective of the Sequence container is to have multiple separate control flows grouped together in the package.

Some of the benefits of the package are as below:
1) Helps in debugging.
2) If a package has multiple tasks then it is easier to group the tasks in Sequence Containers and we can collapse and expand this container for usability.
3) We can set the property of entire container rather than setting the property of individual tasks.
4) We can set the flexibility of completion of the process in the container to a transaction level i.e in case of an error the entire process can be rolled back or partial success can be achieved.

Implementing Sequence Container

Now we will implement the sequence container with a demo. In the first example we will not use TransactionOption but in the second example we will. Let us begin. We will be using the project we created in my article.

Step 1: Open the SSIS Project -> Click on the package MyPackage.dtsx -> Drag and drop a Sequence Container from the toolbox on the design surface -> Drag three Execute SQL Task objects in the Sequence ContainerIS32Step 2: Rename the 1st Task as Create Table. Rename the second task as Insert Values. Rename the 3rd Task as Update Values -> Drag the constraint from the 1st task onto the Insert Values Task and then drag the constraint from the Insert Values Task onto the Update Values Task. The structure should look like below
IS33Step 3: Right click on the Create Table Task -> Click Edit -> Change the connection to AdventureWorks2008 -> Click on the SQLStatement and insert the following code -> Click on OK.

CREATE TABLE [SequenceContain]
(
id INT NOT NULL,
NAME VARCHAR(20),
age INT
)

Step 4: Implement the same for Insert Values Task apart from the SQL. Insert the below SQL code

INSERT INTO dbo.SequenceContain VALUES
(1,'Amit A',34),(2,'Amit B',34),
(3,'Amit C',24),(4,'Amit D',44),
(5,'Amit E',64),(6,'Amit F',34)

IS34Step 5: Implement the same for Update Values Task apart from the SQL. Insert the below SQL code

UPDATE SequenceContain
SET name = 'Bodhi'
WHERE id = 3

IS35Step 5: Press F5  and the package execution begins. The entire package will turn green when the execution succeeds
IS36Step 6: Lets verify via SQL Server if the table and data has been created and modified.
IS37

TransactionOption

Example 1:

Now lets tweak the third task i.e Update Values task and enforce an error. We will change the TransactionOption to NotSupported.

Step 1: Right click on the Update Task -> Goto SQLStatement -> insert the following code below

UPDATE SequenceContai
SET name = 'Bodhi'
WHERE id = 3

We have kept the name of the table incorrect to enforce the error.

Step 2: Select the Sequence Container -> On the properties tab -> TransactionOption -> Select Not Supported.
IS38Step 3: Not lets drop the table from the database so that the create table does not error out.
IS39Step 4: Press F5 and run the package and lets observe what happens
IS40

Observation: What we observe is that the package has failed(red) and the update values Task has also failed(red). Let now have a look at the database and see what impact has this package made.
IS41What we observe is that create table and insert values task has succeeded but the update values task has failed. So this what NotSupported does. It does not do a full roll back.

Example 2:

Now we will change the TransactionOption to Required and observe what happens
Step 1 : Drop the table from the database
Step 2 : Change the TransactionOption to Required
IS42Step 3: Press F5.
IS40
Observation:

The Package fails but lets verify what has happened at the database level by running a select on the table.
IS43
The entire process has been rolled back. The table structure does not exist even though the create table had succeeded. So this is what The Required option does. In case of an error it rolls back the entire package.

In this article I have tried to explain the use of the sequence container in a 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: