Building a Windows Workflow SQL Server Integration Services Activity

By Jeffrey Juday

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;
         }
         else
         {
            parms.IsExecuting = true;

            parms.WorkflowInstance =
               WorkflowEnvironment.WorkflowInstanceId;
            parms.ResponseQueue = queue;

            exec = new SSIS_Package_Execution(parms, this);

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

            parms.CurrentRunningThread = threadExec;

            threadExec.Start();

            status = CurrentPackageStatus.Started;
         }
      }
   }
   else
   {
      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();

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

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

      package.Execute();

      _service.ExecuteEnded(_parms.PackageID);

      _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;
      _service.ExecuteEnded(_parms.PackageID);
   }
}
}

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 ()
{
   _package.Execute();
}