BizTalk DW Reporting???

29 March 2012

Last week we encountered this mysterious BizTalk error in the event log:

Faulting application btsntsvc.exe, version 3.6.1404.0, stamp 4674b0a4, faulting module kernel32.dll, version 5.2.3790.4480, stamp 49c51f0a, debug? 0, fault address 0x0000bef7.

The system log contains entries every minute saying  the BizTalkServerSend  service  terminated unexpectedly.

It happened at all of our customers after updating one of our BizTalk based applications to a new version. The only clue I could find was this support article from Microsoft that describes the same scenario. However in our case no dynamic Windows SharePoint Services (WSS) send port is used. But we do use static WSS send ports. After examining I found out that the binding of one of them was corrupted!

The update of our BizTalk based solution is automated, because it has to be applied at a lot of customers. Part of the process is modification of the specific binding file, by altering the URL to the specific SharePoint document library of the customer. It is located in a string encoded XML within the <TransportTypeData> element. Something went wrong when altering the encoded xml and corrupted the content of the <TransportTypeData> element.


Unit testing with SQLExpress

28 October 2011

When creating unit tests for your data access code you should also have a database with well know data that should not change. Recently I needed one for a unit test project of a resource access assembly using Entity Framework 4.1. The tests should work for every developer and also on a build server. During my study for the MCTS 70-516 exam (Accessing Data with .NET 4) I found out that you can use SQL Server Express to connect to a MDF file by using “AttachDbFilename” in the connection string. So my college made a copy of the MDF file from his development SQL Server and placed it as content file in the test project in Visual Studio. But it took some trial and error to get it working correctly.

First make sure that “Enable deployment” is checked in the Deployment page of the Local test settings, so the deployment items of the test project will be copied to the “Out” directory of the test run (see: How to: Configure Test Deployment). In the connection string “|DataDirectory|” can be used like: “AttachDbFilename=|DataDirectory|\ EFDatabase.mdf”. It will be automatically replaced to the “Out” directory of the current test run by the unit testing framework of Visual Studio.

Then use the DeploymentItem attribute, with the name of your MDF file, above the test methods that needs the database. This will give every test run a fresh copy of the database.

Do not use “Initial Catalog” or “Database” in the connection string. This causes a SqlException like “Database ‘C:\…\Out\EFDatabase.mdf’ already exists. Choose a different database name.” at the second test run. Omitting “Initial Catalog” or “Database” lets SQL Server attach the file with a generated unique database name. However every test run will attach a new copy of the database to your SQL Server instance and you have to detach them manually.

With some custom code (calling the “sp_detach_db” stored procedure from the master database) in the ClassCleanup method I managed to detach the database automatically. But there is a far more convenient solution by using SQL Server Express Edition User Instances! It can be activated with “User Instance=True” in the connection string:

<add name="EFContext" providerName="System.Data.SqlClient" connectionString=
User Instance=True" />

A user instance is similar to a normal instance but it is created on demand while normal instances are created during setup. The service account for a user instance is the Windows user who opened the SQL Client connection to the database. When a connection with this connection string opens successfully, the user application is connected to a user instance of SQL Server Express running as the user who opened the connection. Because the account of the user is the service account for the instance, it has full administrator rights to the attached database. So there is no need for entering users into the test database and setting their memberships! The unit test will work for every developer and build server in the project!

The sqlservr.exe process that is started is kept running for a while after the last connection to the instance is closed. After this period it automatically shuts down and all databases are

Empty string or not!

15 October 2011

One of the most used methods of the .Net framework I think is String.IsNullOrEmpty(), often used like:

if (String.IsNullOrEmpty(value))


if (!String.IsNullOrEmpty(value))

A few years ago someone notified me that the ‘!’ operator is not very noticeable and therefore its more clear to read it like this:

if (String.IsNullOrEmpty(value) == false)

Code should be self-documenting and readable as a story, because there is a good chance that other developers have to maintain it later! These days a .Net developer is blessed with extension methods. One of the great benefits of extension methods is that they can be applied to null-references without crashing your application! So with the string extensions below you can write the previous statements like:

if (value.IsNullOrEmpty())


if (value.IsNotNullOrEmpty())
public static class StringExtensions
  public static bool IsNullOrEmpty(this string value)
    return String.IsNullOrEmpty(value);

  public static bool IsNotNullOrEmpty(this string value)
    return String.IsNullOrEmpty(value) == false;

DateTime extensions for holyday and businessday calculations

1 September 2011

Extension methods are a very powerful feature of .Net. Here is an example of DateTime extensions to determine the holydays of a certain year. It also provides an algorithm to add workingdays to a DateTime object. The Easter calculation method was found here.

public static class DateTimeExtensions
	/// <summary>
	/// Telt werkbare dagen op bij een <see cref="DateTime"/>.
	/// </summary>
	/// <param name="date">De datum.</param>
	/// <param name="days">Het aantal werkdagen.</param>
	/// <param name="extraHolidays">De extra niet werkbare dagen.</param>
	/// <returns>Een <see cref="DateTime"/> waarvan de waarde een som is van de opgegeven datum en het aantal werkbare dagen.</returns>
	/// <remarks>
	/// Standaard wordt al rekening gehouden met Nieuwjaarsdag, Pasen, Hemelvaart, Pinksteren en Kerst
	/// (<seealso cref="DateTimeExtensions.Holydays"/>). Andere niet werkbare dagen, zoals Koninginnedag en
	/// Bevrijdingsdag, kunnen hier opgegeven worden.
	/// </remarks>
	public static DateTime AddBusinessDays(this DateTime date, int days, IEnumerable<DateTime> extraHolidays = null)
		int year = date.Year;
		IEnumerable<DateTime> holydays = extraHolidays == null ? Holydays(year) : Holydays(year).Union(extraHolidays);

		int direction = days < 0 ? -1 : 1;

		date = date.Date;
		while (days != 0)
			date = date.AddDays(direction);
			if (year != date.Year)
				year = date.Year;
				holydays = extraHolidays == null ? Holydays(year) : Holydays(year).Union(extraHolidays);

			if (date.IsWeekend() == false && holydays.Contains(date) == false)
				days -= direction;

		return date;

	public static bool IsWeekend(this DateTime datetime)
		return datetime.DayOfWeek == DayOfWeek.Saturday || datetime.DayOfWeek == DayOfWeek.Sunday;

	public static bool IsNewYearsDay(this DateTime datetime)
		return datetime.Date == NewYearsDay(datetime.Year);

	// Eerste Paasdag (zondag)
	public static bool IsEaster(this DateTime datetime)
		return datetime.Date == Easter(datetime.Year);

	// Tweede Paasdag (maandag)
	public static bool IsEasterMonday(this DateTime datetime)
		return datetime.Date == EasterMonday(datetime.Year);

	// Hemelvaartsdag (donderdag)
	public static bool IsAscensionDay(this DateTime datetime)
		return datetime.Date == AscensionDay(datetime.Year);

	// Eerste Pinksterdag
	public static bool IsWhitSunday(this DateTime datetime)
		return datetime.Date == WhitSunday(datetime.Year);

	// Tweede Pinksterdag
	public static bool IsWhitMonday(this DateTime datetime)
		return datetime.Date == WhitMonday(datetime.Year);

	// Eerste Kerstdag
	public static bool IsChristmasDay(this DateTime datetime)
		return datetime.Date == ChristmasDay(datetime.Year);

	// Tweede Kerstdag
	public static bool IsBoxingDay(this DateTime datetime)
		return datetime.Date == BoxingDay(datetime.Year);

	public static DateTime NewYearsDay(int year)
		return new DateTime(year, 1, 1);

	public static DateTime Easter(int year)
		// Gauss Calculation
		int month = 3;

		// Determine the Golden number:
		int goldenNumber = year % 19 + 1;

		// Determine the century number:
		int century = year / 100 + 1;

		// Correct for the years who are not leap years:
		int leapYearCorrection = (3 * century) / 4 - 12;

		// Mooncorrection:
		int moonCorrection = (8 * century + 5) / 25 - 5;

		// Find sunday:
		int sunday = (5 * year) / 4 - leapYearCorrection - 10;

		// Determine epact (age of moon on 1 januari of that year (follows a cycle of 19 years):
		int epact = (11 * goldenNumber + 20 + moonCorrection - leapYearCorrection) % 30;

		if (epact == 24) epact++;
		if ((epact == 25) && (goldenNumber > 11)) epact++;

		// Get the full moon:
		int fullMoon = 44 - epact;
		if (fullMoon < 21) { fullMoon = fullMoon + 30; }

		// Up to sunday:
		int day = (fullMoon + 7) - ((sunday + fullMoon) % 7);

		// Easterdate:
		if ( day > 31 )
			day = day - 31;
			month = 4;

		return new DateTime(year, month, day);

	public static DateTime EasterMonday(int year)
		return Easter(year).AddDays(1);

	public static DateTime AscensionDay(int year)
		return Easter(year).AddDays(39);

	public static DateTime WhitSunday(int year)
		return Easter(year).AddDays(49);

	public static DateTime WhitMonday(int year)
		return Easter(year).AddDays(50);

	public static DateTime ChristmasDay(int year)
		return new DateTime(year, 12, 25);

	public static DateTime BoxingDay(int year)
		return ChristmasDay(year).AddDays(1);

	public static IEnumerable<DateTime> Holydays(int year)
		return new DateTime[] {

Web Service Software Factory: Beyond the Service Interface (part 1)

30 August 2011

The Web Service Software Factory still is one of my favorite .Net tools, so I was very pleased that is fully available as Visual Studio Tools extension, which makes the installation of it a piece of cake!

The overall architecture of services built using the Service Factory worked very well for the services I build in the last view years. I am a great fan of this figure illustrating the layers of a service:


However the Web Service Software Factory helps you creating the service interface layer, you are on your own when it comes to the other two.

In this post I will explain my way of implementing the business and resource access layers.

Business Entities

Every project should start with implementing the domain model, because it represents the vocabulary and key concepts of the problem domain. The entities are POCO classes (Plain Old CLR Objects) and should only contain properties and associations with other entities. Entities should not implement business logic other than validation. So methods on entity classes are only used for advanced ways of getting and setting the properties and associations that cannot be done through the getters and setters alone.

It’s wise to include one or more class diagrams in your entity project to have a complete overview on what you are doing.

Business Logic

The Web Service Software Factory documentation talks about the Transaction Script or Business Action pattern to implement the entry point into the Business Layer when called from higher layers. Just doing this can result in huge monolithic classes with a lot of methods. To prevent this I use Business Action interfaces in my projects with just one method:

public interface IBusinessAction
	Response Execute(Request request);

Where Request and Response can be business entities, for example:

public interface ICustomerSalesOrdersSelectAction
	ICollection<SalesOrder> Execute(Customer customer);

Sometimes a request can’t be just one business entity, or string, or integer (an ID for example). In that case a specific request can be added to the business logic. Not to the business entities, because it is just a query-like request, for example:

public class CustomerSalesOrdersPagedRequest
	public Customer Customer { get; set; }
	public int Skip { get; set; }
	public int Take { get; set; }

The Business Action interfaces can be implemented directly by classes from the Data Access Logic (repositories) or Service Agents. The usage of interfaces gives us the option of resolving their implementation through a dependency container (as Unity) from within the Service Interface Layer! This way the Business Logic doesn’t need to have references to Resource Access Layer components, unlike what is written in the Web Service Software Factory documentation.

To be continued…

BizTalk Generic WCF Service exceptions

9 April 2010

The BizTalk WCF adapter provides a very nice feature for implementing a Generic WCF Service that eats any SOAP message it receives. However when trying to test this with Altova XMLSpy I encountered the next exceptions:

The adapter “WCF-BasicHttp” raised an error message. Details “System.NotSupportedException: Specified method is not supported.

In this case the SOAPAction was “BizTalkSubmit” which will throw a NotSupportedException as documented. So if you generate a client for this web service you need to change the SOAPAction into something else. I think anything but “BizTalkSubmit” will do.

The adapter “WCF-BasicHttp” raised an error message. Details “System.InvalidOperationException: Text cannot be written outside the root element.

The default setting of the WCF adapter uses the “contents of <soap:Body> element” option for the inbound message body. It seems that when you have an indented XML message the filtered body starts with whitespace which causes this exception. For me it was enough to use “/*” as body path expression instead to solve it.


How to publish an XmlDocument or Microsoft.XLANGs.BaseTypes.Any message to the MessageBox with its MessageType promoted!

7 September 2009

Sometimes you would like to have a generic orchestration that can handle any type of messages that should be send to the MessageBox through a direct bound send port. Ofcourse it would be nice if the message could be routed based on its MessageType after being published. Unfortunately when publishing an XmlDocument or Any message from an orchestration the message type is not promted. Also it can’t be promoted from within the orchestration because BTS.MessageType is read-only.

A solution for this problem was on my wish list for a while and finally I found one.

It’s a combination of calling the XMLReceive pipeline inside the orchestration and assigning an initializing correlation set with the BTS.MessageType to the send shape. The XMLReceive pipeline determines the message type and the correlation set promotes it.

First ad the following references to the orchestrations project:


Within a scope with atomic transaction type create a variable of .Net type Microsoft.XLANGs.Pipeline.ReceivePipelineOutputMessages. Use an assignment shape to assign the XMLDocument or Any body part of the message and execute the XMLReceive pipeline. For example:

msgRetry.HeaderPart = varHeader;
msgRetry.BodyPart = xpath(msgFault, "/*[local-name()='FaultMessage' and namespace-uri()='']/*[local-name()='Messages' and namespace-uri()='']/*[local-name()='Message' and namespace-uri()='' ][MessageName='BodyPart']/*[local-name()='MessageData' and namespace-uri()='']/*/*");

varPipelineMessages = Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteReceivePipeline(typeof(Microsoft.BizTalk.DefaultPipelines.XMLReceive), msgRetry);


Next a correlation type is needed with BTS.MessageType as correlation property. An initializing correlation set of this type needs to be assigned to the direct bound send port.

The atomic scope is needed because the ReceivePipelineOutputMessages type is not serializable. This could be avoided by wrapping the pipeline code in an external static helper method like this:

public static void DetermineMessageType(XLANGMessage message)
	ReceivePipelineOutputMessages pipelineMessages = XLANGPipelineManager.ExecuteReceivePipeline(typeof(Microsoft.BizTalk.DefaultPipelines.XMLReceive), message);