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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
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.
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.
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.
) 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.
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.
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.
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.
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.
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.
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.
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.
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;
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.
|
Checks the allocation and structural integrity of database objects and indexes.
| |
Runs SQL Server Agent jobs.
| |
Runs Transact-SQL statements
| |
Deletes entries in the history tables in the SQL Server msdb database.
| |
Removes files related to maintenance plans, including reports created by maintenance plans and database backup files.
| |
Sends notification messages to SQL Server Agent operators.
| |
Rebuilds indexes in SQL Server database tables and views.
| |
Reorganizes indexes in SQL Server database tables and views.
| |
Reduces the size of SQL Server database data and log files.
| |
Updates information about the distribution of key values for one or more sets of statistics on the specified table or view.
|
