Base64 encode/decode part of messages in map - part 2

Back in November I wrote a post about encoding messages as base64 strings in a BizTalk map. I never added the explicit implementation of how to decode the message though. However, I was asked to provide an example of it, so here is part 2: how to decode a base64 string from an XML message and output it in BizTalk.

Consider the scenario to be the opposite of what we did in part 1.

We have a message on the input side that has two fields, one with an id as a string, and another string element that holds the entire base64 encoded string.

<ns0:Root xmlns:ns0="http://BizTalk_Server_Project6.Schema1">
  <SomeId>1</SomeId>
  <EncodedData>PG5zMDpSb290IHhtbG5zOm5zMD0iaHR0cDovL0JpelRhbGtfU2VydmVyX1Byb2plY3Q2LlNjaGVtYTIiPg0KICA8RGF0YT5EYXRhXzA8L0RhdGE+IA0KICA8RGF0YTI+RGF0YTJfMDwvRGF0YTI+IA0KPC9uczA6Um9vdD4=</EncodedData>
</ns0:Root>

On the output side, we want to have a message that conforms to the schema we have defined. The message is the entire contents of the base64 encoded string in the input.

If we parse the encoded string, we get:
<ns0:Root xmlns:ns0="http://BizTalk_Server_Project6.Schema2">
  <Data>Data_0</Data>
  <Data2>Data2_0</Data2>
</ns0:Root>

Which conforms fully to the schema defined.

So, the task is to extract the contents from the EncodedData element, decode them, and use the full string as the output message. All in a single BizTalk map.

The map will look like this to begin with, with the two schemas chosen:

Similar to when we encoded, we add first a scripting functoid that has no connections, and paste the code for the decode function in it:

public string Base64DecodeData(string param1)
{
            byte[] decodedBytes = Convert.FromBase64String(param1);
            return System.Text.UTF8Encoding.UTF8.GetString(decodedBytes);
}

We simply take a string as an input parameter, decode this and return the decoded string back.

Then we create a new scripting functoid that we set to inline Xsl and paste this code into it:

<xsl:variable name="data">
  <xsl:value-of select="//EncodedData" />
</xsl:variable>
<xsl:value-of select="userCSharp:Base64DecodeData($data)" disable-output-escaping="yes" />

This functoid is connected to the output root node giving us a map that looks like this:

When executing this map with the input message above, we will get the output message properly formatted.

The tricks used here is two. First off, we use the same pattern as before with calling a predefined function we have in another scripting functoid in order to call a simple C# function from our XSL code. Then in the XSL, we first extract the string from our input message and store it in a variable. This is then passed into our C# function and we get a string back. However, if we do not specify the disable-output-escaping="yes" in our value-of select, we would get the string entity encoded. With this extra property set, the string will be output as it is and the way we want it.

The same technique can of course easily be used to just output part of a message by simply connecting the scripting functoid to the node you want to populate (if for instance you have a schema that has a node defined as xs:any that you want to use).

Error 5644 when trying to enable a SQL Server notification receive location in BizTalk Server

When using the SQL Server broker functionality in order to have SQL Server push notifications to BizTalk instead of polling a table, I've found that the following error is quite common to encounter.

 The Messaging Engine failed to add a receive location "Event1" with URL "mssql://localhost//EventDB" to the adapter "WCF-Custom". Reason: "Microsoft.ServiceModel.Channels.Common.TargetSystemException: The notification callback returned an error. Info=Invalid. Source=Statement. Type=Subscribe.

The error message tells you that the statement entered is invalid, but not in which way. There are a lot of rules to comply with that all are available on MSDN: http://msdn.microsoft.com/en-us/library/ms181122.aspx And beside the "normal" ones such as "do not use aggregate functions" and "do not use an asterisk to define the resultset", one that constantly haunts me are "table names must be qualified with two-part names", meaning that you have to prefix all tables with the schema name, such as "dbo.EventTable". Just using select blah from EventTable where processed=0 will generate the error above.

SQL Server: Invalid prefix or suffix characters error message

When trying to do different operations in SQL Server Management Studio such as "Edit Top 200 Rows", you might get the error message "Invalid prefix or suffix characters. (MS Visual Database Tools)".

This is most likely due to the fact that the Management Studio tool is an older version than the database you are connected to and trying to perform the operation on. For instance, using SQL Server Management Studio for SQL Server 2008R2 and connecting to a SQL Server 2012 database will render the above error message when trying to perform the "Edit Top 200 Rows" operation on a table or view.

The solution is to use the same version of Management Studio as the database in question.

BizTalk with codeplex SFTP adapter, doing a file move on AfterGet

A client has a setup with the Codeplex SFTP adapter, reading files from a site and then deleting them when read using the Delete parameter in the AfterGet property within the receive location.

A need arised where the files would need to be moved to an archive folder in the SFTP site instead of being deleted.

There is no mention of this in the documentation of the adapter, however, by checking the underlying code one can see how we can do the move.

The OnBatchComplete method located in BatchHandler.cs, will be called when a message has been sent successfully to BizTalk and hence be removed/renamed on the SFTP server. The line

string renameFileName = CommonFunctions.CombinePath(Path.GetDirectoryName(fileName), batchMessage.AfterGetFilename);

Is the interesting part. We see that when the Rename option is chosen in the adapter properties, the  CombinePath method will be called to create the new full path for the file based on the current directory where it is located, and a new filename. This can be utilized by simply setting the new filename to include a relative path as well as the filename and by doing that create a move of the file and have the rename optional. The latter since the macro replacements will be done after the creation of the full path.

Suppose we read files located in /home/marcus/btstest/ and after reading these should be moved to /home/marcus/btstest/archive/. In this case, you would set the AfterGet File Name property to read archive/%SourceFileName%

If you instead want to move the files to a directory outside of your current path, you can do so by specifying the parent by the usual two dots (..) like for instance ../btstestarchive/%SourceFileName% for the directory located at /home/marcus/btstestarchive/.

One issue can occur though if you use this technique to move files to a file archive in order to have a log on the ftp server side of all files that has been processed. If the filename is already available in the archive folder and the adapter tries to move yet another file to this folder with the same name, it will fail. It will be logged in the event log as "Method: Blogical.Shared.Adapters.Sftp.SharpSsh.Sftp.Rename
Error: Unable to rename /home/marcus/btstest/testfile.xml to /home/marcus/btstest/archive/testfile.xml", but the original file will be left in the pickup folder on the SFTP site.

BizTalk will not try to process this file again, until you restart the host that is. Then it will be picked up once more, sent to the messagebox and the rename function will fail again. You could make it a bit safer by using the %DateTime% or %UniversalDateTime% macros that also are available besides %SourceFileName%. Yet another option is to extend the BatchHandler.cs with the %MessageID% macro and create a GUID to add to the filename in order to get it truly unique.

Fix for jerky mouse movements in remote desktop/Citrix sessions to Windows Server 2012

When connecting with Citrix to a Windows Server 2012, you might experience that the mouse pointer is lagging severely causing a jerky movement and making it a complete dud trying to make any proper work on the server.

If this is the case, you should make sure that the Enable pointer shadow checkbox in the Mouse Properies is unchecked. This feature has been the issue with many machines I've encountered lately and unchecking the property has made the UI more enjoyable.

Recreating a BizTalk Active Directory group will cause access to BizTalk to fail

When setting up and configuring a BizTalk environment you will also add the necessary Active Directory groups to allow access for different accounts to the platform.

In most cases, you will not touch these groups again, but there might come a time when you remove and add the groups with the same name within Active Directory. Doing so will change the SID of the group and it will no longer match the SID stored in SQL Server for the Login.

Trying to access the BizTalk Server Administration Console as a BizTalk Server Operator will for instance yield the following error when the corresponding AD group has been recreated:

You will get an error saying "BizTalk Server cannot access SQL Server. [...] Access permissions have been denied to the current user. [...] Login failed for user [...]".

You will also notice a logged error in SQL Server saying that "Login failed for user [...] Reason: Could not find a login matching the name [...]"

When looking at both the Active Directory accounts/groups as well as the SQL Server security settings, everything will look ok.

In order to correct this, you will have to remove and then add the corresponding Login in SQL Server. This might also cause you to have to delete and then add the corresponding User in the different BizTalk Databases (in general the BizTalkMgmtDb, BizTalkDTADb and BizTalkMsgBoxDb). This will also be notified by SQL Server when removing the Login.

After doing these remove/adds in SQL Server, you should be able to access the resources as expected.

Note that this is documented briefly in the Troubleshooting BizTalk Server Administration page on MSDN, but you more or less have to know where/and for what to look in order to find it.

The difference between single and double quotes in PowerShell

I had a colleague ask me the other day about whether there was a difference between using single or double quotes when defining a string variable in PowerShell. For that specific questions, the answer was "no". However, there is an important distinction to make between the two.

With double quotes, the text within the quotes will be parsed. For instance, the following code will output "Hello Marcus":

$name = 'Marcus'
$output = "Hello $name"
write-host $output

However, when changing the second line to using single quotes as so:

$output = 'Hello $name'

The output will instead be "Hello $name"

The parsers handling of the different quotes also means that if you are to use escape characters, for instance `n, you have to put the string within double quotes for the escape characters to work. If you want to use a double quote within such a string, you can write it using the escape character:

$mystring = "This is a variable called `"mystring`""

Having the possibility of using the two different quotes can also allow for creating strings that contain one of the two quote characters, for instance when building query strings:

$name = 'Marcus'
$query = "SELECT Id, City from Customers WHERE Name LIKE '%$name%'"

Knowing this difference between single and double quotes is quite crucial for being able to build PowerShell scripts and not get into a mess when trying to create dynamic execution of other scripts or programs that require parameters to be passed to them.

Base64 encode/decode part of messages in map

This post is to show how to base64 encode or decode a part of a message (or the entire input message for that matter) and output the resulting string in an element in the output message.

The technique used allows for execution of inline C# (or VB) code from another script functoid using inline XSLT / XSLT Template.

The input and output schemas looks like this:

In the example, our entire input message should be base64 encoded and the resulting string output to the EncodedData element in the output message. This element is of the type xs:string.

First, we create a script functoid to hold our inline C# code for base64 encoding of the input node. When building such a method, one might first try to use string as the datatype for the input which will result in just the inner xml being transferred to the method. This is since the data from our XSLT call will be of the type MS.Internal.Xml.Cache.XPathDocumentNavigator. We therefore declare the input variable as XPathNavigator instead to get access to the full XML node. The base64 encoding is done using the System.Text.Encoding class together with the System.Convert class.

public string Base64EncodeData(XPathNavigator param1)
{
    byte[] bytesToEncode =
        System.Text.Encoding.UTF8.GetBytes(param1.OuterXml);
    string encodedText = Convert.ToBase64String(bytesToEncode);

    return encodedText;
}

This functoid will not be connected to any node, but just left to "hang" in the map since we call it manually from another script functoid. Another solution to have it a bit cleaner would be to place this code in an external assembly and call that, but sometimes it is nice to have everything in the map.

Next we create a script functoid and connect it to the destination element. We set the following Inline XSLT code:

<xsl:variable name="data">
  <xsl:copy-of select="/" />
</xsl:variable>

<xsl:element name="EncodedData">
  <xsl:value-of select="userCSharp:Base64EncodeData($data)" />
</xsl:element>

We first declare a variable that holds a copy of our source node which we simply specify using xpath.
Then we call the previously configured method doing the encoding, and send the resulting data to our created destination element.

The map will now look like this:

The result from sending in a test XML is an XML with the entire original contents base64 encoded in an element.

Pay attention when running Biztalk Server and SQL Server on servers in different timezones

This post discusses what most likely is a seldom encountered setup, but nevertheless a possible one. Consider a global company that is using BizTalk. For various reasons, the SQL cluster will have Windows configured with another timezone than the rest of the servers.

This is our mock setup. BizTalk is located in the U.S. and at UTC-5 while the SQL server is in Sweden at UTC+1.

During normal operations, this will work just fine. Each server will locally run in it's own timezone and all is good. The issues appear when working with service windows.

Let's create a receive port with a corresponding receive location pulling in files from a folder.

Now we want to use a service window on this receive location to only allow files to be processed during two hours every afternoon. This is in a real world scenario most likely going to be set up with a time reference to the BizTalk Server, i.e. the service window time will match the timezone on the BizTalk Server. In our example, it's 16:00 to 18:00 that is allowed, UTC+1.

If we look in the corresponding table in the SQL Server for this receive location, we can see that the time is set accordingly.

However, when the BizTalk Server rolls up to this time, the receive location will still not pull the file in! Why is that?

Well, it seems that when BizTalk is checking if the receive location should be active or not, it compares the stored service window time with the local time on the SQL Server. BizTalk is after all heavily based on SQL Server. This have the effect that the time that we set on the BizTalk machine in UTC+1 and which is stored as such but without the timezone information will be used as is and hence be offset by six hours in this example.

If we change the service window time in the BizTalk Administration Console to be offset with the corresponding six hours (i.e. 16:00 will be 10:00 instead), the receive location will trigger at the intended time.

So far so good. If you know about this issue, it is easy to work around it. However, if you add in the normal complexity regarding daylight saving time that will change the offset yet a little twice a year, you are most likely heading towards a small headache!

Poor-man's scheduled trigger message task in BizTalk

From time to time you might encounter the need for a trigger message in BizTalk. No matter the reason, you need a message that is sent to BizTalk every x minutes that in turn will trigger a process.

In most cases, you will end up installing the BizTalk Scheduled Task Adapter from Codeplex. This adapter is throughly tested and well-used in different scenarious around the world and is a very valid option.

If you do not want to install a third-party adapter for any reason but still need a trigger message to be sent every x minutes, you can script it using the Windows Task Scheduler. This solution will however require you to add the scheduled task to your monitoring solution to make sure that it stays active.

Yet another solution that can be commonly seen is to have an SQL Agent job set a flag in a table every x minutes and then have BizTalk poll this table according to the usual techniques.

My solution to creating a simple trigger message is to utilize the built-in features in BizTalk and not depend on any external trigger mechanism. By setting up a simple receive location with the WCF-SQL adapter, it can be made.

First, create a receive port and location. In the WCF-SQL transport properties, set the endpoint URI to any valid SQL server. I use BizTalks management database in this case, since there won't be any load on it at all from this receive location and it always will be available.

Next, in the Binding section, set the XmlStoreProcedureRootNodeName to TriggerRoot and the XmlStoredProcedureRootNodeNamespace to http://trigger/v1

Using XmlPolling as InboundOperationType, you set the PolledDataAvailableStatement to Select 1 to simply always trigger a polling statement execution. The PollingStatement in turn is set to WITH XMLNAMESPACES(DEFAULT 'http://trigger/v1') select CURRENT_TIMESTAMP for xml raw ('Trigger'), ELEMENTS

PollingIntervalInSeconds can be set to any interval you like. Also make sure that the PollWhileDataFound property is set to False.

What this does is that when the receive location is started, the first statement will return 1, telling BizTalk that there is a message to be fetched and to execute the polling statement. This statement will return a raw xml message that will look like this:

<TriggerRoot xmlns="http://trigger/v1">
    <Trigger>2013-09-14T12:20:11.037</Trigger>
</TriggerRoot>

You can then create a schema for this message and then in turn use it in your BizTalk solution to trigger a process on a timely schedule. Quick and simple and does the job. The bonus is that as long as the receive location is up and running, you know that it will create a message for you.