Data Conversion Services (DTS) There are various areas of database management and development involving DTS: Data Warehouse - extracting data from the original processing system and table to report to use the establishment OLAP to bring a lot of data from text files or others. Non-database formats are copied to the database to generate the Microsoft Office document report Use Distributed Transaction Coordinator (DTC) to implement multi-database operations on the customer's desktop or website, 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, you can have a variety of options. These options are explained one by one below. SQL Server Job You can create 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. The second method of using the DTS DLLS in the client desktop 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 the SP_OA extended stored procedure on the server, which is 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. Calling the DTS package using the VBScript to implement a first step in the 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: The DTS object library uses 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. The sp_oa implements 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. Detailed security issues are not discussed in this article. It is to say that some basic issues must be considered: Implementing the sp_adrundtspackageonserver stored procedure in the SP_OA extension system stored procedure. 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 examples can interact with other COM libraries using the SP_OA system stored procedure. 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. Author Blog:
http://blog.9cbs.net/longrujun/
