A discussion between some peers of mine came up the other day about executing a SQL Server DTS package from an ASP.NET web page. The actual best practice is to invoke a stored procedure that in turn calls the DTS package you wish to run, however thanks to the simplicity of creating a RCW (runtime callable wrapper) via the VS.NET IDE on the legacy unmanaged Microsoft DSTPackage Object Library (located in your SQL Server installation directory in Tools\Binn\dtspkg.dll, also listed on the COM tab in the Add Reference dialog in the IDE), you can invoke a DTS package directly from code if you so wish. Just as a refresher, adding an RCW from the IDE is accomplished by:
- Project references dialog (right click references, add reference)
- Find Microsoft DTSPackage Object Library in the exhaustive list of objects on the COM tab
- Select it, add it...voila. The IDE automagically generates the correct interop assembly and places it in the bin folder, ready for instantiation
The actual code itself is straightforward, here is an example:
using System;
using System.Runtime.InteropServices;
using DTS;
namespace DTSPackage
{
///
/// Summary description for Class1.
///
class Class1
{
///
/// The main entry point for the application.
///
[STAThread]
static void Main(string[] args)
{
Package2Class package = new Package2Class();
object pVarPersistStgOfHost = null;
try
{
package.LoadFromSQLServer("SERVER_NAME", // server name
null, // server username
null, // server password
DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,
null, // if the package has a password, otherwise null
null, // probably null
null, // probably null
"PACKAGE_NAME", // name of the DTS package
ref pVarPersistStgOfHost);
package.Execute(); // execute the package
}
catch
{
throw;
}
finally
{
package.UnInitialize(); // unwrap the package
System.Runtime.InteropServices.Marshal.ReleaseComObject(package); // tell interop to release the reference
}
}
}
}
I still recommend calling DTS packages via stored procedures (or via a scheduled SQL Server job), but in a pinch, or for prototyping, testing, etc, the above code should work nicely. I am assuming that with the release of Yukon, there will be a managed class for this, but you know what they say about assuming.
Share this post: 
|
|
|
|
|
