chartflow.blogg.se

1. #Vb6 missing microsoft dts package object library how to#
2. #Vb6 missing microsoft dts package object library full#
3. #Vb6 missing microsoft dts package object library software#
4. #Vb6 missing microsoft dts package object library code#

#Vb6 missing microsoft dts package object library full#

Before continuing execution, the procedure retrieves the path on the SQL Server to the DTS package from the T_AdDTSPackageSetup table.Ī full security discussion is beyond the scope of the article, but some considerations should be noted: The sp_AdRunDTSPackageOnServer stored procedure accepts an ID value as a parameter. 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. See the sample in Listing B for the detailed implementation. During runtime, the stored procedure will execute properly but fail to return a valid value. Transact SQL will not warn you if the OUTPUT statement is omitted. Where noted, you must include the OUTPUT statement next to the appropriate parameter of the sp_OA stored procedure. 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.Īlso, many of the sp_OA stored procedures use reference parameters. Sp_OAGetProperty, sp_OASetProperty, and sp_OAMethod let you consume properties and functions in an object library. Sp_OACreate is similar to the CreateObject function you invoke in VB or VBScript. Like VBScript, the sp_OA system-stored procedures allow you to interact with a COM+ API of an object library.

#Vb6 missing microsoft dts package object library code#

Once you've written the VBScript code, you're ready to implement the code using the sp_OA extended stored procedures. The structured file format of DTS is most useful when you move from testing to production. 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.

#Vb6 missing microsoft dts package object library how to#

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:

#Vb6 missing microsoft dts package object library software#

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