24 November 2010

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.

23 November 2010

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.