BAM and Reporting
A recent post was written about attempting to query the DTADB database to capture data and send it to a particular customer. I suggested that this is a purpose in Business Activity Monitoring. I have created a sample application that demonstrates some of the powers of BAM.
In this example orders are processed by Biztalk, and key data elements are extracted and sent to the BAMPrimaryImport database.
Another orchestration takes the information in the database and creates a report and sends it out to the customer.
Setup Instructions:
- Download this file.
- Run the following command from a dos prompt to create the necessary BAM objects: \Tracking\BM.exe deploy ..\BAMReporting\ActivityInfo.xml
- Run the extractBAM.sql against the BAMPrimaryImportDb
- Deploy the solution
- Modify the binding.xml file to represent your enviornment
- Bind the ports using the Deployment wizard.
- Enlist the orchestrations
- Drop Order 1.xml, Order 2.xml, and Order 3.xml into the Input folder
- Wait and then drop the Confirmation 1.xml, Confirmation 2.xml, and Confirmation 3.xml into the Input folder
- Drop the Trigger.xml file into the input file and in the output folder there should be an email xml file that shows various BAM data.
This actually goes against my better concience on at least 2 fronts:
- BizTalk has the ability to do a lot of things, but sometimes it shouldn’t. I know of some people think that it is better than sliced bread, but a more elegant solution would be to use SQLReporting services that would go against the BAMPrimaryImportDB
- It uses a trigger file to kick off the report, there could be a better way, with a proper stored proc to do some marking of the data so no duplicate reports get run
Eric,
I am attempting to deploy your solution and am getting a “SQL Server does not exist or access denied” error. I have successfully deployed the XML file on the server. Any idea what would cause this or what I could look at to find the issue?
Thanks,
Fred
Fred,
You need to go to the properties of the project and in the deployment, you need to change the server that the BizTalkMgmtDB from (local) to your particular server. I am sorry, I did not put those instructions in the deployment instructions.
Comment by Fred Heinemann — April 19, 2006 @ 11:08 am
Does something additional need to be changed in the orchestration? I was able to deploy the solution and the binding.xml file. Everything looks okay but I am getting an error when it hits the extract data step in the processing orchestration.
Uncaught exception terminated service BAMReporting.ProcessOrder(37f150bd-7e2c-8221-899c-e0135931de42), instance f90bfb25-41c2-4bed-bc64-db838651dc43
SQL Server does not exist or access denied.
Exception type: SqlException
Source: .Net SqlClient Data Provider
Target Site: System.Data.SqlClient.SqlInternalConnection CreateConnection()
Help Link:
Additional error information:
I am still trying to figure out all the pieces of BizTalk and this is completely new to me. Thank you for your help with this.
Fred
Comment by Fred Heinemann — April 19, 2006 @ 12:05 pm
I put an updated solution there, so there should not be any issues now….
Comment by eric — April 24, 2006 @ 4:38 pm