Executing a DTS Package From .NET

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:

1. Project references dialog (right click references, add reference)
2. Find Microsoft DTSPackage Object Library in the exhaustive list of objects on the COM tab
3. 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:

            [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.