How to create and execute data transformation services (DTS) in SQL Server 2000)

How to create and execute data transformation services (DTS) in SQL Server 2000 of Microsoft, you can use data transformation services (DTS )) the software package extends database events to application software. This article will show you how to create and execute data transformation services (DTS) in SQL Server 2000 ).
In most cases, any data changes exist in the application. In an application system, a program responds to an event through interaction. However, unless your program exists in the system, the events generated by the system are invisible to external programs.
Binding an external program to the system to consume these events often involves a lot of old-fashioned hard work and some luck. In today's offline systems, more and more programs need to respond to system events, while at the same time they need to be separated from the systems that generate events. Most systems are responsible for operating data and changing the data stored in the database. triggers are used to capture these changes and perform corresponding operations on the database. However, it would be nice to extend these events to the external world. in Microsoft SQL Server 2000, you can use the web and data transfer service (DTS) software Package to extend these database events to external programs.
In this article, I will use a stored procedure (Stored Procedure) to execute a DTS software package, which will execute ActiveX scripts in sequence to send requests to Web Services, these Web Services will be responsible for coordinating the processing of generated events.
In SQL Server 2000 Create DTS Software Package
Generally, when designing a system, you need to consider all possibilities of connecting your system to an external system. However, you can end this tug-of-war on how to handle events: do you want to query the status through the service, or do you want the external system to accept the events you generated? If your program is a commercial software responsible for operations and mobile data, it is best to leave the event creation work to the database that generates data changes. These changes can be implemented through table triggers, new events can be published by the event subscriber.
In SQL Server 2000, you can create a data transmission package that runs ActiveX scripts. This script is just a simple VBScript file. Of course, this VBScript file contains some specific knowledge about DTS execution, including the full-process variables for conditional operations. These conditional operations are based on the information stored by the variables, using the Createobject statement, the script can create an instance of the msxml2.serverxmlhttp component, which provides the ability to generate HTTP requests. Here, the DTS package can send a request to a web service or even the simplest web page to push an event. Then, the service or web program can process the event by executing the necessary operations ", this allows you to perform operations on the request.
Now you need a way to help execute the DTS package from the T-SQL, and in order to encapsulate this feature, stored procedures are an ideal choice in stored procedures, you may need the executable file dtsrun to run the DTS package. Using the xp_mongoshell system stored procedure, you can run dtsrun like executing a command line program, some command line parameters can help you successfully run dtsrun. EXE, I use the following parameters:

/S-Identify the server name of the SQL instance
/E-use trusted security for logged-on users currently
/N-name of the executed DTS package
/The execution password for the M-DTS (if set)
/A-name, type, and value of the full variable passed to the DTs Software Package
Your stored procedure should execute the data transmission software package by executing the xp_mongoshell system stored procedure. This command can use the command line string as a parameter to create such a string, in this case, the stored procedure calls the command line interpreter to accept the server name, DTS package name, DTS password, and full-process variables, full variables should be transmitted in the form of "variablename": "datatypeid" = "value". Therefore, a DTS call to the stored procedure should look like this:
Dtsrun/s (local)/E/n mypackage/M dtspassword/a "variable1": "8" = "some string"
The process of creating this command line string is displayed in list A. This will create a command line string effectively. The full-process variables contained in this string can be set by @ globalvariablelist. Then, you can use the xp_mongoshell system to execute dtsrun under the command line parameters. EXE. Finally, if there is an error, it will return an error code.
ListA
Create procedure dtsruntrusted
@ Servernamevarchar (30 ),
@ Dtspkgnamevarchar (30 ),
@ Dtspasswordvarchar (30 ),
@ Globalvariablelistvarchar (1000)
As
Declare @ err int
Declare @ cmdvarchar (1000)
Begin
Set @ err = 0
If (@ globalvariablelist is null)
Begin
Set @ cmd = 'dtsrun/s' + @ servername + '/e/N' + @ dtspkgname +'/m
'+ @ Dtspassword
End
Else
Begin
Set @ cmd = 'dtsrun/s' + @ servername + '/e/N' + @ dtspkgname +'/m
'+ @ Dtspassword + ''+ @ globalvariablelist +''
End
Execute @ err = Master .. xp_cmdshell @ cmd
Select @ err = coalesce (nullif (@ err, 0), @ error)
If @ err 0 return @ err
End
InSQL Server 2000ExecutedDTSSoftware Package
Now you can execute the DTS package, but you still need a DTS package for execution. The simplest way to create a DTS package is to use the DTS package designer in SQL Enterprise Manager, follow these steps:

Use the designer to create a new package
Add an integer full-course variable named eventid and set its value to 0.
Add an ActiveX script task and add the following code to the script:
Function main ()
Dim oxmlhttp as object
Set oxmlhttp = Createobject ("msxml2.serverxmlhttp. 3.0 ");
Oxmlhttp. Open "Post ",
"Http: // localhost/DOTNET/WebServices/eventservice. asmx/newevent", false
Oxmlhttp. setRequestHeader "Content-Type", "application/X-WWW-Form
-Urlencoded"
Oxmlhttp. Send "eventid =" & dtsglobalvariables ("eventid"). Value
Set oxmlhttp = nothing
Main = dtstaskexecresult_success
End Function
CreateWebService to receive generated events
You can add the subscriber of the event Generation Service to the database table, and the Stored Procedure named dtsruntrusted can collect the URL of the user subscribed to the event, the URL is passed to the DTS package as a full variable, and then an HTTP request is sent to the subscriber.
In my example, I only intend to include one event user, but it is more wise for multiple subscribers to set the serverxmlhttp object responsible for asynchronous operations to true, this will not slow down your Web Services, and events can also be generated in real time.
InASP. NETOfWebDatabase events used in services
It is very simple to use such database events. Event IDS can be associated with the event table. These events contain more detailed information about the system, the information stored in the event table should provide valid information and be abstract enough for external programs, so that external programs can use the information in the system.
List B contains an ASP. the framework code of the web service. The Web Service will be responsible for event processing. Note that the newevent method is defined as a webmethod, which can receive a parameter: eventid, in this method, you can add code to process generated events.
Now you can create data-centric applications and add process modules as required. You can create Event Notifications for any changes to data, and send these notifications to the programs that need information, and then these programs can handle data changes as needed.
ListB
Using system;
Using system. collections;
Using system. componentmodel;
Using system. Data;
Using system. diagnostics;
Using system. Web;
Using system. WebServices;
Using system. net;
Namespace WebServices
{
Public class eventservice: system. Web. Services. WebService
{
Public eventservice ()
{
Initializecomponent ();
}
Private icontainer components = NULL;
Private void initializecomponent ()
{
}
Protected override void dispose (bool disposing)
{
If (disposing & components! = NULL)
{
Components. Dispose ();
}
Base. Dispose (disposing );
}
[Webmethod]
Public void newevent (inteventid)
{
// Todo: handle event.
}
}
}