Ads 468x60px

Tuesday, September 13, 2011

Learn SSIS : Contol flow task categorization

Like how we categorized data flow transformations in my previous post "Dataflow-transformation-categorization" here i have categorized various control flow tasks we have in SSIS.

The task have been categroized based on the functionality they provide in SSIS Control Flow.
Data Flow Task
The Data Flow Task category contains a single task that is used to extract data, apply column-level transformations, and load data in your SSIS package.
Data Preparation Tasks
Data Preparation Tasks allow you to copy files and directories, execute web methods, work with XML documents, and cleanse data in your SSIS packages.
Data Preparation Tasks include File System, FTP, Web Service, XML, and Data Profiling.
Workflow Tasks
Workflow Tasks are used in your SSIS packages to run other packages, programs or batch files, and send or receive messages between packages. These tasks can also send e-mail messages, read WMI data, and watch for WMI events.
Workflow Tasks include Execute Package, Execute Process, Message Queue, Send Mail, WMI Data Reader, and WMI Event Watcher.
SQL Server Tasks
SQL Server Tasks allow you to work with SQL Server objects and data in your SSIS packages.
SQL Server Tasks include Bulk Insert, Execute SQL, Transfer Database, Transfer Error Messages, Transfer Jobs, Transfer Logins, Transfer Master Stored Procedures, and Transfer SQL Server Objects.
Script Task
The Script Task category contains a single task that is used to execute Microsoft Visual Studio Tools for Applications, abbreviated to VSTA, scripts in your SSIS package.
Analysis Services Tasks
Analysis Services Tasks allow you to work with Analysis Services objects in your SSIS packages.
Analysis Services Tasks include Analysis Services Execute DDL, Analysis Services Processing, and Data Mining Query.
Maintenance Tasks, and
Maintenance Tasks allow you to perform administrative functions on the SQL Server in your SSIS packages.
Maintenance Tasks include Back Up Database, Check Database Integrity, Execute SQL Server Agent Job, Execute T-SQL Statement, History Cleanup, Maintenance Cleanup, Notify Operator, Rebuild Index, Reorganize Index, Shrink Database, and Update Statistics.
Backward Compatibility Tasks
Backward Compatibility Tasks are provided for backward compatibility with SQL Server Data Transformation Services or DTS (SQL SERVER 2000).
The two Backward Compatibility Tasks are ActiveX Script and Execute DTS 2000 Package.
In addition to the data flow tasks included with SQL Server 2008, you can also create custom tasks using any programming language that supports the Component Object Model or COM programming model.
Control flow containers provide structure to your package. They are used to group tasks together or allow one or more tasks to be repeated.

There are four types of control flow containers:
Foreach Loop
A Foreach Loop container executes its tasks once for each member of an enumerated collection. For example, the tasks in a Foreach Loop container could be executed once for every row in a table, or once for each file in a folder on the hard drive.
For Loop
A For Loop container executes its tasks repeatedly until a specified expression evaluates to false.
Sequence, and
A Sequence container simply creates a logical grouping of tasks that can be managed as a unit.
Task Host
The Task Host container is created for you automatically when you add a task to the Control Flow design surface in BIDS. This container contains only the task and allows container properties, such as checkpoints and transaction, to be configured on individual tasks.
Hope this consolidation helps ...

No comments:

Post a Comment