BizTalk 2009 Database Diagram Now Available

The BizTalk 2009 product development team has released a pdf diagram of the structure of the BizTalk 2009 databases. You can download yourself a copy at the link below.

BizTalk 2009 Database Diagram PDF

How to Recover the XML for an Acivity

Recently I created an activity, then saved off the XML from Excel to a folder (let’s call it v1) to deploy using the command like tool BM.exe.

I needed to make significant changes to the activity, so significant in fact, that I needed to re-create the activity entirely, so I made a new activity (call it v2) and made the mistake of overwriting the v1 XML file. When I wanted to remove the previous (v1) activity, I realized that I had no way to remove it because BM.exe requires the use of the saved off XML file.

Being fairly new to BAM, I wondered, how do I remove the v1 activity?

Well, there is a way to recover the XML from the v1 activity.

From the command line, execute:

bm.exe get-defxml -FileName:BAMDefinition.xml

That command recovers the configuration from the BAM DB which can then be leveraged to delete the v1 version of the activity.

Note: You must edit the XML to maintain the activities you want, if you exec the remove without editing the XML first you remove ALL your activities in the DB.

Then you can then execute:

bm.exe remove-all -DefinitionFile: BAMDefinition.xml

You should never delete anything surrounding BAM by hand, this will cause major problem if you do.

Using MSBuild Extension Pack with BizTalk 2006

As many of you already know MSBuild is not compatible with BizTalk projects out-of-the-box. This fact can be a hurdle to overcome if Team Foundation Server (TFS) is being used to automate builds. The problem is TFS uses MSBuild exclusively in its build process. The workaround was to use an MSBuild task that called a NANT build to execute the BizTalk implementation. This was clumsy and not intuitive.

To solve this issue a group of developers got together and created the MSBuild Extension Pack Project. The Extension Pack provides MSBuild Tasks for BizTalk 2006, SQL 2005, SQL 2008, among others. Tasks for BizTalk include tasks for checking the existence of an Application, adding/removing References, starting/stopping Applications, and creating/deleting Applications. The Extension Pack includes a help file and samples for all this functionality.

BAM: The Case of the Missing Pivot Table Data

I had a recent problem getting a pivot table to display its data on the BAM portal. I went thru a series of steps to get it working, so in case you have a similar problem here are some things to check to get the data displaying.

To frame things: I had a BAM activity with a series of data points to be tracked. I needed to extract a very simple count/sum cube from the data.

I set up the activity and went thru the view setup in Excel. I had a dimension which tracks a calling system and a then I wanted to get a count/sum of another field which was setup as an integer.

I couldn’t get it working; the view in the portal wouldn’t show any data at all. The calling system wouldn’t appear and neither would the count. I tried to set up the view as both a count (like a rowcount) and a sum and neither one showed the data.

I was pretty sure that I set the activity up correctly so I began to look at different things that could be wrong.

One of the first was to check if the SQL job "TrackedMessages_Copy_BizTalkMsgBoxDb" was running, it was.

Next, I used the SQL Management Studio to connect to Analysis Services on the server that BAM was configured on. I located the cube I was interested in, right clicked on it and select "Process...", data should have appeared. It didn’t. Something else was wrong.
I checked the associated SSIS package which is called BAM_AN_ which is responsible for populating the cube. I ran the package and was finally able to see the data in the BAM Portal.

Documentation for this can be seen here: http://msdn.microsoft.com/en-us/library/aa560072.aspx

So, to get things working right, I scheduled the SSIS package to the correct time interval and things were on track.