MSBI # 13 – SSIS # 6 – Control Flow Tasks #1- What is Control Flow Task ? {Part – I}

I have already given some basic information regarding Control flow in my post https://bimentalist.wordpress.com/2011/07/30/msbi-6-ssis-2-gets-familiar-with-bidsssis-sql-server-integration-services/

Control Flow:

• Process is the key: precedence constraints control the project flow based on task completion, success or failure
• Task 1 needs to complete before task 2 begins
• Smallest unit of the control flow is a task
• Control flow does not move data from task to task
• Tasks are run in series if connected with precedence or in parallel
• Package control flow is made up of containers and tasks connected with precedence constraints to control package flow

Some Facts about Control Flow

1. Using a control flow task in an SSIS we can used to carry out an operation such as sending an e-mail message, executing a SQL statement, or copying a file from an FTP server.
2. The user makes use of the control flow to co-ordinate the execution of tasks simultaneously or to set precedence constraints based on the tasks completion status.
3. We have to always start development with this tasks only.

Control flow tab on fly

 

SSIS Package

• Containers provide structure to your packages. They group tasks and other containers into meaningful, logical work units.
• Tasks perform a wide variety of functions in your packages. SSIS packages contain two types of tasks; control flow tasks and data flow tasks. Control flow tasks perform a variety of workflow actions. For instance, the Execute SQL task executes SQL statements on the target database platform; the File System task can read and write from the host server’s file system; the File Transfer Protocol task uses the FTP protocol to transfer files to remote systems; and the Send Mail task uses the SMTP protocol to send e-mail messages. In addition, Maintenance Plan tasks perform a set of routine database maintenance operations. Maintenance Plan tasks includes the Backup Database task, the History Cleanup task, the Rebuild Index task, and the Shrink Database task. The most important and specialized tasks are data flow tasks that move data between different data sources. Using a data flow task, you can define a source and target destination such as a flat file, Excel, OLE DB, and SQL Server.
• Precedence constraints link the items in your package into a logical flow and specify the conditions upon which the items are executed. The three default precedence constraints enable you to control package execution control flow based on the completion, the success, or the failure of a task. For instance, you might create a precedence constraint that links an Execute SQL task and a data flow task where the package will only execute the data flow task if the Execute SQL task succeeds. SSIS supports creating precedence constraints based on an evaluation operator or the execution results of a task. An example of a precedence constraint that is based on an evaluation operator might use the contents of a variable or the evaluation of an expression to determine the flow to the next task.

SSIS Package Properties

Following properties are for each task in component and can be configuring by just

• Disable – If set to true, then the task is disabled and will not execute.
• DelayValidation – If set to true, SSIS will not validate any of the properties set in the task until run time. This is useful if you are operating in a disconnected mode and you want to enter a value that cannot be validated until the package is deployed. The default value for this property is False.
• Description – The description of what the instance of the task does. The default name for this is <task name>. This does not have to be unique and should accurately describe what the task does for people that may be monitoring the package in your operations group.
• ExecValueVariable – Contains the name of the custom variable that will store the output of the task’s execution. The default value of this property is <none>, which means that the execution output is not stored.
• Fail Package on Failure – If set to true, the entire package will fail if the individual task fails. By default this property is set to false.
• Fail Parent on Failure – If set to true, the task’s parent will fail if the individual task reports an error. The task’s parent can be a package or container. You’ll read more about containers later.
• ID – A unique ID that is associated with an instance of a task. The ID is in GUID format and looks like object GUID
• IsolationLevel – Specifies the isolation level of the transaction if transactions are enabled in the TransactionMode property. The values are: Chaos, ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, Unspecified. The default value of this property is Serializable.
• LoggingMode – Specifies the type of logging that will be performed for this task. The values are: UseParentSetting, Enabled, and Disabled. The default value of this property is UseParentSetting, which tells the task to use the logging mechanism for the package or container.
• Name – The name associated to the task. The default name is <task name>. As a SSIS designer, you probably want to change this name to make it more readable to at run time. This name must be unique inside your package.
• TransactionOption – Specifies the transaction attribute for the task. The values are: NotSupported, Supported, and Required. The default value of this property is Supported, which utilizes transactions in your task.

Control flow tab

We just have to drag and drop to use following component in SSIS package development

Category of Control flow task

Following is just logical segregation and representation of each and every task from control flow tab

1. Container and looping Tasks
2. Data Tasks
3. Execute Tasks
4. Scripting Tasks
5. Database Transfer Tasks
6. Analysis Services Tasks
7. File and Network Tasks
8. Instrumentation Tasks
9. Maintenance Plan Tasks
10. Custom Tasks

For sure we will have detailed level discussion on each category even on each task in our upcoming post

 

A control flow task is doing nothing in itself TO the data. It is executing some that itself may (or may not) act upon data somewhere. The data flow task IS doing something with data. It defines its movement and transformation.–Todd McDermidMVP, Moderator

 

If you wan more interesting link please visit our link resource website

Link Resource # 11 August 1 – August 2 « Dactylonomy of Web Resource

Hope this helps !!