This post is by a co-worker of mine, who sums up the differences between Windows Workflow Foundation and BizTalk better than I could ever hope to.
http://geekswithblogs.net/bloesgen/
So you are starting to realize the potential of BAM, but you notice that the times in your Business Milestones are a bit off. This is because BizTalk logs Business Milestones in UTC. So, I thought I would help you all fix this to show the time in your local time zone.
If you get into the DB call BAMPrimaryImport, and look for a SP call bam_ActivityName_PrimaryImport we are going to add a few statements after the BEGIN statement.
--A variable to hold the difference between UTC and your local time
DECLARE @TimeDifference int
--Calculate the difference
SELECT @TimeDifference = ROUND(DATEDIFF(mi, GetUTCDate(), GetDate()), -1)
--Fix the time difference
SELECT @BusinessMilestone = DATEADD(mi, @TimeDifference, @BusinessMilestone)
SELECT @2ndBusinessMilestone = DATEADD(mi, @TimeDifference, @2ndBusinessMilestone)
Ther you go! Now you will need to do this for each Activity you defined.
Ever notice the duration field in a BAM query? You end up with a value like 1.102304-3e. Well BAM by default shows you the duration in days. It is iactually quite easy to adjust this to whatever you want.
All you need to do is make a change to two of the views that are created for you;
1. The ActiveAliasView
2. The CompletedAliasView
Both of which can be found in BAMPrimaryImport. You are loking for a statement that will look like this;
[Duration] = CAST([TimeStamp] AS FLOAT) - CAST([TimeStamp] AS FLOAT
What you need to add is this;
[Duration] = CAST([TimeStamp] AS FLOAT)*24(For Days)*60(For Minutes)*60(For Seconds) - CAST([TimeStamp] AS FLOAT*24*60*60
You will notice that if you query the BAMPrimaryImport DB that all the durations will appear as seconds, but if you have a processed cube, it will still reflect the old data, and when you add the new data, it will become unreliable. So if you need to do this, you must be prepared to start the cubes from scratch.
I have decided to relocate my BizTalk (Well primarily anyways) blog to GeeksWithBlogs.
I am a BizTalk consultant with Neudesic in The Phoenix area. I have been working with BizTalk since the tail end of BTS 2000. I have deployed close to 50 production systems since BTS2002, and recently decided to start becoming proficient in Windows Workflow Foundation (WF).
So what else can I say, but let the Blogging begin.
Todd