In most instances, any changes to data exist within the
application context. Applications respond to events through interactions within
the application system. However, unless your application exists within a
system, events generated within a system are usually invisible to an external
application.

Binding an external application to a system to consume these
events usually involves a lot of good old-fashioned elbow grease and some luck.
In today’s disconnected systems, more and more applications need to react to
events of a system but remain external to the system generating the events.
Since most systems are responsible for manipulating data, changes to the data
exist within the database. Triggers usually capture these changes and react by
doing some action on the database; however, it would be nice to extend these
events to the outside world. Within Microsoft SQL Server
2000, you can make use of the Web and Data Transformation Services (DTS)
packages to extend these database events to external applications.

In this article, I’ll use a stored procedure to execute a
DTS package that will, in turn, run ActiveX script to send a request to a Web
service that will coordinate the handling of the generated event.

Weekly development tips in your inbox

Keep your developer skills sharp by signing up for TechRepublic’s free Web Development Zone newsletter, delivered each Tuesday.

Automatically sign up today!

Creating DTS packages on SQL Server 2000

When designing a system, you normally would want to consider
all possibilities for connecting external systems to your own. However, you
could end up in the push/pull battle of how to handle events: Do you poll the
service to query the state, or do you generate an event that you hope the
external system will be able to absorb? If your application is a business
application that’s responsible for manipulating and moving data, it would be
nice to leave event creation to the database where data changes occur. These
changes can be realized through table triggers, and new events can then be
published to subscribers of events.

In SQL Server 2000, you can create
a DTS package that will execute an ActiveX script. The ActiveX script is simply
a VBScript file with some special knowledge of the DTS being executed. This
special knowledge includes global variables that can be used to do conditional
operations based on the information stored to those variables. Using the CreateObject statement, the script can create an instance
of the ubiquitous MSXML2.ServerXMLHTTP component, which provides the ability to
generate HTTP requests. In this light, the DTS package can make requests to Web
services or even simple Web pages to facilitate an event. The service or Web
application can then handle the request by performing necessary actions to
handle this “event”.

Now you need a method to facilitate executing the DTS
package from within T-SQL. To encapsulate this functionality, a stored
procedure would be an ideal candidate. Within the stored procedure, you can use
the dtsrun
executable to run a DTS package. Using the xp_cmdshell system stored
procedure, you can run the dtsrun executable much like
executing the program from the command line. There are a few command-line
switches that you will need to execute the dtsrun EXE successfully. I’ll use
the following switches:

  • /S–identifies
    the server name of SQL instance.
  • /E–enables
    trusted security using the current context of the logged in user.
  • /N–the
    name of the DTS package to execute.
  • /M–the
    DTS execution password (if set).
  • /A–the
    list of global variable names, types, and values passed to the DTS
    package.

Your stored procedure should execute the DTS package by
executing the xp_cmdshell
system stored procedure taking the command line string as a parameter. To build
this string, your stored procedure should be able to accept the variable
information as parameters. Staying true to form, the stored procedure to call
the shell command accepts the server name, the name of the DTS package, the DTS
password, and the global variables. The global variables should be passed as “VariableName”:”datatypeid”=”value”.
So an example of a DTS call from the stored procedure should look like this:

dtsrun /S (local) /E /N MyPackage /M dtspassword /A "variable1":"8"="some 

string"

The procedure to build this command line string is in Listing A. This effectively creates the command-line string, including
the global variables depending on whether the @GlobalVariableList value is set. It then executes the dtsrun EXE with
the command-line parameters through the xp_cmdshell system stored procedure. Finally, it returns an
error code, if any.

Executing a DTS package on SQL Server 2000

Now that you can execute a DTS package, you’ll need a DTS
package to execute. The easiest way to build your DTS package is within the DTS
package designer in SQL Enterprise Manager. To do so, follow these steps:

  1. Create
    a new package using the designer.
  2. Add
    a global variable called eventId as an Integer and set its value to 0.
  3. 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

Building the Web service to absorb the generated event

You can add subscribers to the event generation service to a
table in your database. Then the DtsRunTrusted stored
procedure can collect the URLs of the subscribed event consumers, pass the URL
to the DTS package as a global variable, and send an HTTP request to that
subscriber.

In my example, I only intend to have one event consumer. But
for multiple subscribers, it might be wise to set the asynchronous operation of
the ServerXMLHTTP object to True. This way you’re not
held up by slow Web services, and events can be generated in a timely fashion.

Consuming database events using an ASP.NET Web service

It’s simple to consume this database event. The event ID can
relate back to a table of events that has more specific information regarding
the system. Also keep in mind that, the information stored in the event table
should be informative and abstract enough that any external application will be
able to use the information in the system.

Listing B
contains the skeleton code of an ASP.NET Web service that will handle the event. It’s important to note that the NewEvent
method is defined as a WebMethod, and it accepts one
parameter: the eventId. Inside this method you should
add the code to handle the generated event.

Now you can create data-centric applications and add process
modules on at will. Any changes you make to the data will create event
notifications to curious applications. Then those applications can handle those
data changes as needed.

Miss a column?

Check out the Web Development Zone archive, and catch up on the most recent editions of Phillip Perkins’ column.