"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.
Saturday, April 21, 2012
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:
Generally the Sequence container can be used in the below scenarios
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
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.
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
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
Now Execute the Task.
We got we want
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
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 Type | Connection 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. |
Subscribe to:
Posts (Atom)