Data Transformation Services Utilities

Data Transformation Services (DTS) allows you to import, export, and transform data between Microsoft SQL Server and any OLE DB, ODBC, or text-file format. DTS saves the information and settings associated with these data movements in an object called a package. These packages can be accessed or created via the SQL Server Enterprise Manager or via the command-line utilities that ship with SQL Server 2000.

Two DTS command-line utilities are included with SQL Server: dtsrun and dtswiz. The dtsrun utility allows you to execute a DTS package without having to open the package. This is particularly useful when scheduling a DTS package for execution via the SQL Server Agent. dtsrun comes with a variety of command-line switches that allow you to customize the execution of the package. The dtsrun utility uses the following syntax to execute a package that has been saved in the SQL Server msdb database:

 dtsrun /Sserver_name /Uuser_name /Ppassword /Npackage_name 

TIP

The command-line text used by dtsrun can be generated using the DTS run utility. This utility is accessed by executing dtsrunui from the command prompt or the Windows run command. Upon execution, the utility presents a dialog box that allows you to enter the relative DTS information, including the DTS package name, server name , and so on. After that information is entered, you can utilize the Advanced button and generate the dtsrun command-line text that can be copied and executed via the dtsrun utility.

The dtswiz utility allows you to start the Data Transformation Services Import/Export Wizard using the options specified on the command line. You can use the Data Trans-formation Services Wizard to create DTS packages that can be run immediately or saved for future execution. The dtswiz utility uses the following syntax to start the wizard using Windows authentication against the database and server specified:

 dtswiz /sserver_name /n /ddatabase_name 

The DTS command-line utilities and the Data Transformation Services Wizard are discussed in more detail in Chapter 20.

The DTS utilities are located, by default, with the SQL Server tools in Microsoft SQL Server\80\Tools\Binn\ .