Data Management

Execute a DTS package from a SQL Server stored procedure

Implement a DTS package from a SQL Server stored procedure.

By Jeffrey T. Juday

Data Transformation Services (DTS) is a SQL Server Software Developer’s trusted friend. The scenarios for using DTS touch almost all aspects of database administration and development, including:
  • Data warehousing—copying data from a transaction processing system and populating roll-up tables for reporting
  • Building OLAP cubes
  • Bulk-copying data from flat files and other nondatabases into your database
  • Generating Microsoft Office document reports
  • Executing multidatabase operations using the Distributed Transaction Coordinator (DTC)

Activation is often via a custom desktop program or a Web site to allow a user to execute a DTS package on demand. Deciding where to put the DTS package and how to invoke it is one of the more vexing decisions you must make when you deploy the DTS package.

Your options
To set up a DTS package to execute on demand, you have a number of options to consider. Let's take a look at each.

SQL Server job
You can create a job on the SQL Server and invoke the sp_start_job stored procedure. The downside to using sp_start_job is that it is an asynchronous process. With no indication of success or failure, you’re forced to poll the results of the job using the sp_help_job system-stored procedure. Unless you want to invoke the job without concern for its success or failure, an asynchronous job can complicate the development of your desktop or Web application. A job can be configured to execute without administrator (sa) access, but configuring it does require some added steps.

DTS DLLs on the client desktop
A second alternative is to load Enterprise Manager or the DTS DLLs on the user’s machine and invoke the DTS package from the user’s machine. While running the DTS package from the user’s machine is a viable alternative, the downside is the installation and dissemination of updates to the DTS package.

DTS on the server using sp_OA extended stored procedures
Another alternative, and the focus of this article, is to utilize the sp_OA family of system-stored procedures and invoke the DTS package programmatically. By using the sp_OA system-stored procedures, you won't be encumbered by the limitations of sp_start_job or the installation requirements of running the DTS package from the user’s desktop.

Invoking a DTS package using VBScript
Building a stored procedure to run a DTS package begins with writing the VBScript code we want to implement. Because using the sp_OA stored procedures can be a bit clumsy, the best approach is to write the code you want to implement in VBScript before implementing the code using sp_OA stored procedures. I like to further simplify script development by writing the code in Visual Basic. You can reference the DTS Package Object Library by adding the library shown in Figure A to your project references. The VBScript code for executing a DTS package is shown in Listing A.

Figure A
DTS Object Library

As you can see, I used the LoadFromStorageFile function. Typically, development is done in a test environment. The structured file format of DTS is most useful when you move from testing to production.

Sp_OA implementation
Once you've written the VBScript code, you're ready to implement the code using the sp_OA extended stored procedures. Like VBScript, the sp_OA system-stored procedures allow you to interact with a COM+ API of an object library.

Sp_OACreate is similar to the CreateObject function you invoke in VB or VBScript. Sp_OAGetProperty, sp_OASetProperty, and sp_OAMethod let you consume properties and functions in an object library. Unlike VB or VBScript, a sp_OA stored procedure causing a COM+ error does not result in the failure of the SQL statement, so each use of the sp_OA function should be checked for success.

Also, many of the sp_OA stored procedures use reference parameters. Where noted, you must include the OUTPUT statement next to the appropriate parameter of the sp_OA stored procedure. Transact SQL will not warn you if the OUTPUT statement is omitted. During runtime, the stored procedure will execute properly but fail to return a valid value. See the sample in Listing B for the detailed implementation.

Included in the solution is a table to allow you to do things such as rename the DTS package and streamline the implementation a bit. The sp_AdRunDTSPackageOnServer stored procedure accepts an ID value as a parameter. Before continuing execution, the procedure retrieves the path on the SQL Server to the DTS package from the T_AdDTSPackageSetup table.

A full security discussion is beyond the scope of the article, but some considerations should be noted:
  • Implementing the sp_AdRunDTSPackageOnServer stored procedure requires EXECUTE permissions on the sp_OA extended system-stored procedures in the master database. To keep a malicious SQL Server savvy user from utilizing the sp_OA procedures for something beyond the intended purpose, you can configure a SQL Server role for your application for tighter security.
  • I’ve found the CURRENT_USER Transact SQL function helpful for safety or lightweight security. Using CURRENT_USER and the fields in the T_AdDTSPackageSetup table, you can query to determine if the user is configured to run the given DTS package.
  • The DTS package will execute on the SQL Server in the security context of the account set up for the SQL Server Agent service. So, for instance, if your DTS package reads an ASCII-delimited file from the file system, be sure the account set up for SQL Server Agent has permissions to the file.

Extending the example
You can use the sp_OA system-stored procedures to interact with other COM+ libraries. I’ve also found the procedures useful for invoking stored procedures on non-SQL Server systems using ODBC and ActiveX Data Objects (ADO). As you may have observed, it’s impractical to implement anything that equates to 10 or so lines of VBScript. The resulting Transact SQL code becomes busy and cumbersome to work with. SQL Server 2000 Books online includes details on which COM+ Object libraries are supported, along with documentation of the sp_OA system-stored procedures. The next time a user must run a DTS package on demand, consider using the sp_OA system-stored procedures.

Editor's Picks