Data Transformation Services (DTS), new in SQL Server 7.0, has probably received more enhancements in SQL Server 2000 than any other SQL Server utility, with the possible exception of Query Analyzer. Here are seven of the most valuable new built-in DTS tasks in SQL Server 2000.
7. Dynamic Properties Task
The Dynamic Properties task lets you more easily use DTS packages for multiple purposes by dynamically substituting values into a DTS package at runtime. For example, you can use this task to substitute the name of the source and target databases at runtime, so you can use the same package for many databases. The values you substitute can come from an external source, such as an .ini file, an environment variable, or even a query's results.
6. Transfer Error Messages Task
You can use the Transfer Error Messages task to transfer user-specified messages created with the sp_addmessage stored procedure from a SQL Server 2000 or 7.0 system to a named instance of SQL Server 2000. With this task, you don't have to manually create user-specified messages on all your distributed SQL Server systems.
5. Transfer Master Stored Procedures Task
The Transfer Master Stored Procedures task copies system stored procedures from a SQL Server 2000 or 7.0 system to a named instance of SQL Server 2000. This means you can move your custom system stored procedures to other servers.
4. Transfer Jobs Task
Centrally creating and then distributing jobs and workflow among distributed SQL Server systems has been challenging. But now, you can use the Transfer Jobs task to copy jobs defined on a SQL Server 2000 or 7.0 system to a named instance of SQL Server 2000.
3. Transfer Databases Task
As its name suggests, the Transfer Databases task lets you copy or move an entire database—including tables, logins, stored procedures, and all other database objects—between two SQL Server systems. This task also creates new data and log files on the target system.
2. Execute Package Task
The Execute Package task lets you execute existing DTS packages from the current DTS package. With this task, you can organize, reuse, and better structure your DTS packages. You can also set any global variables that the called package uses.
1. File Transfer Protocol Task
With pre-SQL Server 2000 releases, you have to create a custom task to perform FTP file transfers from within a DTS package. The new File Transfer Protocol task lets you transfer one or more files from a remote FTP server to the local system.