January 19, 2020
Hot Topics:

Building a Windows Workflow SQL Server Integration Services Activity

  • October 27, 2008
  • 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

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