Mapping a Grouped Flat File

Recently I had to process a type of flat file with a rather tricky format that looked something like this (simplified for clarity):

H,INV_201008.CSV,2010-09-01

G,1011,CODE_001

D,23562,100.00,32,2010-05-05

D,73522,110.00,33,2010-05-06

D,82643,104.00,11,2010-05-07

G,1012,CODE_002

D,23553,99.00,32,2010-05-05

D,73463,156.60,11,2010-05-06

D,96473,122.10,88,2010-05-07

G,1013,CODE_003

D,25435,30.00,14,2010-05-05

D,13435,80.00,8,2010-05-06

D,97563,42.00,22,2010-05-07

A data file consists of one header (H) row, multiple group (G) rows and multiple data (D) rows for each G row.

After using BizTalk's flat file schema wizard, I had to make some manual modifications to the schema to accommodate the unbounded repeating set of G and D rows by treating G as a record,

D as a repeating record and then wrapping the G and D elements within an <xs:sequence maxOccurs="unbounded"> tag.

BizTalk will generate an xml representation of the flat file that looks like this:

<Header>

<FileName>INV_201008.CSV</FileName>

<CreationDate>2010-09-01</CreationDate>

</Header>

<GroupRow>

<Id>1011</Id><Code>CODE_001</Code>

</GroupRow>

<DataRow>

<Id>23562</Id><Price>100.00</Price><Qty>32</Qty><Date>2010-05-05</Date>

</DataRow>

<DataRow>

<Id>73522</Id><Price>110.00</Price><Qty>33</Qty><Date>2010-05-06</Date>

</DataRow>

<DataRow>

<Id>82643</Id><Price>104.00</Price><Qty>11</Qty><Date>2010-05-07</Date>

</DataRow>

<GroupRow>

<Id>1012</Id><Code>CODE_002</Code>

</GroupRow>

<DataRow>

<Id>23553</Id><Price>99.00</Price><Qty>32</Qty><Date>2010-05-05</Date>

</DataRow>

<DataRow>

<Id>73463</Id><Price>156.60</Price><Qty>11</Qty><Date>2010-05-06</Date>

</DataRow>

<DataRow>

<Id>96473</Id><Price>122.10</Price><Qty>88</Qty><Date>2010-05-07</Date>

</DataRow>

<GroupRow>

<Id>1013</Id><Code>CODE_003</Code>

</GroupRow>

<DataRow>

<Id>25435</Id><Price>30.00</Price><Qty>14</Qty><Date>2010-05-05</Date>

</DataRow>

<DataRow>

<Id>13435</Id><Price>80.00</Price><Qty>8</Qty><Date>2010-05-06</Date>

</DataRow>

<DataRow>

<Id>97563</Id><Price>42.00</Price><Qty>22</Qty><Date>2010-05-07</Date>

</DataRow>

As can be seen, when BizTalk disassembles the flat file, the xml data is in a flat structure. The challenge is to transform it to a hierarchical structure like this:

<Header>

<FileName>INV_201008.CSV</FileName>

<CreationDate>2010-09-01</CreationDate>

</Header>

<Group>

<Id>1011</Id><Code>CODE_001</Code>

<Data>

<Id>23562</Id><Price>100.00</Price><Qty>32</Qty><Date>2010-05-05</Date>

</Data>

<Data>

<Id>73522</Id><Price>110.00</Price><Qty>33</Qty><Date>2010-05-06</Date>

</Data>

<Data>

<Id>82643</Id><Price>104.00</Price><Qty>11</Qty><Date>2010-05-07</Date>

</Data>

</Group>

<Group>

<Id>1012</Id><Code>CODE_002</Code>

<Data>

<Id>23553</Id><Price>99.00</Price><Qty>32</Qty><Date>2010-05-05</Date>

</Data>

<Data>

<Id>73463</Id><Price>156.60</Price><Qty>11</Qty><Date>2010-05-06</Date>

</Data>

<Data>

<Id>96473</Id><Price>122.10</Price><Qty>88</Qty><Date>2010-05-07</Date>

</Data>

</Group>

<Group>

<Id>1013</Id><Code>CODE_003</Code>

<Data>

<Id>25435</Id><Price>30.00</Price><Qty>14</Qty><Date>2010-05-05</Date>

</Data>

<Data>

<Id>13435</Id><Price>80.00</Price><Qty>8</Qty><Date>2010-05-06</Date>

</Data>

<Data>

<Id>97563</Id><Price>42.00</Price><Qty>22</Qty><Date>2010-05-07</Date>

</Data>

</Group>

I tried several approaches but couldn't seem to get BizTalk's mapper to generate what I wanted. I then tried the custom XSL route and after a lot of experimentation, I got it working.

Creating the <Group> nodes was the easy bit (using an <xsl:for-each> and selecting the <GroupRow> nodes). Creating the filter expression for <Data> nodes within each group node iteration was the hardest part. In a nutshell, the <xsl:for-each> select statement was:

following-sibling::*[local-name()='DataRow' and (count(preceding-sibling::*[local-name()='GroupRow'])) = $marker]

where $marker is a variable that I set at the start of each group node iteration like this:

<xsl:variable name="marker" select="count(preceding-sibling::*[local-name()='GroupRow'])+1" />

Hope this tip will save someone some time.

BizTalk and X509 Certificate Permissions

Our client needs to retrieve data from a third party web service over SSL using an X509 certificate via BizTalk. The setup is to use a wsdl generated C# proxy class, subclass it and configure the certificate in the constructor:


//Note: ClientCertificates is a property of the base class System.Web.Services.Protocols.SoapHttpClientProtocol
this.ClientCertificates.Add(new System.Security.Cryptography.X509Certificates.X509Certificate(filePath, password));


Then in BizTalk Server Administration Console, the send port is using a SOAP adapter configured to use the proxy class and method by specifying the assembly.

One thing to note is that although the code above is configured to read the certificate's file path and password as app settings from the BTSNTSvc.exe.config file (BTSNTSvc64.exe.config if the handler is running in 64 bit mode), the certificate still needs to be imported into the machine's certificate store with the right permissions. I found this out the long way when things were running smoothly on my development machine but kept failing on the integration server with this message:

A message sent to adapter "SOAP" on send port "xxx" with URI "https://..." is suspended.
Error details: WebException: The request failed with HTTP status 403 Forbidden.

Not a very helpful message. Going on a hunch, I decided to trace the network activity using a very simple yet powerful .NET tracing technique I found at http://blogs.msdn.com/b/dgorti/archive/2005/09/18/471003.aspx. I reconfigured the BizTalk service to start logging all network activities and found this in the logs:

System.Net Information: 0 : [1720] SecureChannel#65709741 - Cannot find the certificate in either the LocalMachine store or the CurrentUser store.

I checked that the certificate was indeed in the certificate store. After a bit more Googling, the culprit turned out to be permissions. To fix the issue:

1. In mmc, right click on the certificate and choose All Tasks > Manage Private Keys...
2. Click on the Add... button and choose the BizTalk Application Users group (or whichever group that contains the account that runs your BizTalk host instance).
3. Ensure this group has Full control permissions then click OK.

Once that was done, my SOAP requests were working as expected.

WCF Service Errors and Serializing Enums

When consuming a WCF service, you may run into this common exception:

System.ServiceModel.CommunicationException: The underlying connection was closed: The connection was closed unexpectedly. ---> System.Net.WebException: The underlying connection was closed: The connection was closed unexpectedly.

at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
at System.ServiceModel.Channels.HttpChannelFactory.HttpRequestChannel.HttpChannelAsyncRequest.CompleteGetResponse(IAsyncResult result) --- End of inner exception stack trace ---

and the server stack trace looks like this:

at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

If you're invoking the WCF service from BizTalk, you will probably get the same exception above wrapped by an outer exception that looks like this:

Microsoft.XLANGs.Core.XlangSoapException: An error occurred while processing the message, refer to the details section for more information

with more stack trace info:

Exception rethrown at [0]:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

at Microsoft.BizTalk.XLANGs.BTXEngine.BTXPortBase.VerifyTransport(Envelope env, Int32 operationId, Context ctx)

at Microsoft.XLANGs.Core.Subscription.Receive(Segment s, Context ctx, Envelope& env, Boolean topOnly)

at Microsoft.XLANGs.Core.PortBase.GetMessageId(Subscription subscription, Segment currentSegment, Context cxt, Envelope& env, CachedObject location)

at ETMS.BizTalk.Orchestrations.ImportAutoWindData.segment2(StopConditions stopOn)

at Microsoft.XLANGs.Core.SegmentScheduler.RunASegment(Segment s, StopConditions stopCond, Exception& exp)

It turns out that the return object had a property which was an enumeration which had trouble serializing.

A colleague of mine introduced me to an excellent tool called WCFStorm. Basically it saved me from knocking up a quick client console app to test the WCF service. All I need to do is pass the service wsdl url to the utility. It discovers the types and properties, lets you populate the fields easily and send the request.

That allowed us to quickly deal with real issue, which turned out to be a property of the return type that was an enum. Having a [DataContract] attribute on the enum type itself is not enough, you will need to decorate each enum value with the[EnumMember] attribute:

using System.Runtime.Serialization;

...

[DataContract(Namespace="...")]

public enum PersonalityTypes

{

[EnumMember(Value="Introvert")]

Introvert,

...

After adding this attribute to each enum value, the WCF service will have no problem serializing the object and sending the results back successfully.

BizTalk Logging

I've read a few blog posts from respected BizTalk experts and the opinions are mixed over the use of debugging/tracing techniques. Some advocate it, others prefer to use BizTalk's suite of monitoring tools and built-in functionality to view what is going on with messages and routing.

My skin has been saved a few times by using explicit tracing and debugging so I'm going to stick with it. One thing I did notice is that often people will write directly to the event log in expression shapes. While that might seem fine, the logging implementation is tightly coupled to the event log. A more flexible approach is to write to the Trace and Debug subsystems (in System.Diagnostics). Listeners can be configured (and modified) in the BTSNTSvc.exe.config file quite easily. Just ensure the BizTalk service account has permissions to write to the target (eg event log or text file) otherwise nothing will show up.

GACing Assemblies in Windows 7 / .NET 4.0 / Visual Studio 2010

Before an assembly can be GACed, you need a strong name key. Open up the Visual Studio Command Prompt (2010) in administrator mode and type in:

sn -k c:\temp\MyKey.snk

Run Visual Sutdio 2010 in administrator mode and view the project’s properties. Under the Signing tab, check the Sign the assembly checkbox and choose your newly created key. Under the Build Events tab, add this under Post-build event command line:

"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe" /i
"$(TargetPath)"

Now when you build your project, the assembly will be automatically GACed. Note that you need to run Visual Studio in administrator mode so that the post-build command will run. You can view your GACed assemblies at C:\Windows\Microsoft.NET\assembly\GAC_MSIL.

Testing BizTalk Maps that use Flat File Schemas

I was testing a BizTalk map that used a flat file schema with a flat file instance input file and ran into this error:

Error 3 Output validation error: Data at the root level is invalid. Line 1, position 1.

Turns out that the map is treating the input file as an XML file. In the map’s property, ensure the TestMap Input is set to Native.

BizTalk Schema Build Error

I just ran into a rather strange problem when I tried to rebuild my BizTalk solution. A bunch of these errors popped up:

Error 3 Source file 'C:\Source\MyProject\InputFile_XML.xsd.cs' could not be opened ('Unspecified error ')

Turns out the rebuild wipes out *.xsd.cs files. These files are generated by Visual Studio at build time for each schema file but when they are removed, subsequent rebuilds will fail to regenerate these .xsd.cs files and the compilation fails. This seems to be related to TFS because the solution is to check out all the schema files in the project and build again. The .xsd.cs files will be generated again.