Populating BizTalk Server Business Activity Monitoring (BAM) from a SQL Server Integration Services (SSIS) package

As some of you may know, BizTalk Business Activity Monitoring (BAM) activities can be populated from outside of BizTalk solutions. Say you have a large solution where you want to monitor the full lifecycle of certain data. For example, you have prospective customers arriving in an Excel file that is imported into SQL Server via SSIS. This prospective customer is reviewed by a user on a SharePoint site and when accepted a message is fired off to an ERP system via BizTalk Server. BAM can monitor all  that and more! In this post I will show a simple example of the initial step of this scenario that involves BAM in SSIS packages (in SQL Server 2005).

As a basis let’s use the Basic Lesson 2 of the “Creating a Basic Package Tutorial: Lesson Packages” tutorial that is part of the SQL Server Integration Services Product Samples. As you can see this tutorial reads an Excel workbook containing prospective customers, sorts it by State and City, adds a column with the customer’s full name, then inserts the data into a SQL Server table.

Based on this scenario we can start thinking about how to use BAM to aggregate information about these prospective customers: for example, the number of prospective customers received over time by State and City. From Excel with the BAM Add-In enabled, let’s define the following Customer activity:

 

 

Then let’s create a Customer View on the Customer activity to monitor a few things including a dimension on State and City:

 

After this is created we can then define our pivot table. I chose to monitor the State and City on the left and Time on top, looking at the aggregated number of customers and total salary in the middle:

From here we can save the Excel file and deploy it using bm.exe:

So now from the SSIS solution we can add a Script Task between the “Add Fullname Column” component and the “Destination – ProspectiveCustomer” component of the package’s Data Flow.

I have added the script Task as a transformation type:

This means it will call the script for every row of the data coming from the previous step and forward the rows to the next step in the data flow. Let’s then select the columns needed for the Script from the “Input Columns” tab:

To use BAM outside of BizTalk code itself we use the file Microsoft.BizTalk.Bam.EventObservation.dll, found in the Tracking folder under the BizTalk installation folder. You can use this DLL from anywhere without paying extra licences on top of your existing BizTalk Server licence (BizTalk 2006 and later). To use the DLL from SSIS you must copy it to the .NET Framework 2.0 location (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 on my machine), and also add it to the GAC of your dev environment, and repeat the same on your test/production environments.

We have to add it to the .NET Framework 2.0 location so that we can reference it from the Script Task (it won’t show up there otherwise). So, from the SSIS package’s “Design Script” windows we are now able to reference it:

 

 

We are finally getting to the code itself! To add new entries to the BAM activities from the EventObservation DLL we can use the DirectEventStream and the BufferedEventStream classes. I implemented this tracking as an Asynchronous Business Event for the performance improvement it offers (it batches the calls to sustain higher throughput). Since SSIS has high performance it is important to minimize how BAM interferes with it as much as possible. Also note that I used a new guid for each customer for the activity id to ensure it’s unique. If your BAM activity has Continuation like in the scenario described at the beginning of the post you would have to hang on to this activity id (probably by adding it as an output column of the Script Task and inserting it into the database table as well).

This is the final Script code from my sample 

[UPDATE: Moved the flush to the PostExecute method and the creation of the BufferedEventStream outside of the ProcessInputRow method to increases performance since now the object is only created once and only flushed once]: 

 

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.BizTalk.Bam.EventObservation

 

Public Class ScriptMain

    Inherits UserComponent

    ‘Will send data to BAM via Buffered Event Stream

    Dim es As New BufferedEventStream(“Integrated Security=SSPI;Data Source=.;” & _

                                      “Initial Catalog=BizTalkMsgBoxDB”, 0)

 

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        ‘Get a unique activityID

        Dim activityID As String = System.Guid.NewGuid.ToString()

 

        ‘Begin and populate activity with details

        es.BeginActivity(“Customer”, activityID)

        es.UpdateActivity(“Customer”, activityID, _

                          “City”, Row.City, _

                          “Firstname”, Row.FirstName, _

                          “Imported”, DateTime.Now, _

                          “LastName”, Row.LastName, _

                          “Occupation”, Row.Occupation, _

                          “Salary”, Row.YearlyIncome, _

                          “State”, Row.State)

 

        ‘End and flush activity

        es.EndActivity(“Customer”, activityID)

 

    End Sub

 

    Public Overrides Sub PostExecute()

        ‘Flush activity to bam

        es.Flush()

        MyBase.PostExecute()

    End Sub

 

End Class

 

 If we now run the package we can see that it runs successfully:

It does add a couple of seconds on top of the one second SSIS takes to insert the 809 rows into the database. But in return we can trust that BAM is in the background now populating the cube for the users’ pleasure! Since we have aggregation in this solution we need to run the OLAP cube by executing the SSIS package that was created for us (BAM_AN_Customer View package inside MSDB)

Here is what the Customer View aggregation looks like from the BAM Portal. California sure does have a lot of potential customers!

From here on it’s the usual BAM Portal interface. For example If I double click on the 2 customers imported from Birmingham, Alabama, it lists them:

And if I select the first one it shows that Customer’s tracked details:

This is very powerful – you can use it for Continuation between your BizTalk, SSIS, and custom projects to give users a complete view of the process as I mentioned at the beginning of the post. You can also create any kind of flashy reporting on the BAM Analysis view, for example from Reporting Services:

 

Of course this solution is a very simple sample. You’d want to look at using a package variable as the BAM connection string, and maybe BAM Interception (shipped in BizTalk Server 2006 R2) not to tie the package directly to the BAM view, and investigate ways to improve performance.