What Happens When You Deploy a BAM Activity
When you deploy a BAM Activity using bm.exe, BizTalk Server does more than create database tables. Instead, it also generates a comprehensive set of SQL Server views.
If you’ve ever tried to query tracking data directly from the BAMPrimaryImport database, you already know the challenge. The underlying table structure is complex and difficult to navigate.
Why BAM Uses SQL Server Views
To simplify access, BizTalk exposes tracking data through SQL Server views. These views act as the primary interface for:
- Custom SQL reports
- Dashboards
- Excel‑based analysis
As a result, you should always query BAM data through views rather than accessing tables directly.
What the deploy-all Command Creates
When you run the deploy-all command with bm.exe, BAM automatically builds the full data model for your activities. This process includes both the required tables and the corresponding views.
Moreover, Microsoft guarantees the stability of these views across future product versions. Because of this commitment, you can safely consume BAM view data using any programming tool that works with SQL Server.
Understanding the Two Meanings of “View” in BAM
In the context of BAM, the term view has two distinct meanings.
A SQL Server view represents a virtual table defined by a SELECT statement. In contrast, a BAM view derives from a BAM activity through subsetting, aggregation, or aliasing.
However, BizTalk ultimately relies on SQL Server views to implement both BAM activities and BAM views. Therefore, all BAM tracking data surfaces through SQL Server’s view mechanism.
📝 One-Minute Brief
When you deploy a BAM activity, BizTalk creates several SQL Server views in the BAMPrimaryImport database to hide the complexity of the underlying table structures. This post explores the three essential types of views: bam_{ActivityName}_Active, which shows in-flight instances; bam_{ActivityName}_Completed, for finished processes; and bam_{ActivityName}_All, which provides a unified view of the entire lifecycle. Understanding these views is the key to building custom reports or dashboards without querying messy internal tables.
The views that are directly derived from the deployment of a BAM activity:
- bam_<Activity Name>_ActiveInstance: Returns the list of active activity instances based on the records stored in the active activity table.
- bam_<Activity Name>_AllInstances: Joins the active and complete activity views, returning a list of all the BAM activity instances. In other words, return all instances of the activity regardless of state.
- bam_<Activity Name>_AllRelationships: Joins the active and complete activity relationship tables, returning a list of all the relationships of the BAM activity instances.
- bam_<Activity Name>_CompletedInstances: Returns the list of the completed activity instances based on the records stored in the completed activity table.
- bam_<Activity Name>_InstancesForArchive: return all instances of the activity that will be archived on the next execution of the DTS Package.
Additionally, a new set of views is created for each activity view that we include in the BAM activity model:
- bam_<ActivityName>_<ActivityView Name>_ActiveView: Returns the list of active activity instances, including the calculations of the required dimension levels.
- bam_<ActivityName>_<ActivityView Name>_ActiveAliasView: Return the list of items currently in the active state by alias.
- bam_<ActivityName>_<ActivityView Name>_CompletedView: Returns the list of completed activity instances, including the calculations of the required dimension levels.
- bam_<ActivityName>_<ActivityView Name>_CompletedAliasView: Items currently in completed state by alias.
- bam_<ActivityName>_<ActivityView Name>_View: Joins the two previous views (ActiveView and CompletedView), returning the list of all activity instances included the calculation of the required dimension levels, in other words, all items in the view.
These views enable two key functionalities in the BAM infrastructure: At the first level, activity views are the fundamental mechanism to expose BAM data to other applications, including other components of the BAM infrastructure, like the aggregation process. Additionally, these views include some of the calculations required by elements such as measures that are not directly included in the activity tables.
The views in the BAMPrimaryImport database are the “Public API” of your tracking data. By using these views, you ensure that your custom reporting tools remain robust, fast, and easy to maintain as your BizTalk environment grows.
Hi Sandro,
I came across similar kind of scenario where bam data is not moving to completed tabled even though the status of Iscomplete is 1. Not sure how we can check to make it resolve. Any suggestions from you end.