Building a Windows Workflow SQL Server Integration Services Activity

Workflow has always been a part of software development. Often, however, workflow has been confined to a narrow solution domain with few extensibility points into multiple solution domains. On the Microsoft platform, Windows Workflow Foundation (WF) has reversed the workflow trend, providing a multi-domain solution with broad extensibility and hosting options.

Unfortunately, not all workflow style development tools in the Microsoft world have embraced WF. Take, for example, SQL Server Integration Services (SSIS). SSIS is complete in its solution domain (Extract Transform and Load), but lacking in its capability to, for example, completely leverage the .NET framework. I'm going to show you how you can bring SSIS into WF and begin to fully leverage the .NET Framework development environment around SSIS.

Why WF and SSIS?

I'm a technical person, but I'm a business person first. In my opinion, the business problem should dictate the technology. So, I thought I should elaborate on the synergies achieved by making SSIS available to WF.

WF handles things such as approval processes or coordinates the actions between multiple computer systems. SSIS moves large amounts of data into databases. It's not hard to imagine an approval process or some system coordination ending with moving data and being considered incomplete until the data move has finished.

SSIS is missing Policy features, Business rule features, and deep integration with the .NET Framework. Policy and business rules features allow for elaborate business decision logic. Deep integration with the .NET Framework allows for more elaborate applications. So, for example, executing or changing different SSIS packages depending on a collection of business conditions using WF Policies is easier to implement, version, and change in WF than SSIS.

I'm going to show you an SSIS Activity I created for WF. First, I'll do an architectural overview of the sample and then look at each solution component in more detail later in the article.

The Sample Solution Cast

Windows Workflow combines standard configurable components or patterns, a tool to compose and configure the components, and a runtime hosting framework.

Figure 1 shows all the components of the sample solution and the relationships between the components. I'm going to briefly describe each component of the architecture before delving into the details later in the article.

Figure 1: Solution Architecture

Activities are the configurable workflow components and patterns. WF includes a standard set of Activities with base classes and interfaces for building and extending activities. In the sample, I've built a SSIS Event Activity.

Activities may work in conjunction with a Service living inside of the Workflow Runtime environment. Services often decouple the Activity from the world outside of the Workflow. In the sample, the SSIS Service handles the SSIS package loading and execution.

A SSIS_Package_ExecutionContext stores SSIS package execution information and maps the execution back to a running workflow. SSIS_Service maintains a collection of SSIS_Package_ExecutionContexts.

Activity and Service interaction is often loosely coupled and asynchronous. A set of Workflow Queuing classes provided by WF is the foundation for the loosely coupled asynchronous interaction between the SSIS_Activity and the SSIS_Service.

That's the high-level view. It's time to look at the details, starting with the SSIS_Activity.

Choosing an Activity

The solution design inspiration for the SSIS_Activity came from the WF FileWatcher Activity sample shipping with the .NET SDK samples.

There are many different types of Activity features, each with a bevy of Interfaces and Classes to support a particular function or pattern. So, a complete introduction to activity development is beyond the scope of this article. For a complete introduction to Custom Activities, see the resources at the end of this article.

Two key Activity design decisions were: deciding to handle SSIS package execution inside a Workflow Service and deciding to make the Activity Event Driven. Later in the article, I'll talk about the motivations behind the Workflow Service design decision. First, I want to outline how I implemented my Event-driven Activity.

Implementing an Event-Driven Activity

Event-driven Activities often complete their work asynchronously. Event-driven activities are good candidates when an Activity depends on something external to the workflow to execute the bulk of the work. Aside from implementing common Activity functions, there are a number of features an Event-driven activity must implement to coordinate external workflow communication.

All Activities must implement an Execute function. Execute is the entry point of the Activity. The WF Runtime invokes the Execute function when it first encounters the Activity in the workflow. The Execute function for SSIS_Activity appears below.

protected override ActivityExecutionStatus
   Execute(ActivityExecutionContext context)
{
   CurrentPackageStatus status;
   ActivityExecutionStatus retStatus;

   status = this.DoSubscribe(context, this);

   switch (status)
   {
      case CurrentPackageStatus.Started:
         retStatus = ActivityExecutionStatus.Executing;
         break;
      default:
         retStatus = ActivityExecutionStatus.Closed;
         break;
   }

   return retStatus;
}

SSIS packages can take some significant time to execute. I wanted the Activity to exit from its Execute function quickly so a workflow running a parallel activity could continue to execute the other Activities in a Parallel Composite Activity. As you can see, the function does some initialization and returns ActivityExecutionStatus.Executing.

IEventActivity is the Event Activity interface. The IEventActivity implementations appear below.

public IComparable QueueName
{
   get { return this.queueName; }
}

public void Subscribe(ActivityExecutionContext parentContext,
   IActivityEventListener<QueueEventArgs> parentEventHandler)
{
   this.DoSubscribe(parentContext, parentEventHandler);
}

public void Unsubscribe(ActivityExecutionContext parentContext,
   IActivityEventListener<QueueEventArgs> parentEventHandler)
{
   this.DoUnsubscribe(parentContext, parentEventHandler);
}

QueueName Property is probably the most interesting of the IEventActivity class members. SSIS_Activity uses the WorkflowQueuingService to communicate with the SSIS_Service. Implementing IEventActivity is required to manage subscribers to a WorkflowQueue. QueueName is a unique identifier for the WorkflowQueue.

Implementing IEventActivity<> is required to receive a notification when data appears in the Queue. The IEventActivity<> OnEvent appears below.

public void OnEvent(object sender, QueueEventArgs e)
{
   if (this.ExecutionStatus ==
      ActivityExecutionStatus.Executing)
   {
      ActivityExecutionContext context =
         sender as ActivityExecutionContext;

      WorkflowQueuingService qService =
         context.GetService<WorkflowQueuingService>();

      WorkflowQueue queue =
         qService.GetWorkflowQueue(this.QueueName);

      object obj = queue.Dequeue();

      DoUnsubscribe(context, this);
      DeleteQueue(context);

      context.CloseActivity();

      return;
   }
}

You may have noticed that the Execute function sets the SSIS_Activity up as a subscriber to the Queue. Inside the DoSubscibe function, the SSIS_Activity calls the SSIS_Service ExecutePackage function. The SSIS_Activity DoSubscribe function appears below.

private CurrentPackageStatus
   DoSubscribe(ActivityExecutionContext context,
   IActivityEventListener<QueueEventArgs> listener)
{
   CurrentPackageStatus status;
   WorkflowQueue queue = CreateQueue(context);
   queue.RegisterForQueueItemAvailable(listener);

   SSIS_Service SSIS = context.GetService<SSIS_Service>();

   status = SSIS.ExecutePackage(PackageID, this.QueueName);

   return (status);
}

When package execution completes in the SSIS_Service class, the results are posted to the SSIS_Activity Queue. Because the SSIS_Activity is a Queue listener, WF invokes the OnEvent on the SSIS_Activity and the Activity completes.

A peripheral, but important, detail to point out is this: When the host initializes the Workflow runtime, it also invokes the AddService function. Adding the SSIS_Service to the runtime environment makes the service available to the SSIS_Activity.

Thus far, I've covered how an Event Activity must be implemented and how the SSIS_Activity builds and responds to communication with the SSIS_Service. Now, I'm going to cover how the SSIS_Service class executes the SSIS Package and returns the results back to the SSIS_Activity.

Building a Windows Workflow SQL Server Integration Services Activity

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();
}

Once Package execution completes, the SSIS_Services takes over, posting the text "Done" to the WorkflowQueue set up by the SSIS_Activity, invoking the OnEvent function in the SSIS_Activity, and the SSIS_Activity closes.

Conclusion

Windows Workflow Foundation gives a developer deep access to the .NET Framework, as well as a foundation for building workflows for all types of scenarios. Unfortunately, workflow style tools such as SSIS are not implemented in WF. You can fix this by building your own WF SSIS_Activity,; leveraging the features of WF and the SSIS Object model.

Resources



About the Author

Jeffrey Juday

Jeff is a software developer specializing in enterprise application integration solutions utilizing BizTalk, SharePoint, WCF, WF, and SQL Server. Jeff has been developing software with Microsoft tools for more than 15 years in a variety of industries including: military, manufacturing, financial services, management consulting, and computer security. Jeff is a Microsoft BizTalk MVP. Jeff spends his spare time with his wife Sherrill and daughter Alexandra.

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds