vb6 missing microsoft dts package object library

Typically, development is done in a test environment. The VBScript code for executing a DTS package is shown in Listing A.Īs you can see, I used the LoadFromStorageFile function. You can reference the DTS Package Object Library by adding the library shown in Figure A to your project references. I like to further simplify script development by writing the code in Visual Basic. 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. 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.īuilding a stored procedure to run a DTS package begins with writing the VBScript code we want to implement. 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.ĭTS on the server using sp_OA extended stored proceduresĪnother alternative, and the focus of this article, is to utilize the sp_OA family of system-stored procedures and invoke the DTS package programmatically. A job can be configured to execute without administrator (sa) access, but configuring it does require some added steps.Ī 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. 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. 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.

The downside to using sp_start_job is that it is an asynchronous process. You can create a job on the SQL Server and invoke the sp_start_job stored procedure. To set up a DTS package to execute on demand, you have a number of options to consider. 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.

Executing multidatabase operations using the Distributed Transaction Coordinator (DTC)Īctivation is often via a custom desktop program or a Web site to allow a user to execute a DTS package on demand.

Generating Microsoft Office document reports.

Bulk-copying data from flat files and other nondatabases into your database.

Data warehousing-copying data from a transaction processing system and populating roll-up tables for reporting.

The scenarios for using DTS touch almost all aspects of database administration and development, including:

Data Transformation Services (DTS) is a SQL Server Software Developer’s trusted friend.

Vb6 missing microsoft dts package object library