Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
DTS Tasks
The number of tasks available in DTS depends on the version of SQL Server you are using. The following table shows the tasks available in SQL Server 7.0 and 2000. The rest of the article gives you a brief overview of each task.
|
Task |
SQL Server 7.0 |
SQL Server 2000 |
|
ActiveX Script Task |
Yes |
Yes |
|
Bulk Insert Task |
Yes |
Yes |
|
Copy (Transfer) SQL Server Objects Task |
Yes |
Yes |
|
Data Driven Query Task |
Yes |
Yes |
|
Dynamic Properties Task |
|
Yes |
|
Execute Package Task |
|
Yes |
|
Execute Process Task |
Yes |
Yes |
|
Execute SQL Task |
Yes |
Yes |
|
File Transfer Protocol Task |
|
Yes |
|
Message Queue Task |
|
Yes |
|
Send Mail Task |
Yes |
Yes |
|
Transfer Database Task |
|
Yes |
|
Transfer Error Messages Task |
|
Yes |
|
Transfer Jobs Task |
|
Yes |
|
Transfer Logins Task |
|
Yes |
|
Transfer Master Stored Procedures |
|
Yes |
|
Transform Data (Data Pump) Task |
Yes |
Yes |
|
Analysis Services Processing Task |
|
Yes (only if Analysis Services are installed) |
|
Data Mining Prediction Task |
|
Yes (only if Analysis Services are installed) |
ActiveX Script Task
The ActiveX Script Task lets you customize data manipulations you wish to perform. DTS will automatically generate code for simple data transfers, such as moving data from one column to another. However, if you wish to combine several columns into one or split a column into several columns, you can write a custom script to do so. The most commonly used scripting languages (in DTS) are VB script and Jscript; however, you're not limited to these languages. You can use the scripting language of your choice. Keep in mind, though, that in order to use the scripting language, you must first install its library.
Bulk Insert Task
The Bulk Insert Task lets you import large volume of data from a text file into a SQL Server table or view. The Bulk Insert Task's functionality is very similar to that of BCP or the BULK INSERT command of Transact-SQL. The Bulk Insert Task (or BULK INSERT statement) is the fastest way of importing data into SQL Server; however, fast performance comes with the price: You cannot perform any transformations. In other words, the imported data will be in the exactly same format as it is in the text file. Also note that you can import data only from a text file, so if you need to import data from a spreadsheet, you have to copy this data into a text file prior to bringing it into SQL Server.
Copy SQL Server Objects Task
The Copy SQL Server Objects Task will look very similar to you if you ever used the object transfer utility in SQL Server versions 6.0 and 6.5. This task lets you transfer database objects from one database to another. Be careful when using this task: By default, it will drop the destination objects prior to re-creating them and populating them with data, which may or may not be what you want. If you want to create a fresh copy of each table, then the default option is fine. But if you just want to transfer data, be sure to select the "append data" option and deselect the "drop destination objects first" check box. By default, this task transfers all objects, including tables, views, stored procedures, user-defined functions, constraints, triggers, and more. Again, please examine all available options carefully prior to executing this task.
Data Driven Query Task
The Data Driven Query Task lets you execute a custom Transact-SQL program for each row defined by a particular query. This task can be very useful if you need to execute a stored procedure based on a certain condition. You could make an effective use of the Data Driven Query Task when implementing replication with transformable subscriptions. For each row that is inserted on the publishing server, you could respond with executing a certain INSERT, UPDATE, or DELETE statement (or a stored procedure) on the subscribing server. Keep in mind, though, that Transact-SQL is a set-based language that doesn't perform as efficiently when processing one row at a time. Therefore, much like Transact-SQL cursors, the Data Driven Query Task is not likely to be very efficient.
Dynamic Properties Task
The Dynamic Properties Task lets you modify DTS package properties "on-the-fly." You don't always know the parameters for your package ahead of time. In such cases, you might need to run a query to get the parameter values while the package is executing. This is when you use the Dynamic Properties Task. For example, you might need to execute a particular stored procedure for all rows in table A that are older than the oldest record in table B. The Dynamic Properties Task could run a query to get the modification date for the oldest record in table B. This parameter would be passed to the Execute SQL Task that will run the stored procedure.
Execute Package Task
The Execute Package Task lets you orchestrate the execution of other DTS packages. If you want to execute several DTS packages together, depending on the outcome of the other packages, you could build a package that executes the participating packages in sequence. You could execute different set of packages based on the outcome of previous packages' execution (success or failure).
Execute Process Task
The Execute Process Task lets you run a program or a batch file as part of your DTS package. For example, you could execute a custom application written in VBA that generates some type of report in Excel and then mails it to executive employees through Outlook. You could also use this task to schedule the execution of a Windows NT task (through a custom Win32 executable program).
Execute SQL Task
The Execute SQL Task does exactly what it says. It lets you execute any Transact-SQL statement or stored procedure as part of your DTS package. This is a great way to automate execution of several stored procedures, based on the success or failure of executing others.
File Transfer Protocol (FTP) Task
The File Transfer Protocol (FTP) Task lets you download files from an Internet site or a directory. As in any FTP process, you have to have the appropriate permissions to download files. The FTP Task could be useful if you can access the files only through FTP (for instance, files on a VAX system might only be accessible through FTP).
Message Queue Task
The Message Queue Task lets you send and receive messages between two DTS packages in order to coordinate their execution. You must have MSMQ client installed on the computer containing the DTS package to successfully execute such a task. The Message Queue Task can come in handy if you have a need to check for the availability of data prior to importing or manipulating it from a remote server.
Send Mail Task
The Send Mail Task is a great way to inform the database administrator about package execution status. This task can also be used to send a file (as an attachment) after it is generated by the DTS package. The Send Mail Task is not related to SQL Mail functionality. However, you still have to have a MAPI client installed on the SQL Server machine, and you also need to have a valid user profile. The Send Mail Task will use this user profile to send the mail messages.
Transfer Database Task, Transfer Error Messages Task, Transfer Logins Task, Transfer Jobs Task, and Transfer Master Stored Procedures Task
The Transfer Database Task, Transfer Error Messages Task, Transfer Logins Task, Transfer Jobs Task, and Transfer Master Stored Procedures Tasks are all very similar in nature. They transfer various server-wide pieces of information from one SQL Server to another. You can transfer server-wide objects from SQL Server 7.0 to 2000 or between two instances of SQL Server 2000.
Transform Data Task
The Transform Data Task is the DTS task you will use most often. It lets you transfer and transform data from one source into another. This is also most powerful task by far, allowing you to import and export data from many different platforms. This task also has numerous options that let you customize it according to your needs.
Analysis Services Processing Task and Data Mining Prediction Task
The Analysis Services Processing Task and Data Mining Prediction Task are available only if you have Analysis Services installed. The former lets you process Analysis Services dimensions or cubes. The latter can be used to run prediction queries based on the previously defined mining model.