MSBI # 14 – SSIS # 7 – Control Flow Tasks # 2- Details of Control Flow Task {Part – II}

In this post we will have details of following Control Flow Task and remember this is just introduction to task category

Container and looping Tasks

The container and looping tasks allow us to repetitively run a set of tasks for a set number of times or for each element in a collection, such as all files in a folder.
Use containers like the ForEach Loop and For Loop to execute a set of tasks multiple times.
For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics.
The Sequence container groups together several tasks.
Use it to define a transaction boundary around a set of tasks so they all fail or succeed together. Or, use it simply to reduce the clutter on the design surface by hiding the detailed steps within the sequence.
We used a sequence container in the package illustrated in Figure 5.2, for the Sales Reason and Sales Reason Bridge tables.
You can also group control flow objects, and collapse or expand those groups. There’s no task for grouping.

Task Under Container and looping are as follows

Containers task are as follows

Data Tasks

The data tasks allow you to perform Extract, Transform and Load (ETL) operations on your data and thus full manipulate the data.
In particular the Data Flow Task, as this task allows you to full edit the ETL process on the Data Flow Tab within the package.

Task Under Data flow are as follows

Data flow task are as follows

Execute Tasks

The execute tasks allow you to call other packages or components such as ActiveX scripts, VB.NET scripts and even executable.
The Execute Package task executes an Integration Services package.
This is used to break a complex workflow into smaller packages, and define a parent or master package to execute them.
Create a separate package to populate each table in your data warehouse database.
The use of parent and children packages enables the modularization and reuse of complex logic.
Parent package is also know as master package.
If you have DTS2000 packages running already in your production system, you can run them
with the Execute DTS2000 Package task.
The Execute Process task will run any operating system process.

Task under Execute Task are as follows

Execute task are as follows

Scripting Tasks

These tasks are available to perform an endless array of operations that are beyond the scope of the standard tasks.
The ActiveX Script task is provided for backwards compatibility to DTS2000; use the Script task for new work.
The Script task uses Visual Basic .NET from the Visual Studio for Applications environment. Or, you can use any .NET language to create a custom task that will become available in the list of control flow tasks.
Defining a custom task is a programming job, rather than simply scripting, but has the significant benefit of re-use.

Task under Scripting Task are as follows

Execute task are as follows

Database Transfer Tasks

The database transfer tasks allow you to move databases around as well as supporting system data such as agent jobs, error messages, master stored procedures and logins.
The Transfer Jobs task transfers one or more SQL Server Agent jobs between instances of SQL Server.
The Transfer Master Stored Procedures task transfers one or more user-defined stored procedures between master databases on instances of SQL Server. To transfer a stored procedure from the master database, the owner of the procedure must be dbo.
The Transfer SQL Server Objects task transfers one or more types of objects in a SQL Server database between instances of SQL Server. For example, the task can copy tables and stored procedures. Depending on the version of SQL Server that is used as a source, different types of objects are available to copy. For example, only a SQL Server 2005 database includes schemas and user-defined aggregates.

Task under Scripting Task are as follows