SSIS Package Control Flow Components

The control flow in the Integration Services package is constructed by using different types of control flow elements: the containers that provide structure in packages and services to tasks, tasks that provide functionality in packages, and precedence constraints that connect containers and tasks into a control flow. Below is the description of the heavily used component in control flow task;

1.) Sequence Container:  This container can be used when business wants to create logical group of control flow tasks.

Example: Consider a scenario when based on some condition we want to perform set of operation. In that case we can combine task corresponding to conditional result and accommodate tasks in various container where we can flow the control of the SSIS package.

2.) For Loop Container: This component is used when business wants to execute set of tasks multiple time based on some conditions.

Example: Consider a scenario when we want to perform data transfer from source to destination for 5 files stored in the database. With condition set as the 5 iteration, we can run the control flow tasks 5 times. 

3.) For each Loop Container: This component is used when business wants to execute set of tasks based on some dataset that is mostly dynamic in nature. This dataset can be accommodating more conditional information compare to for loop container. 

Example: Consider a scenario when we have files stored in some folder and for each file we want to fetch data and transfer file data to destination database.

4.) Analysis Services Execute DDL Task: This component can be used when business wants to run the OLAP query on SSAS cubes.

Example: Consider a scenario when we want to create replica of the cube. Using this task we can fire OLAP query that can create replica of SSAS cube.

5.) Analysis Services Processing Task:  This component can be used to process the SSAS cube full or partial mode.

Example: Consider a scenario when SSIS job is populating data into data warehouse that contains fact and dimension table and we want to process the cube after pushing data into dimension and fact tables.

6.) Bulk Insert Task: This component can load data into a table by using the BULK INSERT SQL command.

Example: Consider a scenario when we have large flat files in some folder and we want to transfer data from flat files to SQL server. If we have flat file size large and we want to transfer data using traditional data flow task, it might affect performance of the package. Using bulk insert task we can push data into destination with performance improvement but here we cannot transform the data in between as we can do in data flow task.

7.) Data Flow Task:  This component should be used when business wants to perform ETL operation i.e. Extract data from source, Apply Transformation to data and Load data into destination database.

8.) Execute Package Task: This component can be used when business wants to execute SSIS packages from some other SSIS packages.

Example: Consider a scenario when we have data warehouse and we want to execute dimension packages and fact packages from a master package that control execution hierarchy.

9.) Execute Process Task: This component can be used when business wants to runs an application or batch file as part of a SQL Server Integration Services package workflow.

Example: Business can use the Execute Process task to expand a compressed text file. Then the package can use the text file as a data source for the data flow in the package.

10.) Execute SQL Task: This component can be used when business wants to execute SQL statement of SQL objects like function, stored procedure on a particular DBMS.

11.) File System Task: This component can be used to perform file operation such as to crate directory and files, copy or delete directory or files, to move files or directory, rename files, set attributes etc.

12.) FTP Task: This component can be used when business want to download and uploads data files and manages directories on servers.

Example: a package can download data files from a remote server or an Internet location as part of an Integration Services package workflow. You can use the FTP task for the following purposes:

·         Copying directories and data files from one directory to another, before or after moving data, and applying transformations to the data.

·         Logging in to a source FTP location and copying files or packages to a destination directory.

·         Downloading files from an FTP location and applying transformations to column data before loading the data into a database.

13.) Message Queue Task:  The Message Queue task allows you to use Message Queuing (also known as MSMQ) to send and receive messages between SQL Server Integration Services packages, or to send messages to an application queue that is processed by a custom application. These messages can take the form of simple text, files, or variables and their values.

By using the Message Queue task, you can coordinate operations throughout your enterprise. Messages can be queued and delivered later if the destination is unavailable or busy.

Example: The output from a restaurant cash register can be sent in a data file message to the corporate payroll system, where data about each waiter's tips is extracted.

14.) Script Task: This component can be used when business require code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides. This code can be written in C# or VB.NET.

Example: A script can use Active Directory Service Interfaces (ADSI) to access and extract user names from Active Directory.

15.) Send Mail Task: This component can be used when business want to send email notification. Business can configure subject, body text along with details such as CC, BCC, from, to, attachments etc.

Example: Send mail task can be used to send notifications that indicate execution of SSIS package and end of failure of SSIS package.

16.) Transfer Database task: This component can be used when business wants to transfers a SQL Server database between two instances of SQL Server only. The Transfer Database task supports SQL Server 2000 and SQL Server. It can transfer a database between instances of SQL Server 2000, instances of SQL Server, and from an instance of SQL Server 2000 to an instance of SQL Server.

17.) Transfer Error Messages Task: This component can be used when business wants to transfers one or more SQL Server user-defined error messages between instances of SQL Server. User-defined messages are messages with an identifier that is equal to or greater than 50000. Messages with an identifier less than 50000 are system error messages, and cannot be transferred by using the Transfer Error Messages task. The Transfer Error Messages task supports a source and destination that is SQL Server 2000 or SQL Server. There are no restrictions on which version to use as a source or destination.

18.) Transfer Jobs Task: This component can be used when business wants to transfers one or more SQL Server Agent jobs between instances of SQL Server. There are no restrictions on which of the two versions to use as a source or destination.

19.) Transfer SQL Server Objects Task: This component can be used when business wants to 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. The Transfer SQL Server Objects task supports a source and destination that is SQL Server 2000 or SQL Server. There are no restrictions on which version to use as a source or destination.

20.) Web Service Task: This component can be used when business wants to execute a Web service method and utilize its result. You can use the Web Service task for assigning value to a SSIS package variable or for writing value to flat/XML file that has been returned by Web service method.

Example:  Business could obtain the highest temperature of the day from a Web service method, and then use that value to update a variable that is used in an expression that sets a column value.

21.) Maintenance Plan Task: SQL Server Integration Services includes a set of tasks that perform database maintenance functions. These tasks are commonly used in database maintenance plans, but the tasks can also be included in SSIS packages. The maintenance tasks can be used with SQL Server 2000 and MS-SQL Server databases and database objects. The following table lists the maintenance tasks and its usage;

Task	Description
Back Up Database Task	Performs different types of SQL Server database backups.
Check Database Integrity Task	Checks the allocation and structural integrity of database objects and indexes.
Execute SQL Server Agent Job Task	Runs SQL Server Agent jobs.
Execute T-SQL Statement Task	Runs Transact-SQL statements
History Cleanup Task	Deletes entries in the history tables in the SQL Server msdb database.
Maintenance Cleanup Task	Removes files related to maintenance plans, including reports created by maintenance plans and database backup files.
Notify Operator Task	Sends notification messages to SQL Server Agent operators.
Rebuild Index Task	Rebuilds indexes in SQL Server database tables and views.
Reorganize Index Task	Reorganizes indexes in SQL Server database tables and views.
Shrink Database Task	Reduces the size of SQL Server database data and log files.
Update Statistics Task	Updates information about the distribution of key values for one or more sets of statistics on the specified table or view.