Data Conversion Services (DTS) will involve DTS in various fields of database management and development.
Data Warehouse - Extract data from the original processing system and table for use
Establish OLAP
Copy a large amount of data from a text file or other non-database format file to a database
Generate Microsoft Office Document Report
Using Distributed Transaction Coordinator (DTC) to implement multi-database operations
On a customer desktop or website, you often need to allow users to perform DTS packages on demand. In this case, when deploying the DTS package, you should decide where to place the DTS package, and call it by way.
your choice
To create a DTS package on-demand, there can be multiple options. These options are explained one by one below.
SQL Server Job
You can build a job in SQL Server and call sp_start_job stored procedures. The use of sp_start_job is that it is an asynchronous process. Since it cannot return success or failure instructions, you must force the SP_HELP_JOB system stored procedure to query the result of JOB. Asynchronous Job will make the desktop programs or web programs complicated unless the result is not concerned about the result. A job can be set to a non-administrator (SA) mode, but there are some additional steps.
Use DTS DLLS on the client desktop
The second method is that the user computer loads Enterprise Manager or DTS DLLS, calls the DTS package on the user's computer. Although user computers have a certain feasibility, it is not enough: you must take into account the distribution and installation of upgraded DTS packages.
Use sp_oa extended stored procedures on the server
The third choice is that the core content described in this article is to use the sp_oa system to store the process family and have a planned call DTS package. This solution can effectively avoid the drawbacks of the two programs.
Use VBScript call DTS package
A first step in implementing a stored procedure that can run the DTS package is to write a VBScript code. Because the sp_oa stored procedure is a bit troubles, you must write the code you want to implement with VBScript before using the sp_oa stored procedure. Generally tend to use Visual Basic to develop a simple script development. As shown in Figure A, by adding a DTS package object library in the Item Reference window, you can reference the DTS package object in the script.
Figure A: DTS object library
Use the LoadFromStorageFile function in your code. In general, development work should be carried out in a test environment. Understand the structured structure of the DTS format, it is helpful to change the test product to the actual product.
SP_OA implementation
Write the VBScript code to implement code with the sp_oa extended stored procedure. Similar to VBScript, the SP_OA system stored procedure allows interaction with the COM API of the object library.
SP_OACREATE and the CreateObject function called in VB or VBScript is similar. Sp_oAgetProperty, sp_oasetproperty, and sp_oamethod are used to connect features and functions in the object library. Unlike VB or VBScript, the COM error caused by the sp_oa stored procedure does not make the SQL statement fail, so you must manually check if each function using sp_oa is working properly.
At the same time, many sp_oa stored procedures will be referenced, so the OUTPUT statement must be added after the appropriate parameters during the sp_oa stored procedure. If the OUTPUT statement is omitted, T-SQL does not issue a warning message. So in the runtime state, although the stored procedure runs normally but does not return the correct value. List B is a detailed implementation code.
The solution includes a table that can rename the DTS package and the process of implementation. The sp_adrundtspackageonserver stored procedure receives an ID parameter. Before proceeding, the program finds the SQL Server path to the DTS package from the T_AddtsPackageSetup table. safety
Detailed security issues are not discussed in this article, here is the basic problem that must be considered:
During the SP_OA extension system store of the primary database, implement the sp_adrundtspackageonserver stored procedure requires Execute permission. To prevent some malicious users from implementing some purpose through the SP_OA process, you can modify the SQL Server rules for your application to enhance security.
T-SQL's Current_User function has a slight help for system security. Use the Current_User and the area in the T_AddTsPackageSetup table, you can query whether a user is set to: use a given DTS package.
When the DTS package is executed on SQL Server, it will be affected by the account settings of the SQL Server Agent service. If you read the ASCII file from the file system, you should determine that the SQL Server Agent's account settings are licensed by the file.
Extended example
You can use the SP_OA system stored procedure to interact with other COM libraries. At the same time, on other non-SQL Server systems that use ODBC and ActiveX Data Objects (ADOs), SP_OA can also pay the stored procedure. A VBScript script with only 10 lines is not a practical value, and the final synthetic T-SQL code will become very lengthy. The online SQL Server 2000 books includes a detailed Com object library support instructions and contains documents for the SP_OA system stored procedure. When the user needs to run the DTS package as needed, consider the use of the SP_OA system stored procedure to be implemented.
