October 18, 2018
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

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

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

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.


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