Saturday, April 21, 2012

Error – MSDTC Service Not Running - SSIS 2008

Error – MSDTC Service Not Running

"The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Start the Distributed Transaction coordinator service.

Sequence Container - SSIS 2008

Sequence Container
The Sequence container defines a control flow that is a subset of the package control flow. Sequence containers group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow.

There are many benefits of using a Sequence container:

·         Disabling groups of tasks to focus package debugging on one subset of the package control flow.

·         Managing properties on multiple tasks in one location by setting properties on a Sequence container instead of on the individual tasks.

·         Providing scope for variables that a group of related tasks and containers use.

Generally the Sequence container can be used in the below scenarios

a.       For transactions :

If we have multiple set of operation to be executed and if any one of the execution is failed then all the executed statement tollback

b.      For Subset of Multiple task

If you want to execute subset of tasks in a group. Eg. I have set of tasks like task1, task2, task3, task4, etc..

Task1 and task2 be executed if some condition is true, else task3 and task4 should be executed

How to Work:

                It is very simple to give sample for sequence container.

                Step a: Drag a sequence container

                Step b: Drag 3 Execute SQL Tasks into the Sequence container

                Step c: Create a OLE DB connection Manager

                Step d: Set valid SQL statement for any two of the SQL Task and for one define invalid SQL Statement.

We will get the below result based on the value set for transactionOption

If the transactionOption is set to ‘Not Supported’ you can see the two tasks are executed successfully and one is failed and overall the sequence container is failed

If the transactionOption is set to ‘Required’ you can see the all three tasks are failed and overall the sequence container is failed

ForEach Loop Container - SSIS 2008

ForEach Loop Container

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in other C#/ Vb.Net languages. In a package, looping is enabled by using a Foreach enumerator.

For Loop Container - SSIS 2008

For Loop Container

Purpose: It loops to execute a task/set of task till a specified condition is true.
Eg : I am going to display a message using the for loop container and the script task (Easy oneJ)
Step1 : Define as user variable

Step2: Drag the For Loop Container from the ‘Control Flow’ tool bar
Step3: Define the For loop conditions

                InitExpression   : Define the initial Condition

                EvelExpression  : It is the condition by which the loop executes. The loop will occur till this condition is true

                AssignExpression : It defines what is to be done after executing each loop
Step4 : Drag the ‘Script Task’ into inside the For Loop container
Click the ‘Edit’ option and Click the ‘Edit Script’ option

Write the code to display the looping value as shown below
And compile the code to make sure no mistake made.

Now close the Script edit window and select the ‘loopcount’ variable in the ‘ReadOnlyVariables’ option of Script Task.

Now Execute the Task.

We got we want

Thursday, April 12, 2012

SQL 2008 R2 SSIS Conenction Types

Connection TypeConnection Description
ADO To Connect ADO objects (Eg : Recordset)
ADO.NET To connect data sources through an ADO.NET provider (Eg : Data
CACHE To Connects a cache either in memory or in a file
MSOLAP100 To connect an Analysis Services database or cube.
EXCEL To connect an Excel worksheet.
FILE To connect a file or folder.
FLATFILE To connect delimited or fixed width flat files.
FTP To connect an FTP data source.
HTTP To connect an HTTP data source.
MSMQ To connect a Microsoft Message Queue.
MULTIFILE To connect a set of files, such as all text files on a particular hard drive.
MULTIFLATFILE To connect a set of flat files.
ODBC To connect an ODBC data source.
OLEDB To connect an OLE DB data source.
SMOSever To connect a server via SMO.
SMTP To connect a Simple Mail Transfer Protocol server.
SQLMobile To connect a SQL Server Mobile database.
WMI To connect Windows Management Instrumentation data.