Saturday, 21 September 2013

SSIS Control Flow Objects and Tasks

When you have an SSIS project open within BIDS and are designing a package, the tabs across the top of the SSIS Designer let you choose an SSIS component to work with. The Control Flow design surface is the first tab and displays a workspace where you configure control flow objects.
There are three primary types of control flow objects:
Control flow tasks Workflow objects that perform operational-level jobs
Control flow containers Provide a grouping mechanism for tasks and other containers
Constraints Let you connect tasks and containers and define execution ordering and precedence

A control flow task is an SSIS component that performs an operation such as sending an e-mail message, executing an SQL statement, or copying a file from an FTP server. When a control flow task is complete, it either succeeded or failed. You use the control flow to coordinate the execution of tasks in parallel or to set precedence constraints based on the tasks’ completion status.

To create a new control flow task in package, drag the task from the toolbox to the Control Flow tab in the SSIS Designer
After you add a task to the control flow workspace, you need to configure the task to perform the specific operation you selected. To allow configuration, you will have to EDIT the task.

Few  Highlighted Tasks

"ActiveX Script Task" Runs Microsoft Visual Basic Scripting Edition (VBScript) and JScript code and is included mainly for legacy support when a Data Transformation Services (DTS) package is migrated to SSIS.

"Analysis Services Execute DDL Task" Runs XML for Analysis (XMLA) code against an SSAS database. XMLA is the data definition language (DDL) for SSAS; therefore, this task lets you perform common structure changes such as adding partitions to cubes.

"Analysis Services Processing Task" Allows the processing of SSAS objects through an SSIS package.

"Bulk Insert Task" Allows the execution of bulk copy operations for SQL Server. This task works only against a SQL Server Database Engine.

"Data Flow Task" Allows data processing from sources to destinations.

"Data Mining Query Task" Performs data mining queries and lets you capture the results for analysis.

"Data Profiling Task" Allows the analysis of source data for patterns, missing data, candidate keys, and statistics. These results typically inform developers about what logic to include in their SSIS packages based on their data needs.

"Execute DTS 2000 Package Task" Runs a DTS package within SSIS.

"Execute Package Task" Runs other SSIS packages either deployed to SQL Server or in the file system.

"Execute Process Task" Runs a command-line operation such as program or batch file execution.

"Execute SQL Task" Runs SQL code against any underlying database connection in the SQL language of the connected database engine.

"File System Task" Lets you copy, move, and delete files as well as perform other file and folder operations.

"FTP Task" Sends and receives files between the file system and an FTP server and performs simple file and folder operations on the FTP server.

"Message Queue Task" Integrates with Message Queuing (MSMQ) on a server running Windows to read and send messages.

"Script Task" Runs Microsoft Visual Basic 2008 or Microsoft Visual C# 2008 within an SSIS package.

"Send Mail Task" Sends an e-mail message through an SMTP server.

"Transfer [Object] Task" Tasks that copy SQL Server objects from one system to another, including databases, SQL Server Agent jobs, error messages, logins, master stored procedures, and database-level objects.

"Web Service Task" Lets you connect to a Web service to send or receive information.

"WMI Data Reader Task" Lets you run a Windows Management Instrumentation (WMI) query against the operating system to capture server information.

"WMI Event Watcher Task" Waits for a particular event before executing.

"XML Task" Combines, queries, and differentiates multiple XML files on the server.