BizTalk BAM Archiving

Posted By: Bill Chesnut

There have been several previous blog posts by several different authors about BAM Archiving, I decided that it would be good if all of the information was in one article, so here I go.

To start off I have used information from Richard Seroter’s Blog post BizTalk BAM Data Archiving Explained which gives a good description of the BAM Archiving structure.  What I found though was there was not any really good material on what I need to do to make the BAM archiving happen.

I started investigating and found that what I had thought was automatically happening, the running of the BAM SSIS package was not automatically happening, the only part of the BAM processing that happens automatically is the Real Time Aggregation (if you have SQL Enterprise Edition).

So now I need to figure out what I need to do to get BAM Archiving working on my current clients site.  I found that I needed to go into SSIS and look at all the BAM SSIS packages that were deployed and then setup a SQL Agent job to run these, but thinking more about BAM, I was looking for a way to not have to create or update a SQL Agent job every time that a new BAM Activity was added or removed.

Below is a look at the BAM SSIS packages that are deployed in a typical BizTalk installation with EDI and the ESB Toolkit.

SNAGHTML1ca09ca2

I also found that a list all the BAM Activities are in the BAMPrimaryImport Database

image

The bam_Metadata_Activities table also include the OnlineWindowTimeUnit and OnlineWindowTimeLength and if Archiving was enabled

So I had an idea, what about another SSIS package that reads the bam_Metadata_Activities  table and then executes all the SSIS packages for the deployed BAM Activities, this way only the currently deployed BAM Activities packages would be run.

I created an SSIS package that used the Execute SL Task to return a row set with the name of all the BAM SSIS packages, it then passes the row set to a Foreach Loop that runs an Execute Package Task for each SSIS package found.

image

I then created an deployment for the SSIS package and deployed it to the BizTalk SQL Server

image

Once the package is deployed in SSIS you can right click on the package and run package or you can create a SQL Agent job to execute the SSIS on a regular schedule, that is what I choose to do

image

Create the Job and then add a step that executes the SSIS package

image

If necessary you can update the location where the BAMPrimaryImport and SSIS packages are stored

image

Then Create a schedule, I have chosen to run the archive once a week at 2:00am Sunday morning

image

You can then test the job, but right clicking on the Job and start job at step and wait for it to complete

image

Once the job has completed you can look at the contents of the BAMPrimaryImport table bam_Metadata_Partitions and see which tables have data to be archived, but the archive process will not start until the CreationTime of the row in the  bam_Metadata_Partitions  is older than the OnlineWindowTimeLength, so in my example archiving will not start until 27/02/2013, so I cannot stress enough how important it is to setup archiving when you install BizTalk.

image

This should be all the information that you need to setup BAM archiving.

You can download my SSIS package from http://www.biztalkbill.com/Default.aspx?id=15&tabid=60

Comments are closed.