Data Flow Task
Ø Data Flow Task: This task extracts data from a source, allows for
transformations of that data, and then the data is loaded into a target data
destination.
Data Preparation Tasks
Ø File System Task: This task allows the user to copy/move/delete
files and directories on a file system.
Ø FTP Task: This task allows the user to copy/move/delete files and
directories over FTP.
Ø Web Service Task: This task allows the user to
execute a Web service method and store the results
Ø XML Task: This task is used to work with XML data. XSLT can be used along
with XPath to validate, compare and merge documents. The results of this can
then be stored.
Ø Data Profiling Task: This task can be used for
checking and validating data quality. Profiles can be set up and checked for
varius data quality issues such as, column length issues, column patterns,
column statics, etc.
Workflow Tasks
Ø Execute Package Task: This task will run other SQL
Server Integration Services packages.
Ø Execute Process Task: This task will execute an
application or batch file.
Ø Message Queue Task: This task allows you to send and
receive messages between SSIS packages, or to send messages to an application
via an application queue. This task uses Message Queuing (MSMQ)
Ø Send Mail Task: This task allows for email messages to be created and
sent using an SMTP server.
Ø WMI Data Reader Task: This task allows a Package to
query, using WQL, computer systems (local and remote) for information regarding
that computer.
Ø WMI Event Watcher Task: This task watches for WMI events
that have occurred on a computer system, and allows the package to take an
action if certain criteria are met.
Scripting Tasks
Ø Script Task: This task can be used to program functions that are
not available in the standard SSIS tasks or transformations. In SSIS 2005 this
task can be programmed in VB .NET. In SSIS 2008 VB .NET and C# can be used to
program a Script Task.
SQL Server Maintenance Tasks
Ø Back Up Database Task: This task will allow you to
backup a one or many SQL Server databases.
Ø Check Database Integrity Task: This task will allow you to
check the integrity of all the objects in one or many SQL Server databases.
Ø Execute SQL Server Agent Job Task: This task
allows for the execution of a SQL Server Agent job.
Ø Execute T-SQL Statement Task: This task is similar to the
Execute SQL Task, however it only supports Transact SQL Statements. It should
be used for SQL Server specific SQL statements.
Ø History Cleanup Task: This task allows for the cleanup
of historical activity data. It will cleanup the history for database
maintenance plans, backup activites, restore activities and SQL Server agent
jobs.
Ø Maintenance Cleanup Task: This task allows for the cleanup
of backup files, and the reports of maintenance plans.
Ø Notify Operator Task: This task allows SSIS to notify
SQL Server Agent operators. They can be notifies by email, pager, or netsend.
Ø Rebuild Index Task: This task will rebuild an index
or indexes on one or many databases.
Ø Reorganize Index Task: This task will reorganize an index
or indexes on one or many databases.
Ø Shrink Database Task: This task will shrink the size
of the SQL Server database data and database log files.
Ø Update Statistics Task: This task will update the
statistics for one of many tables in one or many databases.
SQL Server Tasks
Ø Bulk Insert Task: This task offers an efficient
way to copy large volumes of data.
Ø Execute SQL Task: This task allows the execution
of a SQL statement. If the statement returns results, they can be stored in a
variable.
Ø Transfer Database Task: This task will copy or move a
SQL Server database between two instances of SQL Server. It can even be used to
make a copy of a database on the same server. Databases can be copied either
online or offline.
Ø Transfer Error Messages Task: This task will transfer a single
or multiple SQL Server user defined error messages between SQL Server
instances. It can be setup to transfer specific user messages or all error
messages.
Ø Transfer Jobs Task: This task will transfer a single
or multiple SQL Server Agent jobs between SQL Server instances.
Ø Transfer Logins Task: This task will transfer a single
or multiple SQL Server logins between SQL Server instances.
Ø Transfer Master Stored Procedures Task: This task will
transfer a single or multiple SQL Server Master database stored procedures
between SQL Server instances.
Ø Transfer SQL Server Objects Task: This task will transfer a single
or multiple SQL Server database objects between SQL Server instances. Most of
SQL Servers DDL objects can be copied with this task.
Analysis Services Tasks
Ø Analysis Services Execute DDL Task: This task will
run data definition language statements on Analysis Services. This allows for
the create, drop, alter of cubes, dimensions and mining models.
Ø Analysis Services Processing Task: This task will
process Analysis Services Cubes, Dimensions, and Mining Models.
Ø Data Mining Query Task: This task will run a DMX (Data
Mining Extensions) query that create a prediction based on new data that is run
against a Analysis Services data mining model.
Containers
Ø For Loop: Repeat a
task a fixed number of times
Ø Foreach Loop: Repeat a task by
enumerating over a group of objects
Ø Sequence: Group
multiple tasks into a single unit for easier management
