In the following sections, we’ll conduct a very high-level review of all the containers and tasks available for use on the Control Flow tab. Some are very specialized (the Message Queue and Data Mining Query tasks), while others are likely to appear in almost every package you build (the Data Flow, Execute SQL, and Send Mail tasks).
Most of the following controls’ names make their purpose easy to understand. In the following list, we describe possible uses that might help you retain their functions a bit better:
For loop container Loops through a list of items; this is less frequently used than the ForEach loop container. It has the ability to process files in any folder and to process that folder recursively, without code, and it succeeds even if there are no files in the folder. It can also loop through some other items such as ADO and ADO.NET rowsets as well as less common objects such as SMO enumerators.
Sequence container Creates a logical subset of a package. Sometimes this is just to make it easier to arrange things in the designer, but more often it’s because all the items in the container need to be in the same transaction. It doesn’t do anything; it’s just a container.
ActiveX Script task Executes legacy (VBScript) code until it can be upgraded to full Visual Basic .NET code.
Analysis Services Execute DDL task Creates, alters, or drops cubes, models, and objects within Analysis Services.
Analysis Services Processing task Processes cubes, models, and mines in Analysis Services.
Bulk Insert task Inserts data from a text file into a SQL table or view, and does so pretty quickly
Data Flow task You’ll use this one a lot. Remember that you have as many Data Flow tasks as you need, and that each functions as a container for Data Flow transformations. The Data Flow tab is where you manage the workflow within each Data Flow task.
Data Mining Query task Runs prediction queries based on data mining models.
Execute DTS2000 Package task Executes legacy DTS packages. This lets you use existing DTS packages as reusable components without any modifications to the DTS packages.
Execute Package task Executes other SSIS packages.
Execute Process task Launches an external application or batch file.
File System task Manages files and folders, including attributes.
FTP task Uploads or downloads data from an FTP server. Note that if the data is on your own network, you’ll generally want to use the File System task.
Message Queue task Sends and receives messages from an MSMQ server.
Script task Executes fully compiled .NET code that you create in a real IDE.
Send Mail task Sends mail; requires an SMTP server. It does not require a Database Mail profile to use this task. It’s also worth remembering that mail can be sent from within the Script task using the System.Net.Mail namespace.
Transfer Database task Copies or moves a SQL database, either to the same server or to a different one.
Transfer Error Messages task Copies custom SQL Server error messages to a different server.
Transfer Jobs task Copies jobs to a different server and can be set to overwrite, skip, or fail if it detects a duplicate job.
Transfer Logins task Transfers all SQL logins, specified logins, or logins that have access to a specified database.
Transfer Master Stored Procedures task Transfers one or more master stored procedures to another server.
Transfer SQL Server Objects task Copies tables, stored procedures, and so on, to another server.
Web Service task Executes a web service method. Often this will be used to retrieve data from a web service-one common example is retrieving the weather for a particular ZIP code. Visit http://wsfinder.jot.com/WikiHome for more examples of web services.
WMI Data Reader task Executes queries written in Windows Management Instrumentation Query Language (WQL); this is used to return information about a computer.
WMI Event Watcher task Watches for WMI events using a WQL event query; examples include watching for the installation of an application and running a package when system resources reach a given level or when a file is dropped into a folder.
XML task Retrieves XML documents, reformats them, or selects portions of an XML document.
The following tasks are all designed to be used by maintenance plans, and their titles are pretty clear. Remember that they aren’t limited to maintenance plans, though; you can use them for your own purposes, just as you can now tweak maintenance plans far more than was possible in SQL Server 2000.
Backup Database task
Check Database Integrity task
Execute SQL Server Agent Job task
Execute T-SQL Statement task
History Cleanup task
Maintenance Cleanup task
Notify Operator task
Rebuild Index task
Reorganize Index task
Shrink Database task
Update Statistics task