March 5, 2021
Hot Topics:

Building a Windows Workflow SQL Server Integration Services Activity

  • By Jeffrey Juday
  • Send Email »
  • More Articles »

Workflow Service: SSIS_Service

The ExecutePackage function in SSIS_Service starts SSIS Package execution. The important parts of the ExecutePackage function appear below.

public CurrentPackageStatus ExecutePackage(string packageId,
   IComparable queue)
   SSIS_ServiceTrace.WriteLine("Executing " + packageId);
   SSIS_Package_Execution exec;
   SSIS_Package_ExecutionContext parms;
   Thread threadExec;
   CurrentPackageStatus status = CurrentPackageStatus.Missing;

   if (_packages.ContainsKey(packageId))
      parms = _packages[packageId];

      //Once here, you want to be the only one changing this
      lock (parms)
         if (_packages[packageId].IsExecuting)
            status = CurrentPackageStatus.Executing;
            parms.IsExecuting = true;

            parms.WorkflowInstance =
            parms.ResponseQueue = queue;

            exec = new SSIS_Package_Execution(parms, this);

            threadExec = new Thread(new ThreadStart(exec.Execute));

            parms.CurrentRunningThread = threadExec;


            status = CurrentPackageStatus.Started;
      status = CurrentPackageStatus.Missing;
      SSIS_ServiceTrace.WriteLine("NOT FOUND " + packageId);

   return status;


A collection of SSIS_PackageExecution_Context classes maintains the execution information, including the thread executing the package and WorkflowQueue to notify when package execution has ended. Multiple steps in a workflow may be executing SSIS Packages so, to avoid race conditions, SSIS_Service locks the collection.

Earlier, I mentioned I would explain why I chose to implement package execution in a Workflow Service. I wanted to be sure two workflows couldn't simultaneously run the same SSIS Package. I also wanted to keep the Activity configuration simple. I figured that the less the Activity needed to know to execute a Package, the easier it would be to change the package without modifying the workflow.

The details of SSIS package execution are wrapped in SSIS_PackageWrapper and SSIS_Package_Execution classes. I'm going to look at those next.

SSIS Package Execution

The SSIS_Package_Execution Execute function runs on a thread created in the SSIS_Service class. Execute appears below.

public void Execute()
   SSIS_PackageWrapper package = new SSIS_PackageWrapper();

      _parms.IsExecuting = true;
      _parms.ErrorStack = "";
      _parms.ErrorLastRun = false;

      package.Open(_app, _parms.PackagePath, _parms.PackageName);



      _parms.IsExecuting = false;
   catch (Exception ex)
      SSIS_ServiceTrace.WriteLine("ERROR Execute "
         + _parms.PackageID + ex.Message + ex.StackTrace);
      _parms.ErrorStack = ex.Message + " " + ex.StackTrace;
      _parms.ErrorLastRun = true;

SSIS_PackageWrapper runs the SSIS package. A complete discussion of the SSIS Object model is beyond the scope of this article. You can view a complete discussion in the resources at the end of this article.

Executing an SSIS Package is straightforward. The SSIS_PackageWrapper Open and Execute functions appear below.

public void Open(Application app, string path, string packageName)
   _path = path;
   _name = packageName;
   _app  = app;

   _package = _app.LoadPackage(Path + "\\" + Name, null);


void Execute ()

Page 3 of 4

This article was originally published on October 27, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date