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.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read