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.