Standalone DTS

DTS Asserts its Independence

A simple definition of interoperability is having the ability to work well with others. When we think of interoperability in conjunction with SQL Server tools, we can't help but consider how well Data Transformation Services (DTS) fits this definition. To discover how DTS interoperates, let's first consider its composition and the technologies on which it operates. This month, we look at the technology that gives DTS its independence, examine some of its dependencies, and show how to deploy DTS in a standalone environment—one that isn't running SQL Server.

Debunking the Myth

Before we go further, let's first establish that DTS doesn't require SQL Server for developing or executing packages. It's true that to design packages graphically, you need to first connect to a SQL Server database. However, this connection is necessary only because Microsoft chose to embed the Package Designer in an Enterprise Manager server connection, and not because of a true dependency on SQL Server. We hope that Microsoft will reconsider this location and separate the Package Designer from Enterprise Manager in a future release.

In "The DTS Development Guide" (July 1999), we looked at how DTS packages run locally on the machine that initiates them, unlike stored procedures, which run within SQL Server at execution time. At runtime, all packages execute through the dtsrun.exe or the dtswiz.exe application. Note that dtswiz.exe controls only those packages you create with the Import and Export Wizard; dtsrun.exe controls all other packages. For this discussion's purposes, however, both behave in the same manner. For execution to happen, packages that aren't already stored in a COM-structured storage file format are extracted from either SQL Server or the SQL Server Repository, converted to a COM-structured storage file, then executed. Hidden from the user, the DTS executables dtsrun.exe and dtswiz.exe handle the extraction.

Independence Day

So how does DTS gain its independence? Through OLE DB and COM. Acting as an OLE DB consumer, DTS can interact with any data store that provides a compatible OLE DB or ODBC interface. OLE DB, Microsoft's solution for universal data access, defines a set of COM interfaces for accessing and offering any type of data. Applications using these interfaces are referred to as data consumers (for accessing) and data providers (for offering). Microsoft's vision is that over time, OLE DB will replace ODBC as the industry standard for data access. (For more details, see Dino Esposito, "OLE DB or ODBC?" November 1999.) Microsoft bundles and freely distributes the components for OLE DB, along with several OLE DB provider interfaces and ODBC drivers, through Microsoft Data Access Components (MDAC). You can download MDAC from Microsoft's Universal Data Access (UDA) site at http://www.microsoft.com/data.

The other technology that DTS leverages is COM, the foundation on which DTS builds all its interfaces. DTS exposes a complete COM object hierarchy that developers use to create, maintain, and execute packages inside applications. Using COM-compliant programming languages, developers can create custom applications and still leverage all the functionality of DTS. For details on using the DTS object model to create packages, see "Unleash the Power of DTS" and "Pump Up the Power of DTS" in the May and June 1999 issues.

Runtime Licensing

As we mentioned, using the Package Designer for graphical package development requires a connection to SQL Server. But unless you save your packages in SQL Server (either in the MSDB database or in the Repository), no other SQL Server dependencies exist. As long as your DTS packages aren't connecting to SQL Server, you don't need additional SQL Server Client Access Licenses (CALs) to develop and subsequently deploy your DTS applications. The DTS runtimes are free. Therefore, in a standalone environment, where SQL Server or the client tools aren't present, you need only the appropriate licensing for your database. For example, if you use DTS to build a replication utility that moves data between an Oracle and an Informix server, you need only Oracle and Informix client licenses.

Data Providers

For DTS developers accessing other data stores, probably the biggest hurdle is obtaining a compatible OLE DB or ODBC driver. Microsoft includes an OLE DB provider for Oracle with MDAC, but to access any other major database (Sybase, Informix, DB2, Ingres, etc.), you need to obtain drivers from a third party. The good news is that you can choose from numerous third-party vendors. The bad news is that not all drivers have the same level of support. Table 1, page 44, lists the OLE DB interfaces, properties, and schemas that DTS uses. For drivers to work with DTS, they must at a minimum correctly support the required elements.

Beyond the problem of interface support, the problems you'll most often encounter involve the use of non-thread-safe drivers and drivers that don't support the OLE DB Service Components. If a driver is thread-safe, DTS attempts to increase performance through parallelism—creating multiple threads over a single connection. (Threads enable multiple tasks to execute within a single process. For example, within an application, one thread might write data to disk while another thread handles network traffic. Multi-threading also lets applications take advantage of multiple CPUs.) Before attempting parallelism, DTS queries each driver to determine its thread-safe status. Problems arise when the driver reports that it is thread-safe when in fact it isn't. Two known offenders in this area are the Microsoft Access Jolt driver and Sybase's SQL Anywhere driver. Fortunately, DTS packages work fine on only one thread. To force DTS to use one thread, set the ExecuteOnMainThread property of a task's step definition to TRUE.

OLE DB Service Components provide advanced functionality features, such as session pooling and support for the IRowsetChange interface. By default, DTS always tries to take advantage of these features. However, some drivers, including older driver versions from Merant (formerly Intersolv), don't properly support all the components and fail when DTS accesses them. DTS overcomes this limitation by providing a package-level property, UseOLEDBServiceComponents, which controls DTS' use of the service components. To disable service component use and let these older drivers work with DTS, you can override the UseOLEDBServiceComponents property by setting it to FALSE.

Fortunately, most vendors offer a trial period with their drivers. We strongly recommend that, before making your purchase decision, you take the driver and your application for a test drive.

Sorting Through the Files

With all the functionality DTS provides, you might be surprised to learn that you can establish a standalone DTS runtime environment with only six DLL files, six RLL (resource) files, and one EXE file. These files, which Table 2 lists, make up the DTS runtime environment. All these files are in the x86binn directory on the SQL Server 7.0 CD.

Developers will quickly recognize several of these files. The first is dtspkg.dll, the Microsoft DTSPackage Object Library (DTS). Dtspkg.dll exposes an OLE Automation interface with which developers can create and modify DTS packages, using any language that supports OLE Automation. The second familiar file is dtspump.dll, the Microsoft DTSDataPump Scripting Object Library (DTSPump). Dtspump.dll defines the DTS ActiveX scripting constants. Another file you've already encountered is dtsrun.exe, a command-line utility that executes DTS packages. New to the group but no less important are axscphst.dll and dtsffile.dll. Axscphst.dll provides the data pump interface through which ActiveX scripting transformations execute. And dtsffile.dll gives DTS an OLE DB text file provider. The remaining files serve supporting roles.

Deploying a Package

Let's look at what it takes to deploy a standalone server. For this example, we created a package, AuthorNames.dts, and saved it to a file. (Screen 1 shows a graphical representation.) This package reads in a text file, Authors.dat, and extracts the authors' last names to generate a new text file, LastNames.dat. All files for this example are in the Standalone-DTSExamples.zip file, which subscribers can download from http://www.sqlmag.com. Because this will be a standalone server, we assume that you haven't installed the SQL Server 7.0 client tools on your machine.

As we mentioned, OLE DB is one of DTS's essential technologies. Therefore, you must install it before you can use DTS. If you haven't done so already, obtain the MDAC files (version 2.1 or higher) and install them on your box. Then you're ready to begin.

To use DTS, you must install its components. To assist you, we created the command file script in Listing 1, DTSReg.cmd, which registers all the required files. After unzipping the StandaloneDTSExamples.zip file into a new directory, execute the DTSReg.cmd file. For each of the four DLLs registered, you receive a message box confirming its successful registration.

Now you're ready to use the new installation. To run the AuthorNames.dts package, use the dtsrun.exe utility. We created the command file script in Listing 2, RunAuthorNames.cmd, to execute the package. After executing the script as in Screen 2, the package creates a new file, LastNames.dat, containing all the authors' last names.

Interoperability is essential for most applications, and DTS is a tool that certainly fits that bill. The potential to leverage DTS's power and flexibility without the overhead of installing SQL Server or other client tools makes DTS an ideal utility to embed in your custom applications. We've used it in several standalone implementations. Give it a try! We're always interested in how you're deploying DTS, so drop us some email and tell us what you're doing.

Corrections to this Article: