Whilst the previous method of executing the package directly from ASP
may be the simplest it is not always the best. There are limitations
when using integrated security across machines from IIS, and not all
methods in the object model are supported from script as opposed to
compiled code. The most notable example is the GetExecutionErrorInfo
method which cannot be used as it passes arguments by reference, of
types other than variant. Scripting only supports the variant type.
To overcome this you may wish to encapsulate the code in a COM
component,
which can be run in a Microsoft Transaction Server package (NT), or a
Component Services application (W2K). These have the ability to set a
package/application identity. In this way a privileged account can be
used to execute the DTS package without granting additional to
individual or
anonymous users.
The download to accompany this
article includes a simple COM object
SQLDTS_ExecDTS.ExecutePackage, and a calling ASP page (ExecCOM.asp).
For information about installing components see
HOWTO: Create an Empty MTS Package to Add Components for ASP (223406).
The problem with the methods covered so far is that they are all
synchronous. So for anything but a very quick package it is likely that
your ASP page will time out. The package will also execute on the same
server
as IIS, which is not always the machine as SQL Server. This can be
very inefficient
on network traffic depending on the processes involved. An alternative
is to introduce
some abstraction, such as Microsoft Message Queue.
Another is to have SQL Server Agent execute the DTS package, the ASP
page just
initiates the execution of the job. A sample methodology for this is
covered in Professional SQL Server 2000 DTS