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