You are here:   Home
  |  Login

Search Results for BizTalkBill's Ramblings

BizTalk BAM Archiving

Aug 29

Written by:
8/29/2012 2:20 PM  RssIcon

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.


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


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.



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


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


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


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


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


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


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.


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

You can download my SSIS package from


4 comment(s) so far...


Re: BizTalk BAM Archiving

Thanks for sharing the information. It was indeed helpful especially the idea of executing one SSIS package instead of creating one job per package.
Hopefully you will be able to help me in the following query.
We are using tidal as a scheduling application and to schedule SSIS packages as tidal job, a service account is needed. My question is, what permissions do a service account need to execute BAM_DM SSIS packages?

Thanks again!

By Tahir on   8/30/2012 1:01 AM

Re: BizTalk BAM Archiving

That is a very good questions, I have never tried to run them with any account that is either not the SQL Agent account or an administrator on the SQL server. I would 1st try an account that is a member for the BizTalk Operators group, if that does not work, I would then try an account that is a member of the BizTalk Server Administrators group and see how you go. You may need to add the account to the SSIS security, but see how you go with the BizTalk groups.


By Bill Chesnut on   8/30/2012 10:39 PM

Re: BizTalk BAM Archiving

You can also create one job and call each package from each step in the job.

By ron on   5/8/2013 6:54 AM

Re: BizTalk BAM Archiving

You can create one job and call each package from each step, but when you deploy or un-deploy an activity, you would need to change the job, my SSIS packages run the packages for all deployed activities, cutting out this periodic maintenance to the job.

By Bill Chesnut on   5/8/2013 9:49 PM

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Security Code
Enter the code shown above in the box below
Add Comment   Cancel 



MCP.gif MCT.gif MCPD(rgb)_506.gif