February 28, 2021
Hot Topics:

Building a Windows Workflow SQL Server Integration Services Activity

  • By Jeffrey Juday
  • Send Email »
  • More Articles »

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;
         retStatus = ActivityExecutionStatus.Closed;

   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.

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 ==
      ActivityExecutionContext context =
         sender as ActivityExecutionContext;

      WorkflowQueuingService qService =

      WorkflowQueue queue =

      object obj = queue.Dequeue();

      DoUnsubscribe(context, this);



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

   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.

Page 2 of 4

This article was originally published on October 27, 2008

Enterprise Development Update

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

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