Tag Archives: MSSQL Server

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 – 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.

 

 

SQL Server Integration Services – Packages, Datasources and Connections

SSIS November 4, 2014 Comments: 4

In this article we will see how we can create a SSIS project via visual studio and then create a package and linking the packages to data sources along with connections. For this demo i will be using SQL Server 2008. The steps remain same for SQL Server 2008 R2 and SQL Server 2012.

creating a SSIS project…

Step 1: Open SQL Server Business Intelligence Development Studio(BIDS). Go to File -> New -> Project
IS1

 

 

 

 

 

 

 

Step 2 : ON the new Project dialog box choose Integration Services Project. Give the Project a name and location as shown below. Click on OK. The  Project will appear on the Solution explorer. By default a package is already created named Package.dtsx
IS2IS3

Step 3: let us rename this package as MyPackage1. Right click on the Package and select rename. Change the name to MyPackage.dtsx. Click Yes on the confirmation dialog box.
IS4IS5
Step 4: We will create two more packages named DC.dtsx and DP.dtsx. Right click on SSIS package on solution explorer and click on New SSIS Package. A package would be automatically added with default name Package(n).dtsx. We will rename the package as DC.dtsx. Repeat the same steps and create another package named DP.dtsx. Click on save all button to save all the changes made.
IS6 IS7So this is how we will create a SSIS project with packages. In the next section we will create data sources for the project.

 creating data sources..

Step 1: Open the Project in BIDS. Right click on Data Sources and select New Data Sources. On the Data source wizard click on new and select a server name and database name. In this case we will choose AdventureWorks as our database.
IS8 IS9 IS10Step 2: Click on OK. Click Next and click on Finish. The new data source will now appear under Data Sources
IS11 IS12 In the next section we will create new package connections from the Data Sources we created.

Creating Connections

Step1 : Click any one of the packages. Right Click on the connection managers window and select New Connection from Data Source. Select the Data source we created name AdventureWorks. Click on OK.
IS13 IS14The new connection connection will now appear in the connection managers window for the package. We need to create the same for the rest of the two packages.
IS15

 

%d bloggers like this: