Posts Tagged ‘BizTalk’

Basically there are two properties inside the schema element decides whether an element can be absent from the document: Min Occurs and Nillable.

If Min Occurs property is set 0 then that element can be absent from the XML message but if it is set to 1 it has to be present though its value can be empty. This is useful to reduce the size of the document if only not all the elements are mandatory to end systems.

In other hand, if the Nillable property of the element is set to true, this will indicate that the value of an element in the document may be null. This NULL values will be expressed with xsi:nil = true attribute in the element, ex:

<IntExist xsi:nil="true" />

An element with the attribute xsi:nil = true explicitly means that the value is unavailable or unknown at that moment and sometimes the end system explicitly requires to be notified that the value of the element is NULL so that they can take appropriate action.

In this sample scenario we will have a 2 mandatory elements that can be nillable that we need to map to the destination schema. In this scenario all the destination elements are also mandatory and we need to fill them with a valid value or specify the nillable property as true: so if the element exist we need to map the correct source value otherwise we need to set the destination element as nillable.

The first element “DateExist” is a mandatory element that can be null. If null we need to set a null value in the destination element also as null, otherwise we need to map the source value. To accomplish that we need to:

  • Drag one IsNil Functoid from the Toolbox window onto the Grid.
    • Drag a link from the “NillValue” field element in the source schema to the IsNill Functoid
  • Drag one Nil Value Functoid from the Toolbox window onto the Grid.
  • Drag one Logical NOT Functoid from the Toolbox window onto the Grid.
  • Drag one Value Mapping Functoid from the Toolbox window onto the Grid.
  • To create a rule for mapping the value if the element is null
    • Drag a link from the IsNill Functoid to the Nil Value Functoid
    • Drag a link from the Nil Value Functoid to the “NillValueOutput” field element in the destination schema
  • Otherwise, To create a rule for mapping the value if the element different of null
    • Drag a link from the IsNill Functoid to the Logical NOT Functoid
    • Drag a link from the Logical NOT Functoid to the Value Mapping Functoid Functoid
    • Drag a link from the “NillValue” field element in the source schema to the Value Mapping Functoid
    • Drag a link from the Value Mapping Functoid to the “NillValueOutput” field element in the destination schema

Do the exact same logic for the second element present in the source schema.


Sometimes the maps are misunderstood and notorious for producing a lot of unnecessary code that may cause a in some cases lack of performance. So the question that we can and should ask is whether this is the best solution or not to address this type of operations. To respond this question we should also inspect the generated code produce by the BizTalk Mapper:

<xsl:variable name="var:v1" select="string(NillValue/@xsi:nil) = 'true'" />
    <xsl:variable name="var:v2" select="userCSharp:LogicalNot(string($var:v1))" />
    <xsl:variable name="var:v4" select="string(AnotherNilValue/@xsi:nil) = 'true'" />
    <xsl:variable name="var:v5" select="userCSharp:LogicalNot(string($var:v4))" />
      <xsl:if test="string($var:v1)='true'">
          <xsl:attribute name="xsi:nil">
            <xsl:value-of select="'true'" />
      <xsl:if test="string($var:v2)='true'">
        <xsl:variable name="var:v3" select="NillValue/text()" />
          <xsl:value-of select="$var:v3" />
      <xsl:if test="string($var:v4)='true'">
          <xsl:attribute name="xsi:nil">
            <xsl:value-of select="'true'" />
      <xsl:if test="string($var:v5)='true'">
        <xsl:variable name="var:v6" select="AnotherNilValue/text()" />
          <xsl:value-of select="$var:v6" />

In fact is a pretty decent XSLT code but the reality is that it can be better, we don’t need to use any support variables and we can remove one if condition by replacing the xsl:if condition for one xsl:choose condition.

This is a very simple approach, easy to implement and readable that you should use even in small or large messages (transformations) but only if you have to deal with a small number of nillable element.

However applying this approach in transformation that will need to deal with a large number of nillable elements, can lead to two problems:

  • A lot of unnecessary XSLT code that can in fact and of course always depending in the size of the message can lead to some lack of performance
  • A lot of functoid shapes (4 Functoids) and links (7 links) for each element that can lead to lack of visual Readability

So can we improve this solution for transformations that needs to deal with a large number of nillable elements?

Well that’s the problem, there isn’t a simple solution for that. At the first look you may think that’s easy, just copy the XSLT code inside to a Scripting Functoid and optimize the XSLT code.

However by doing that you will receive an error:

error btm1050: XSL transform error: Unable to write output instance to the following <file:///C:\…\MapNillValuesWithCustomXSLT_output.xml>. Prefix ‘xsi’ is not defined.

The problem is that the nil attribute is defined in the XML Schema instance namespace, (commonly associated with the prefix xsi) and this namespace is not declared by default in the XSL code generated by the BizTalk Mapper.

This namespace is automatically declare only if you use the Nil Functoids in the map.

So the normal solution here is… to bypass the BizTalk Mapper and generate an external XSLT code and add it to the map by specifying the Custom XSLT Path by:

  • Open the map
  • Click the grid zone and on the properties window there will be a “Custom XSLT Path” property.  Click the ellipses and navigate to the file containing the XSLT.


You then can use a similar code to check and map the elements:

  <xsl:when test="NillValue/@xsi:nil">
      <xsl:attribute name="xsi:nil">
        <xsl:value-of select="'true'" />
      <xsl:value-of select="NillValue/text()" />

However applying this approach we have a major problem for me:

  • We lose all the BizTalk Mapper functionalities.

Well, at least that I know, unfortunately there is no simple way to declared the xmlns:xsi= namespace to the stylesheet of the map.

However we can apply one small workaround, is not perfect but in most of the case it will solve my problems:

  • For only one of the nillable elements we need to use the Nil Functoids explained in the beginning of this post.
    • This will declare automatically the name xsi namespace for us.
  • In the rest of the elements we now can use Scripting Functoids with the optimized XSLT code described above


I’m still working to find a better way but until then

You can download the source code from:

BizTalk Mapper: Working With Nillable Values (xsi:nil=”true”) (92.7 KB)
Microsoft | Code Gallery

I publicly announce for the first time that I was working in an eBook about BizTalk Mapping Patterns and Best Practices, and that I was published for free, this March during BizTalk Summit 2014 London…


… At the time I said it would be published in mid-April or May but since then I’ve been slightly quiet, a few tweets and that’s it!

So how’s the book and when will it be published? This is the question that some people have asked me and that you probably are asking too, at least if you attended the event in London

Well I have some good news Smile… the book will be a reality and will definitely be for free. I’ve already made ​​the commitment to the BizTalk community and I’ll fulfill it. And the first chapters had already been sent to my awesome team of reviewers… I’m waiting for their feedback Smile!

However there are still many things to finish, the structure of the book may still suffer some changes (depending on the reviewers feedback), although it is unlikely that I make some radical changes in its current structure. And I still need to finish the main chapter “BizTalk Mapper Patterns” and one additional final chapter.

You also need to remember that this eBook will be for free and that all those involved are spending their free time to make this happen! Personally, the last few months have been difficult for me to find some additional free time to engage and finish this project… and I also cannot ask and demand nothing more from my reviewers, we all are busy people. I prefer to take a little more time and provide some quality work (I hope).

State of art and key point to take:

  • The book will be a reality and will definitely be for free
  • 12 patterns addressed in the eBook, currently 233 pages…
  • Estimated that 85% of the work is done
  • All demos are finished, finally! (is hard to invent some good and practical scenarios)
  • I promise that I will push my reviewer to send me the feedback (but they are not the blocking point)
  • I’m currently have some free time to finish the eBook!!! SmileSmile

I hope that soon I will be sending all the chapter to the reviewers and then depending on the feedback: improve it, fix it and finally publish it… so I ask you to have a little more patience.

Almost three weeks ago in my last post I release a SQL script to manager and delete the unnecessary records in the BizTalk “MarkLog” tables according to some of the best practices (you can read more here: Managing and cleaning BizTalk Server MarkLog database tables according to some Best Practices), however I knew in advance that the script had some limitations:

  • Basically the user needs to update the script manually after “installing” it. He/she needs to add and remove the databases that might not be in use (like BAM).
  • Or add, if more than one MsgBox is used.

But the beautiful of the BizTalk Community is that sometimes they provided awesome feedback, and that was exactly what happened in this case. 10 days after I published my post, I received an email from Mikael Sand, Mikael is a strong BizTalk Community member from Sweden that I had the pleasure of meeting him in person, I think the first time was two years ago in Norway, describing this exact limitations but… you are making a mistake if you think that he only described the limitations!!

Indeed he spent some time describing the limitations but I also provided me with an updated scripts with all the improvement necessary to suppress these limitations! Basically Mikael updated the script using an identical logic from another SP in the backup job so that user doesn’t need to update the script once it is installed. Also, it does not need to be updated if a database (like BAM) is added later or if you are using more than one MsgBox.

Basically the differences between the first release and this one is that:

  • Instead of hard coding implement all the delete necessary queries
/****** Delete MarkLog History from BAMAlertsApplication database ******/
DELETE FROM [BAMAlertsApplication].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BAMArchive database* *****/
DELETE FROM [BAMArchive].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BAMPrimaryImport database ******/
DELETE FROM [BAMPrimaryImport].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkDTADb database ******/
DELETE FROM [BizTalkDTADb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkMgmtDb database ******/
DELETE FROM [BizTalkMgmtDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkMsgBoxDb database ******/
DELETE FROM [BizTalkMsgBoxDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from BizTalkRuleEngineDb database ******/
DELETE FROM [BizTalkRuleEngineDb].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

/****** Delete MarkLog History from SSODB database ******/
DELETE FROM [SSODB].[dbo].[MarkLog]
WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep
  • Mikael have implementing this same operation dynamically by “stolen” the cursors logic and realservername from the BizTalk procedure “sp_MarkBTSLogs”. The cursor iterates all the databases that are backed up by BizTalk.
DECLARE @BackupServer sysname, @BackupDB sysname, @RealServerName sysname
DECLARE @tsql nvarchar(1024)
DECLARE @ret int
/* Create a cursor */
DECLARE BackupDB_Cursor insensitive cursor for
   SELECT ServerName, DatabaseName
   FROM	admv_BackupDatabases
   ORDER BY ServerName

open BackupDB_Cursor
fetch next from BackupDB_Cursor into @BackupServer, @BackupDB
      -- Get the proper server name
      EXEC @ret = sp_GetRemoteServerName @ServerName = @BackupServer, @DatabaseName = @BackupDB, @RemoteServerName = @RealServerName OUTPUT

      /* Create the delete statement */
      select @tsql =
      'DELETE FROM [' + @RealServerName + '].[' + @BackupDB + '].[dbo].[MarkLog]
      WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),''_'',''''), GETDATE()) > ' + cast(@DaysToKeep as nvarchar(5) )
      /* Execute the delete statement */
      exec (@tsql)
      /* Get the next DB. */
      fetch next from BackupDB_Cursor into @BackupServer, @BackupDB
close BackupDB_Cursor
deallocate BackupDB_Cursor

Then I spent some days testing and improving a little more the script, and again by looking to the same SP: “sp_MarkBTSLogs”, I was able to implemented some kind of error handling, just in case of something fails that an exception will be raised, as you can see in this example picture bellow:


Special thanks for Mikael Sand for the feedback and for this new implementation logic, and of course to the previous persons involved: Tord Glad Nordahl, Rui Romano and Pedro Sousa.

You can download the last version of the custom sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure from:

BizTalk Server: Cleaning MarkLog Tables According to Some of the Best Practices (1.2 KB)
Microsoft | TechNet Gallery

All the BizTalk database which is being backed up by the ‘Backup BizTalk Server’ job, so all databases with the exception of the BAM Star Schema database (BAMStarSchema), has one table called “MarkLog”.

These tables are holding all the transaction marks (they are actually timestamps in a string format), set to a specific database, created by the 3th step (MarkAndBackUpLog) of the ‘Backup BizTalk Server’ job. This step, MarkAndBackupLog, is responsible for marking the logs for backup, and then backing them up. So each time this step runs, by default each 15 minutes, a string is stored on that table with the following naming convention:

  • Log_<yyyy>_<MM>_<dd>_<HH>_<mm>_<ss>_<fff>


  • Log”: Constant string
  • yyyy: The year as a four-digit number.
  • MM: The month, from 01 through 12
  • dd: The day of the month, from 01 through 31.
  • HH: The hour, using a 24-hour clock from 00 to 23.
  • mm: The minute, from 00 through 59.
  • ss: The second, from 00 through 59.
  • fff: The milliseconds in a date and time value.

Again this marks are timestamps in which the Log backups were set to be made. Example:

  • BTS_2014_05_06_14_15_01_327

Unfortunately BizTalk has no out-of-the-box possibilities to clean up these tables. And the normal procedure is to run the terminator tool to clean it up. See also: Clean up the MarkLog table with Terminator


BizTalk Terminator is an excellent tool that allows for common BizTalk problems to be addressed by scripts provided by the BizTalk Team, but needs to be used carefully and by users who know what they are doing.

Although most of the times this is extremely useful tool and our friend, in this particular operation/situation using this tool it’s not really the best option for two reasons:

  • Using this tool means that we need to stop our environment, i.e., downtime in our integration platform.
  • And if we look at the description of this “PURGE Marklog table” task it says that this operation calls a SQL script that cleans up everything in Marklog table – and maybe this is not a best practices!

I spoke to my dear friend, and one of the best BizTalk Administrator that I know, Tord Glad Nordahl to discuss and know what should be the best practice to maintain these tables:

  • Is these information (timestamps) useful for BizTalk Administrators?
  • Should I clean all the data inside this tables or should I maintain a history?
  • Is safe to clean this information in runtime?

Were some of the questions that we discussed.

Is these information (timestamps) useful for BizTalk Administrators? Should I clean all the data inside this tables or should I maintain a history?

For the same reason that we maintain a Backup history in the Adm_BackupHistory table controlled by the step “Clear Backup History” of the ‘Backup BizTalk Server’ job. This information is important for example to keep an eye on the backup/log shipping history records to see whether the back is working correctly and data/logs are restored correctly in the stand by environment. The information on the MarkLog tables are also useful for the BizTalk Administration team!

So as long as the MarkLog tables have the same info (data from the same dates) as the backup job days to keep you can safely delete the rest of the information.

As a best practices: you should respect the @DaysToKeep parameter that you specify in the “Clear Backup History” step of the ‘Backup BizTalk Server’ job.

And this is why that in my opinion, you shouldn’t use the Terminator tool to perform this operation!

Is safe to clean this information in runtime?

The rows in the Marklog table are not “required” and can be cleaned whenever you want as long the BizTalk Backup Job is not running.

Cleaning MarkLog Tables According to Some of the Best Practices

So the main challenger is how can we safely delete and maintain a history of all MarkLog tables according to some of the best practices described earlier?

My first approach was creating a new job that according to a scheduler would run a stored procedure to delete all the unnecessary information on that tables but I realized I could have two problems with this approach:

  • I need to be sure that BizTalk Backup Job wasn’t running performing the backups;
  • And I didn’t want to have to places to define the @DaysToKeep and I didn’t want to make unnecessary joins or additional selects

I was trying to find an approach for this operation to be carried out in an easy way and as fast as possible.

So I end up recreating sp_DeleteBackupHistory (that is configured is the Backup BizTalk Server (BizTalkMgmtDb) job in the last step) with a different name sp_DeleteBackupHistoryAndMarkLogsHistory and configure the job to run this step:

CREATE PROCEDURE [dbo].[sp_DeleteBackupHistoryAndMarkLogsHistory] @DaysToKeep smallint = null, @UseLocalTime bit = 0
	set nocount on
	IF @DaysToKeep IS NULL OR @DaysToKeep <= 0
		Only delete full sets
		If a set spans a day such that some items fall into the deleted group and the other don't don't delete the set

		Delete history only if history of full Backup exists at a later point of time
		why: history of full backup is used in sp_BackupAllFull_Schedule to check if full backup of databases is required or not.
		If history of full backup is not present, job will take a full backup irrespective of other options (frequency, Backup hour)

	declare @PurgeDateTime datetime
	if (@UseLocalTime = 0)
		set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETUTCDATE())
		set @PurgeDateTime = DATEADD(dd, -@DaysToKeep, GETDATE())

	DELETE [dbo].[adm_BackupHistory]
	FROM [dbo].[adm_BackupHistory] [h1]
	WHERE 	[BackupDateTime] < @PurgeDateTime
	AND	[BackupSetId] NOT IN ( SELECT [BackupSetId] FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] = [h1].[BackupSetId] AND [h2].[BackupDateTime] >= @PurgeDateTime)
	AND EXISTS( SELECT TOP 1 1 FROM [dbo].[adm_BackupHistory] [h2] WHERE [h2].[BackupSetId] > [h1].[BackupSetId] AND [h2].[BackupType] = 'db')

	/****** Delete MarkLog History from BAMAlertsApplication database ******/
	DELETE FROM [BAMAlertsApplication].[dbo].[MarkLog]
    WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BAMArchive database* *****/
	DELETE FROM [BAMArchive].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BAMPrimaryImport database ******/
	DELETE FROM [BAMPrimaryImport].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkDTADb database ******/
	DELETE FROM [BizTalkDTADb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkMgmtDb database ******/
	DELETE FROM [BizTalkMgmtDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkMsgBoxDb database ******/
	DELETE FROM [BizTalkMsgBoxDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from BizTalkRuleEngineDb database ******/
	DELETE FROM [BizTalkRuleEngineDb].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

	/****** Delete MarkLog History from SSODB database ******/
	DELETE FROM [SSODB].[dbo].[MarkLog]
	WHERE DATEDIFF(day, REPLACE(SUBSTRING([MarkName],5,10),'_',''), GETDATE()) > @DaysToKeep

Steps required to install/configure

  • Create the sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure described earlier
  • You need to adjust the SQL Script to your environment because some environment may not have all features installed, for example: Business Rules and in this case BizTalkRuleEngineDb database will not exist.
  • Change and configure the 4th step of the ‘Backup BizTalk Server’ job – “Clear Backup History” to call this new stored procedure


Note: Do not change or delete the “sp_DeleteBackupHistory”!

Special thanks for Tord Glad Nordahl for reviewing and all the feedback, and to my friends at DevScope Rui Romano and Pedro Sousa for the helping me developing this SQL Query.

Hope you find it useful

You can download the custom sp_DeleteBackupHistoryAndMarkLogsHistory stored procedure from:

BizTalk Server: Cleaning MarkLog Tables According to Some of the Best Practices (1.2 KB)
Microsoft | TechNet Gallery


BizTalk Server databases and their health are very important for a successful BizTalk Server database messaging environment. BizTalk is shipped out with a total of 13 SQL Agent jobs that perform important functions to keep your servers operational and healthy.

Like any other system, all BizTalk Server databases should be backed up and BizTalk Server will provide out-of-the-box a job for accomplished that: Backup BizTalk Server (BizTalkMgmtDb) job.

This job makes both Full and Log backups. By default the Backup BizTalk Server job performs a full backup once a day and performs log backups every 15 minutes. This means that once the full backup is performed you need to wait 24 hours for it to automatically do another full backup of the BizTalk Server databases… even if you try to manually run the job, it will only make the backups of the log files.

But sometimes we need, for several reasons, to have the ability and the possibility to force a full backup:

  • We will have some maintaining plan on the server, or apply a new configuration, and we want to backup the environment
  • Or simple we will install a new integration application and again we want to have a backup in this exact moment

Each company have is policies, so again for several reasons, we sometimes need to force a full backup of all BizTalk Server databases.

The standard way is to use the “BizTalkMgmtDb.dbo.sp_ForceFullBackup” stored procedure. However, and unlike what many people think, this stored procedure does not perform a full backup it only marks internally on BizTalk Server databases that the next time the Backup BizTalk Server job runs it will need to perform a full backup of the data and log files.

All BizTalk Admins know this (if they don’t know, they should!), however sometimes we need to really on DBA Admins to perform this task, and do not misunderstand me, I have nothing against DBA! All the BizTalk Administration team should be composed by several element with different skills: DBA Admins, network admins, Sys Admins and a BizTalk Admins… each playing its role. But sometime DBA don’t really understand the BizTalk infrastructure and BizTalk Administrator should have an important role advising and helping them (like in other situations it will be the opposite).

And because this is not a day by day task people tend to forget all the steps and sometimes they will create custom scripts to perform this task, but you really need to be aware of two important things:

  • The Backup BizTalk Server job is the only supported method for backing up the BizTalk Server databases. Use of SQL Server jobs to back up the BizTalk Server databases in a production environment is not supported.
  • You can use the SQL Server methods to backup the BizTalk Server databases only if the SQL Server service is stopped and if all BizTalk Server processes are stopped.

So to help one of my DBA teams I end up creating this job that is composed by two steps:

  • Step 1: Force Full Backup
    • That will call the “BizTalkMgmtDb.dbo.sp_ForceFullBackup” stored procedure
  • Step 2: Backup BizTalk Server
    • That will call the standard Backup BizTalk Server (BizTalkMgmtDb) job


Note: Of course this custom job should be disabled and manually executed when you need it!

Hope you find it useful

You can download the custom “Force Full Backup BizTalk Server (BizTalkMgmtDb)” Job from:

Force Full Backup BizTalk Server (BizTalkMgmtDb) Job (863 bytes)
Microsoft | TechNet Gallery


2013 was a big year for us in term of events: BizTalk Summit 2013 London, Oporto BizTalk Innovation Day, Bouvet Innovation Day and finally BizTalk Innovation Day Italy… but we promise that 2014 would start even bigger and better!

Nearly 200 attendees (an increase of 56% compared with the previous year), more than 100 different companies across 20 countries were present in this event. 12 Microsoft Integration MVP’s from across the world (USA, Canada, India, Netherlands, Norway, Portugal, Italy, Belgium and of course UK) and 2 members of Microsoft Product group were present as a speakers! These are some of the numbers that reflect the huge success of this event!


And viewing all the comments (Day 1 and Day 2 summary from tweets, what did the attendees say)… we did it! Of course this was not possible without all the effort and commitment of Saravana Kumar and all his BizTalk360 team (~6 months on this project) that make this a remarkable event!

The journey

As almost of the speakers and many attendees, I arrived on Saturday, March 1, thereby enjoying the weekend to meet London city a little more and chat with my fellows MVPs and members of the BizTalk Community.


The event

My session was on the second day of the event in the afternoon. And was all about BizTalk Mapping Patterns where I tried to address and explain some common mapper problems, specifying best practices and some of the best ways to address common problems by choosing the right approach and a small introduction to WABS maps where I address the key differences between BizTalk Server and BizTalk Services maps, key problems in migrating BizTalk Server to BizTalk Services maps and some WABS maps samples.


Unfortunately, because time restrictions, I had to be very brief showing only key concepts and not getting deep on many of the topics. But in overall I was pleased with the end result and all the people, and people can find out more details on this topics when my eBook become available.

But let’s go back to the beginning…

Day 1

The first day started at 7am with the registrations progress which occurred very smoothly. And after a quick kick off at 8:45 by the hand of Saravana Kumar the Microsoft Product Group took the stage, first by Guru Venkataraman that start his presentation by saying “On-prem is real and here to stay!” and also explaining the BizTalk Server roadmap and the new version: BizTalk Server 2013 R2 that they are preparing and the Windows Azure BizTalk Services roadmap.

Being succeeded by Harish Kumar Agarwal that explain in detail all the Windows Azure BizTalk Services features updates that went into the February release.


After the Microsoft Product Group was time for the Microsoft integration MVPs to take the stage for them, two more sessions were carried out in the morning:

  • Jon Fancey presented a session about how you can move to BizTalk Services, explaining the advantages and challengers you can expect in moving to the cloud
  • And Saravana Kumar cover in his session some of the key capabilities of BizTalk360 and demonstrated how to improve your operations (monitoring and management) in your BizTalk environment using BizTalk360.


The afternoon started with a monster on the stage that don’t need any introduction: Richard Seroter that gave us an overview of the existing integration tool and how to choose the right one for the right scenario. However Richard was not alone and was followed by two great session:

  • Tord Glad Nordahl with an excellent and kind of provocative (in a good sense and intentional) session about How we/you can mess up the BizTalk environments! Proving excellent tips and tricks not only for admin but also to developers in what you can do and should avoid doing.
  • And Stephen W. Thomas that gave us a look at Windows Azure IaaS and how best to leverage it with BizTalk Server 2013. Providing also awesome PowerShell scripts to configure and manager your VM in the cloud.

BizTalk-Summit-2014-Richard-SeroterBizTalk-Summit-2014-Tord-NordahlBizTalk-Summit-2014- Stephen-Thomas

And finally the day 1 finish with a Q&A session with Microsoft Product Group and a couple of MVP’s, special reference to the presence of Michael Stephenson on this session:


After that was time to talk with the attendees, some social network with some beers and a speaker dinner’s at Browns.


Day 2

The second day started at 9am with four amazing sessions carried out in the morning:

  • The first one that took the stage was Kent Weare that give us a look to the mobile services, presenting a real world business scenario using many of Windows Azure building blocks like Azure Service Bus and Azure Mobile Services; and BizTalk Server 2013
  • Steef-Jan Wiggers was the second, and as always he made a great session about Manageability of Windows Azure BizTalk Services were he focused on the tools that are available for managing WABS solutions.
  • Is not every day that you can see a session about BAM, and I personally like this topic, and Dan Rosanova was the person responsible for this topic! And what a great job. It was one of my favorite’s talks/topics! He was able to walk through and show us real world BAM implementations covering all the key concept of BAM.
  • The last in the morning was Johan Hedberg, who lost a bet and had to wear a Canada shirt during his presentation, but he was not intimidated and made a good session on how to use BizTalk Server together with SQL Server Master Data Services.


The afternoon started, in my personal opinion, with the best session of the event and with an “animal” on the stage: Nino Crudele, in addition of captivate and put the whole audience laughing, Nino was able to present an awesome add in to Visual Studio “btsgnosaddin” that will make the lives of all BizTalk developers much better and easy! This will be a tool that all BizTalk developers must have!

This session was followed by two more sessions:

  • Sandro Pereira (me) who better than me to succeed my good friend Nino Smile.
  • And the last one to take the stage was Sam Vanhoutte that gave us a session about Running durable workflows in the cloud and on prem. Providing an overview of Workflow Manager and showing awesome hybrid demo applications using Twilio.


Once again, for me it was excellent and an honor to participate in this event along with all these amazing people that were responsible for this happens: the speakers and the BizTalk Crew, Microsoft, BizTalk360 crew members and special all the attendees!

After the event

After the event was time to relax and enjoy the last night in London in the company of friends and fellow BizTalk Community members.


Stay tuned on the BizTalk Innovation Day Facebook page to learn more about our upcoming events!

Key point/resources to take into considerations
  1. btsgnosaddin” addin to Visual Studio that will be provided by Microsoft Integration MVP Nino Crudele for free!
  2. PowerShell scripts to configure and manager your VM in the cloud that will be provided by Microsoft Integration MVP Stephen W. Thomas for free!
  3. BizTalk Mapping Patterns and Best Practices eBook that will be provided by Microsoft Integration MVP Sandro Pereira (me) for free!
  4. All the session will be available online soon in the BizTalk360 webpage

Unlike schemas that you will be able to directly take your BizTalk Server schemas and use them in you WABS solution, BizTalk Maps needs to be migrated because despite the Transform Designer (mapping tool used for WABS) and the BizTalk Mapper Designer looks mostly the same in terms of layout and user experience, they are quite different and the underlying map format is different. Basically when we are using BizTalk Mapper Designer the transformation are created using XSLT, however with Transform Designer the transformation are created using Extensible Application Markup Language (XAML).

Also the available functoids, that are now called Operations, are different as well. Not all the functoids available in BizTalk Server are available as map operations in Azure BizTalk Services and there are new Operations that doesn’t exist in BizTalk Server like: several List Operations, DateTimeReformat, GetContextProperty and so on. And despise XSLT and custom C# are supported the concept of “Scripting” map operation in Azure BizTalk Services Transforms are limited comparing to BizTalk Service for example:

  • You cannot use custom inline XSLT, you only can custom XSLT code directly to override WABS map, similar to the “Custom XSLT Path” map grid properties in BizTalk Server.
  • You can only use CSharp Scripting inside WABS maps.

Because of all this reasons you cannot directly use a BizTalk map in Azure BizTalk Services.

Basically you have two options that you can use to migrate BizTalk maps:

  • By using custom XSLT code gathered from your BizTalk Maps (…the easy way)
  • Or by using the BizTalk Map Migration Tool provided by Microsoft.

Using BizTalk Map Migration Tool

This is a command line tool, provided by Microsoft that takes a BTM File as input and that will try to produces the new mapper (.trfm) file as an output.

I’m saying “that will try” because there are many limitations present like:

  • Certain functoids may not get converted by this tool, for example some Scripting and Custom functoids from BTM way cannot be converted. In this case they are converted to arithmetic expression functoid in the TRFM map with the expression value as empty.
  • Links to functoids, which cannot be converted successfully, are denoted as empty inputs in the new TRFM functoids.
  • Certain features that are not supported, yet, in TRFM, will not be converted properly. For example, links from xs:extension node(s) are not converted to TRFM
  • Certain conditional mapping are not converted as expected as these features are not yet implemented in the Transforms
  • And so on.

At the end this tool will generate a conversion process report (Report.txt) listing out the failed conversions.

Also in terms of Organizing and Documenting Maps there are two drawbacks presents:

  • Labels and comments from the BTM functoids are not mapped onto the TRFM functoids
  • The multiple pages from BTM are translated to a single page in the TRFM map to ensure all operations within a scope are within a page.

The BizTalk Map Migration Tool is available on Codeplex at BTM Migration Tool, but before you use this tool you need to know that:

  • This tool expects that the machine on which it’ll run would have both products installed: BizTalk 2010 or above and Windows Azure Biztalk Services.
  • And the schemas should be present as expected by the BTM map (in the relative folders).

The BTM Migration Tool is a command line tool that uses the following parameters:

  • A BizTalk map (.btm)
  • Optional. Resulting BizTalk Service project Transform (.trfm)


Let’s look at one very easy example and analyze the end result. In this scenario we have two different records, Client and Employee, and we want to map all the man’s clients and employees to Person (Sex == M). This is look of the BizTalk Server maps:


When we apply the BTM Migration Tool it will generate this nice WABS map for us:


And the good news in this case, if we try the map it will successfully work and it will produce the tight output! (Yes I looked for a good sample). However is this the perfect output?

So let’s try change this to get only the female sex present in the input message. It seems an extremely easy change to make… we just need to go to the Logical Operation present in each MapEach Loop Operation and change the condition


However surprisingly the output instance after this change is empty


So why this happens? In this case, after looking to all the shapes, I found out that the MapEach Loop operation also as the same condition that is present in the Logical Operation that we need to change, as you can see in the picture bellow:


Can this map be improved?

The aim here is not to explain the functionalities of the new map and how the new operations works, however if you look at the map generated by the compiler, at least in this case, you can easily identify that we can simplify the map to be more easy to read and to maintain, by:

  • Maintaining the condition in the MapEach Loop operation
  • Suppress all the Logical and Conditional Assignment Operations that are inside the MapEach Loop operation
  • And use direct links to map the Name and Sex



So as a final note, even if the migrator tool did a decent job, you should always try to identify some bottlenecks, problems or overlaps and simplify it

Using Custom XSLT Code

Migrating maps from BizTalk Server solutions to Azure BizTalk Services could be one of the more complex artifacts to migrate (depending on map complexity)… or not!

The good news is that, you don’t necessary have to remake or migrate our BizTalk map to WABS map. As I mention earlier WABS maps support custom XSLT code, you can use a custom external XSLT file, similar to the “Custom XSLT Path” map grid properties in BizTalk Server to override WABS map and use custom XSLT to make the entire map transformation.

I try to avoid using Custom XSLT files for solving a mapping problem, for many reasons, however there are always exception. I do recommend using custom XSLT files:

  • If you are dealing with huge message and High Performance is one of the primary requirements (and even then this maybe can be avoid)
  • Or if you already have a custom XSLT file provided by another team, or from another system that you can re-use in your map. In this case don’t try to reinvent the wheel!

And because we are migrating a BizTalk Server map we already have the XSLT, so for me there is no point spending a few and in some case many hours trying to remake the same transformation in a different editor.

To extend your WABS maps to use custom XSLT code you must:

  • Go to the grid properties (or also call Transform design surface properties) in the Transform Designer, where you can see that there is a XSLT property in which we can extend the map to use custom XSLT code


Note: This time Microsoft did a nice work and if you notice in the grid page there is message that will help you.


  • In Properties, click XSLT, and click the ellipses (…). A Configure XSLT dialog window is opened.


  • To import an existing XSLT file, click Import XSLT. Browse to the .xslt or .xsl file and click Open. The imported XSLT populates the Configure XSLT dialog window. OR, enter the XSLT syntax in the Specify the XSLT below … window.


  • Optional. To import an existing XML extension file, click Import EXTXML. Browse to the XML file and click Open. The imported XML populates the Specify the extension objects … dialog window. OR, enter the XML syntax in the Specify the extension objects … window.
  • Optional. Click Use XslCompiledTransform for better performance to transform XML data by compiling XSLT style sheets and executing XSLT Transforms. When the style sheet is compiled, it can be cached and reused. Otherwise, the XslTransform class is used; which is best when executing a Transform on one occurrence.
  • Click OK.

When XSLT is used, the Transform design surface is grayed out. XSLT can be specified for every Transform file (.trfm).


How do I generate XSL output from the BizTalk Mapper Designer?

To generate a XSLT file that represent you BizTalk Map you just need to:

  • Open the BizTalk map in Visual studio
  • Right click on the map and select “Validate Map” option, this operation will generate the XSLT and you can see the path of generated XSLT in output window.

You can use this XSLT, without change it in most of the cases, to add in your WABS Maps.

Code Samples

All of this sample can be found and downloaded in Microsoft Code Gallery:

For the third consecutive year, BizTalk Innovation Day event will be conducted in several European cities. And like last year the tour will start with our major event that is back to London even bigger and better!

  • 12 Integration MVP’s from across the world (USA, Canada, India, Netherlands, Norway, Portugal, Italy, Belgium and of course UK) will be speaking in this event.
    • The BizTalk Crew
      • Nino Crudele [Italy]
      • Saravana Kumar [UK] – the event host
      • Steef-Jan Wiggers [Netherlands]
      • Tord Glad Nordahl [Norway]
      • And me [Portugal]
    • Dan Rosanova [USA]
    • Jon Fancey [UK]
    • Michael Stephenson [UK]
    • Richard Seroter [USA]
    • Sam Vanhoutte [Belgium]
    • Stephen W. Thomas [USA]
    • Kent Weare [Canada]
  • Once again Microsoft Product group will be speaking in this event and will bring some of the core content presented in “BizTalk Integration Summit” held in Seattle last year or even new stuff to European audience.
  • Approximately 220 attendees expected from all over the Europe (or why not all over the world). Which means that we can interact and exchange ideas, not only with MVP and the product team, but also with two hundred BizTalk Experts!!!

BizTalk Summit 2014, London event will be organized by BizTalk360 (Kovai) limited with co-operation from Microsoft Product group, Microsoft UK and Microsoft Integration MVP’s.


This will be a two day event purely focused to the Microsoft Integration stack: BizTalk Server, Windows Azure BizTalk Services, Service Bus and Mobile Services. So we invite you all to join us next 3rh March 2014 in London, England. The event will once again take place in Microsoft (Cardinal Place), 100 Victoria St, SW1E 5JL London.

Programar-edicao-37The event only accommodate a limited number of attendees due to logistical constraints and is almost sold out! However there are still a few tickets available. So if you are interested please go ahead and confirm your seat as soon as possible, this will easily be the biggest Microsoft Integration event in Europe this year with a lot of new content.

You can find all detail about this event here.

About my session…

Last year I had the opportunity to introduction Windows Azure BizTalk Services (at the time Azure Service Bus EAI/EDI Labs), this year I will address a topic that I especially love and that I have presented in previous BizTalk Innovation Day events, however slightly different from the previous ones: “BizTalk Mapping Patterns and an Introduction to WABS maps“- this is mainly a session focused for developers. Here is the session’s abstract:

Maps or transformations are one of the most common components in the integration processes. They act as essential translators in the decoupling between the different systems to connect.
In the last couple of month I have been writing a free eBook about “BizTalk Mapping Patterns and Best Practices” that will soon be released, and this will be a good opportunity for you to see what you can expect. In this session I will try to address and explain some common mapper problems, specifying best practices and some of the best ways to address common problems by choosing the right approach. For me there is no perfect solution to solve a particular mapping problem, and most of the times we can find several ways to solve it depending on the experience and knowledge that we have or technologies and tools that we like to use, but all of them have advantages and disadvantages. Not always the best solution is the best approach to address the problem, you must understanding the problem and the requirements, researching the available options and taking actions to achieve your goals sometimes base on:

  • What’s the best approach to have performance? Is my message so big that I really need to worry about performance? Do I really need High performance?
  • Can I easily maintain this map? It is easy to read and understand? What the estimated effort for debug, finding and fix problems?
  • What the expertise levels required to create and maintain this map approach? What much time do I need to develop?

Finally I will try to make an introduction to the Windows Azure BizTalk Service maps, at the first glance the map editor seems to be the same that we used in BizTalk, but although the concept be the same this is a very different editor. And hopefully you will enhance your skills when using BizTalk Server and WABS Mapper editor.

Let’s see a glimpse for what you can expect:

BizTalk-Mapping-Patterns-and-Best-Practices-eBook Aggregator-Pattern

Working-with-Name-Value-Pair-Structures WABS-Maps

Last year was like this….

128 attendees, more than 70 different companies across 16 countries (Austria, Belgium, Denmark, France, Germany, Italy, India, Ireland, Netherlands, Norway, Portugal, Spain, Sweden, Switzerland, UK, USA), more than 10 Microsoft Integration MVP’s present (speakers, Q&A members and attendees) and 3 members of Microsoft Product group, these are some of the numbers that reflect the huge success of this event! This was the first time in UK that was been conduct a full day event dedicated to Microsoft BizTalk Server.


Check more here and in our Facebook page.

When we configure Deployment Settings in Visual Studio projects, our database settings (“Configuration Database” and “Server”) and BizTalk Settings (“Application Name”) are only stored in a personal “*.btproj.user” file. By default these files are ignored in your check-ins, but that may not be the desired effect. We recommend that those settings are also stored in your source control (forever) to preserve testing configurations and help you and other team members pick your archived project easily.

In one of my previous post, “Failed to add resource(s). Resource (…) is already in store and is either associated with another application or with another type”, I explained a common deployment problem that can happen when we work with BizTalk Server project and Team Foundation Server. In this post I will try to explain how can we avoid this problem and how can we store deployment settings in TFS so that can be shared by our team members.


Firstly, is important for you to know that Visual Studio, I believe since BizTalk 2009 onwards, stores the deployment setting of BizTalk projects in the Project user option file: “*.btproj.user” file. This is a XML file that contains not only the BizTalk deployment Settings but also several personal user settings like References path, test file names and so on.

Regarding to BizTalk deployment properties all this setting are stored in the “*.btproj.user” file:

  • Application Name (ApplicationName): Is the name of the BizTalk application that we want to deploy the assemblies in this project. If the application already exists, the assemblies will be added to it when you deploy the project. If the application does not exist, the application will be created. If this field is blank, the assemblies will be deployed to the default BizTalk application in the current group (“BizTalk Application 1”). Names that include spaces must be enclosed by double quotation marks (“).
  • Configuration Database (ConfigurationDatabase): Is the name of the BizTalk Management database for the group, the default value is “BizTalkMgmtDb”.
  • Server (Server): Is the name of the SQL Server instance that hosts the BizTalk Management database on the local computer. By default this is usually the name of the local computer.
  • Redeploy (Redeploy): Boolean property that indicates if you want to allow redeployments from within Visual Studio. Setting this to “True” (the default) enables you to redeploy the BizTalk assemblies without changing the version number.
  • Install to Global Assembly Cache (Register): Setting this to “True” (the default) installs the assemblies to the Global Assembly Cache (GAC) on the local computer when you install the application. Set this to False only if you plan to use other tools for this installation, such as gacutil.
  • Restart Host Instances (RestartHostInstance): Setting this to “True” automatically restart all host instances running on the local computer when the assembly is redeployed. If set to False (the default), you must manually restart the host instances when you redeploy an assembly.

Let’s see an example of this file:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="">
  <PropertyGroup Condition="'$(Configuration)|$(Platform)' == 'Debug|AnyCPU'">
      <FlavorProperties GUID="{EF7E3281-CD33-11D4-8326-00C04FA0CE8D}">
		<File Path="NormalizeAndRemoveUnnecessaryData\Client.xsd">

Secondly, you must keep in mind that these file (“*.btproj.user”) are typically not under source control. The reason is that the Visual Studio deployment feature main focus is for development scenarios where one user settings might differ from another. And it will be up to you, or your client, to decide if you want these files to be checked in and available to all of the developers on your team.

However when the team shares the same BizTalk developer environment, is expected that all build related information required at the time of build can be shared between developers in an easy way… unfortunately there is no easy way and one of the possible solutions is to force storing “*.btproj.user” files in the source control so that this setting can be shared by all the team members.

Although I know that the “*.btproj.user” Files are not supposed to be shared because they contain personal settings (as explained before) sometimes this approach can be considered a good practice.

Of course that this “workaround” can lead to additional problems, simple because this files contains some user settings, that can be different that yours (for example schema file properties only allows a valid full path file names) that can change your setting and probably don’t work in your developer station and need to be reconfigured, but this can be minimized with some precautions and good practices.

How can we check if “*.btproj.user” Files are stored in TFS and how to add them?
  • Open Visual Studio 2010 and select the “View” Menu and choose “Team Explorer” to connect to your TFS
  • Access to “Source Control” and from the source control tree you must browse to the project that you want.


  • Right click in the project folder and select “Add Items to Folder…” option


  • Select the BizTalk project folders present in the solution, and click “Next”


  • From this windows we can see that the “*.btproj.user” files are not include in the source code


  • To add them, you just need to select them and then click “Finish”.
Testing this workaround

After applying this wokaround, when I try to access and get the last version of the project with a different user, all the settings where correctly properly fulfilled:


And after a battery of tests that I performed very quickly with a member of my team, here are some suggestions to avoid some problems that sometimes happens and we face in our tests:

  1. Make a Label in the “*.btproj.user” file to properly and easily identify a clean BizTalk Application Deployment Settings with all important deployment settings and without any personal settings. This way you can, at any time, force to get that specific version of the file


  1. If you already have a local version of the project in your environment, you should get the last version and force to overwrite all the files by:
    1. Access to source code, in the project folder, right click and choose the option “Get Specific Version…”
    2. Check all the option in the Get Windows:
      1. Project folder
      2. “Overwrite writeable files that are not checked out” option
      3. “Overwrite all files even if the local version matches the specified version” option
      4. Choose the type “Latest Version” and click “Get”


In addition, once you have the correct settings, even if each individual users change locally their personal settings they are not committed to the source control by default, which means that it will not spoil the settings of other users every time someone does a check in. Again if you want to change this setting in the source control you need to force this to happen.

Final notes

Probably this is not the best approach for sharing the BizTalk Application Deployment Settings across all the team, but is a valid way and much better than each member manually add to their solution. I will try to find a better way and if you already have it, feel free to comment and share it here.


Once again I want to thank José António Silva, my ALM guru for the help! And thanks Bruno Zacarias for helping me testing this workaround.