In the previous article of our series dedicated to SQL Server 2000 Data Transformation Services, we covered the first of the essential elements of a package - connection. Now it is time to look into another critical component - task - that is responsible for the processing of data retrieved through connections. Tasks are represented by icons in the Task area of the DTS Designer window and can be added to a package by simply dragging them with the mouse cursor to the package window area. A number of them will require an existing connection (or connections - in which case you will be prompted).
There are 17 built-in tasks with varying degrees of complexity in SQL Server 2000 DTS. We will start at the lower end of the spectrum by reviewing 11 of the more straightforward ones and continue with the rest in our next article:
Bulk Insert task - functions as a wrapper for the T-SQL BUILK INSERT command, so its purpose is to provide a speedy way to import the content of a delimited text file, (when configuring the task parameters, you can specify either row and column delimiters or use a format file), into a SQL Server database table. The main drawback of this task is its lack of flexibility. In particular, no data transformations can be performed during bulk import. (If this is a requirement, you might need to resort to the Transform Data task, which we will discuss later). In addition, the column mapping feature is not available (which means that fields and text files and columns in a table need to match), and data transfer can only be performed from a text file to a SQL Server. You can, however, using the Options page on the Bulk Insert Task Properties dialog box (accessible by double-clicking the task icon in the DTS Package window), specify a number of T-SQL BULK INSERT parameters, such as constraints checking (CHECK_CONSTRAINTS), identity insert settings (KEEPIDENTITY), rows to be copied (FIRSTROW and LASTROW), type of data file (DATAFILETYPE), or a batch size (BATCHSIZE).
Copy SQL Server Objects task - duplicates any set of objects such as tables (including their data), views, stored procedures, constraints, triggers, indexes, users and roles, logins, primary and foreign keys, as well as object level permissions between two SQL Server databases. You should avoid using this task exclusively for copying data (Transform Data task is more efficient). In addition, if your intention is to copy an entire database, you might want to consider utilizing Copy Database Wizard (or Transfer Databases task, which is discussed later). Even though the wizard requires for a source database to be offline during its operation (unlike the Copy SQL Server Objects task), it is superior in terms of speed.
Execute Process task - runs any Win32 executable or batch file. For either one, you can specify input parameters, timeout period (after which the process will be terminated), and return code (integer value indicating success). The process executes in the security context of the account that launched the DTS package.
Execute SQL task - provides the ability to execute SQL statements (including stored procedures). The statements can be divided into batches (separated with the GO command). The Execute SQL Task properties dialog box allows setting the timeout period, after which the corresponding task is forced to terminate. SQL statement can be parsed for syntactical correctness. While this capability seems to be rudimentary, it can be significantly enhanced by employing input/output parameters and global variables in SQL statements.
Global variables are used to store data, which can then be shared across multiple components of the same package, or even exchanged among multiple packages. Since global variables for a package are stored in a single location and they can be altered dynamically during package execution, they also simplify package maintenance and customization. We will discuss global variables in more detail in one of one of the future articles of this series.
Input parameters appear as question marks in the SQL Statement portion of Execute SQL Task. Their sequential position determines whether they are referred to as Parameter 1, Parameter 2, etc. on the Input Parameters tab of the Parameter Mapping dialog box (accessible by clicking on Parameters command button in the Execute SQL Task Properties dialog box). Entries in the Parameter Mapping section (located on the same tab) specify how input parameters are mapped to global variables. Similarly, on the Output Parameters tab, you can create a mapping between global variables and output parameters of the SQL statement. The output parameter can consist of a single row or a rowset (if your query returns multiple records).
Execute SQL task is frequently applied following tasks performing data import (e.g. to force rebuilding indexes or updating statistics).
File Transfer Protocol task - accommodates common requirements in mixed or distributed environments where data needs to be copied via File Transfer Protocol. In the previous versions of SQL Server, this had to be handled via workarounds involving third party programs (or custom, in-house solutions). SQL Server 2000 DTS has this functionality built-in.
File Transfer Protocol task invokes the FTP process between an Internet (or intranet) site or a server directory and local file system. Connection parameters (such as username, password and number of retries) are set from the Location tab of the File Transfer Protocol Properties dialog box. On the same tab, you need to specify the destination directory path. Once the existence of the target data store and access rights to it are verified, you can click on the Files tab within the same dialog box and specify individual files to be transferred to the destination. By checking on the Overwrite checkbox, you can also designate whether target files should be overwritten in case they already exist.
Send Mail task - allows sending an e-mail as part of a DTS package. This typically is used to provide confirmation about execution results for monitoring purposes. Such results can be either specified as part of the message body or delivered in the form of an e-mail attachment. Before you set up this task, you first need to install a MAPI compliant client (such as Outlook) on the system where the package will be executing and configure appropriately a profile for this client. What tends to be a source of confusion is the fact that such a profile might need to be configured for multiple accounts - depending on how a package is executed. This might be your account, if you launch packages interactively, but more frequently it is an account used to run SQL Server Agent service (if packages are scheduled as jobs). In this case, you need to configure an e-mail profile after logging on interactively using the SQL Server Agent account, to the computer on which SQL Server 2000 is installed.
You create profiles by double-clicking on the Mail icon in the Control Panel. Profile parameters are dependent on the type of mail server (e.g. Microsoft Exchange or Lotus Notes). Once a profile is created, it will appear in the Send Mail Task Properties dialog box in the Profile name list box. Within the same dialog box, you will be able to specify message recipients, message subject and content and optional attachments.
Note that the same functionality can be accomplished with the ActiveX Script task (which we will discuss in our next article) in combination with methods and properties of the Collaboration Data Object model. The main benefit of this approach is eliminating the requirement for a local MAPI profile.
Transfer Databases task - transfers a database from a SQL Server 7.0 or 2000 to another SQL 2000 Server. You can specify whether a database will be copied or moved. For destination, you can specify the location of data and log files (using settings on the File Locations tabs of the Transfer Database Properties dialog box). You cannot transfer any of the system databases or any others that already exist at the destination. This task is built into the Copy Database Wizard, which we have discussed in one of our previous articles.
Transfer Error Messages task - serves the specialized role of copying user-defined error messages between two SQL Servers (this includes the transfer from SQL Server 7.0 to 2000). Such messages are stored in the sysmessages table in the master database and can be added with the sp_addmessage stored procedure. You have an option of either copying all custom error messages or only selected ones. The task will also prevent copying of messages that already exist at the destination. This task is also part of the Copy Database Wizard.
Transfer Jobs task - copies jobs defined on a SQL Server 7.0 or 2000 (stored across several tables in the msdb database) to another SQL 2000 Server. This task is configured similarly to the Transfer Error Message task described above (you can either transfer selected jobs or all of them) and just like this task, it is included in the Copy Database Wizard.
Transfer Logins task - transfers logins between SQL Server 7.0 or 2000 and a SQL 2000 Server, in the same manner as the two previously discussed tasks. As before, you can copy all of them or select them based on databases to which logins have granted access. This means, you choose a database and all logins mapped to roles defined in these databases are automatically added. As with the few most recent predecessors on this list, you have seen this functionality in the Copy Database Wizard.
Transfer Master Stored Procedures task - transfers user-defined stored procedures residing in the master database of a SQL Server 7.0 or 2000 to another SQL Server 2000. Here also you can decide whether to transfer all or only selected items. As before, in case of naming conflicts, this task will prevent overwriting existing stored procedures at the destination.
Besides the ones listed above, SQL Server 2000 DTS offers more complex tasks, such as Execute Package, Dynamic Properties, ActiveX Script, Transform Data, Data Driven Query, and Message Queue. We will provide their coverage starting with our next article.