Understanding Control Flow Items and Maintenance Plan Tasks


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).

Control Flow Items

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.

Maintenance Plan Tasks

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



MCITP Administrator. Microsoft SQL Server 2005 Optimization and Maintenance Study Guide (70-444)
MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (Exam 70-444) Study Guide
ISBN: 0470127457
EAN: 2147483647
Year: 2004
Pages: 146

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net