asp.net and dts

[rac55]

Hi

Does anyone know how to invoke a dts from asp.net web page? I have searched the internet but cannot find any examples.

Any help would be greatly appreciated

Thanks

 

[link9]

You have to use COM Interop to do so. You will need a project reference to the Interop.DTS assembly, which you can find under the COM tab when adding references.

Below is a link to the class that we use to execute DTS packages. It's just a little lengthy, which is why I didn't post the code here, but it's basically just a few static methods, which you can call:

DTS.ExecutePackage(serverName, packageName);

The above call assumes windows authentication, but it has overloads if you need to pass username/password along with it.

http://www.cmisolution.com/paul/DTS.zip

(=
paul

The answer to getting answered -- faq855-2992

 

[rac55]

Thank you so much for replying...the url above cannot be diaplayed due to an internal error.

Thanks
Rachel

 

[link9]

Hmmm... well, here's the class:

using System;
using System.Runtime.InteropServices;
using DTS;

namespace Link9.Bin.Data
{

   //============================================================
   //D T S (class)
   //============================================================
	/// <summary>
	/// Provides methods for executing DTS Packages.
	/// </summary>
	public class DTS
	{

		public DTS(){}



      //============================================================
      //E x e c u t e P a c k a g e 
      //============================================================
      /// <summary>
      /// Execute a DTS package using Windows authentication.
      /// </summary>
      /// <param name="serverName"></param>
      /// <param name="pkgName"></param>
		public static void ExecutePackage(string serverName, string pkgName)
		{
         ExecutePackage( serverName, pkgName, null, null );
		}//ExecutePackage



      //============================================================
      //E x e c u t e P a c k a g e 
      //============================================================
      /// <summary>
      /// Execute a DTS package.  Provide a username and password to
      /// use SQL Server Authentication.  Set username and password
      /// to null to use Windows Authentication.
      /// </summary>
      /// <param name="serverName">server name</param>
      /// <param name="pkgName">DTS package name</param>
      /// <param name="UID">username - for SQL Server authentication</param>
      /// <param name="PWD">password - for SQL Server authentication</param>
		public static void ExecutePackage(string serverName, string pkgName, string UID, string PWD)
		{

         Package2Class package = new Package2Class();

         //The sink handles the package's events: OnError, OnStart, OnCancel, etc.
         Link9.Bin.Data.PackageEventsSink PES = new PackageEventsSink();

			try
			{


            object pVarPersistStgOfHost = null;


            //Link the Package Event Sink to the DTS Package.
            UCOMIConnectionPointContainer CnnctPtCont = (UCOMIConnectionPointContainer)package;
            UCOMIConnectionPoint CnnctPt;
            Guid guid = new Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5");
            CnnctPtCont.FindConnectionPoint(ref guid, out CnnctPt );
            int iCookie;
            CnnctPt.Advise( PES, out iCookie );

            //Load the DTS Package
				package.LoadFromSQLServer
            (
					serverName,
               UID,
               PWD,
               //If a userID is provided, use SQL Server authentication. Else use windows authentication
					(UID == null) ? DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection : DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,
					null,
               null,
               null,
					pkgName,
					ref pVarPersistStgOfHost
            );

				package.Execute();

			}//try

			catch(System.Runtime.InteropServices.COMException e)
			{
            string strMsg = 
               "Link9.Bin.Data.DTS.ExecutePackage()"
               + " \n Computer Name: "   + package.CreatorComputerName
               + " \n Creator Name: "    + package.CreatorName
               + " \n Package Name: "    + package.Name
               + " \n Server Name: "     + serverName
               + " \n Event Source: "    + PES.EventSource
               + " \n Description: "     + PES.Description
               + " \n Error Code: "      + PES.ErrorCode
               + " \n Exception: "       + e.Message
               + " \n Source: "          + PES.Source
               + " \n HelpFile: "        + PES.HelpFile
               + " \n HelpContext: "     + PES.HelpContext
               + " \n InterfaceError: "  + PES.InterfaceError;

            throw new ApplicationException( strMsg, e );

			}//catch

			catch(System.Exception e)
			{
            string strMsg =
               "Link9.Bin.Data.DTS.ExecutePackage()"
               + " \n Package Name: "    + pkgName
               + " \n Server Name: "     + serverName
               + " \n Exception: "       + e.Message;

				throw new ApplicationException( strMsg, e );
			}//catch

         finally
         {
            if ( package != null )
            {
               package.UnInitialize();
               // force Release() on COM object
               System.Runtime.InteropServices.Marshal.ReleaseComObject(package);
               package = null;
            }//if

         }//finally

		}//ExecutePackage


	}//class
}//namespace

The answer to getting answered -- faq855-2992