Introduction
Recently, we had worked on a BizTalk project that had requirement of pipe delimited flat file (FF) generation. We had several learnings due to the various requirements that were expected from this task. I would like to share some of the learnings in this article which may help others during development.
First, we would briefly go through the different aspects of requirement to be implemented as a part of this task.
We will go through each of these functionalities and explain how these were addressed using BizTalk 2013 R2.
Order XML file comes from the EOM (Enterprise Order Management) System, which has to be converted to pipe delimited FF format with certain mappings and rules
We used an intermediate Canonical XML format to convert the input Order XML file before converting to the FF. The business logic and looping is achieved in this conversion and stored as the canonical XML fields. During conversion of canonical to output FF, the idea is to have a direct one to one mapping without much business logic in it. Canonical format also helps when one or more source or target systems are added to the systems to integrate. In these scenarios, we would want to make the conversion to canonical from source and/or canonical to target formats. If the canonical schema approach is not used, we need to maintain different maps for conversion between different source and target formats which is cumbersome.
As per the BizTalk practice, canonical to Flat file conversion requires FF schema to be specified in the send port for flat file generation.
Order XML file comes from the EOM (Enterprise Order Management) system, which has to be converted to pipe delimited FF format with certain mappings and rules.
FF needs to have repeated lines for each of the invoices and its lines
We used XSLT for the entire conversion process of canonical to output FF format. Hence, achieving this in XSLT is relatively simple as we can use for each loops looping through each of the Invoice nodes in the input XML and forming the output elements repeatedly for each of the input invoice and lines.
<xsl:for-each select="// Invoices">
<Element1></Element1>
<Element2></Element2>
…
<xsl:for-each select="// Invoices/Line">
<LineElement1></LineElement1>
<LineElement2></LineElement2>
…
FF needs to have a header element on the top before all the contents
Sample Header:
<Header download_id="SALE_20180322121907.txt" deployment_name="ORDER_SALE" download_time="IMMEDIATE" />
This has been achieved by creating an optional Header element in the BizTalk FF schema. This can be tested using FFAsm.exe to test XML to Flat file generation or FFDAsm.exe to test the FF to XML conversion. We will see more on these tools and how to use them in a later section.
Separate Flat files need to be generated for Sales and Adjustment in different locations
We have added an extra field InvoiceType in the canonical header which will have the values “Sales” or “Adjustment”. Input XML to Canonical XML conversion will populate this value appropriately based on the input file field values. Also the InvoiceType is promoted in the canonical schema so that it can be viewed in the Send Port Filter. Once it is viewed, we can check for this condition in Send Port and use it to generate the file in different locations.
SendPortAdjustment - Filter Condition: InvoiceType == Adjustment
SendPortSale - Filter Condition: InvoiceType == Sale
Pipe delimited format for Adjustment needs to have dynamic contents rather than fixed elements.
We had the requirement of generating the adjustment Flat file with the following format:
RUN_SQL|INSERT INTO TABLE(FIELD1, FIELD2, ….) VALUES (FIELDVAL1, FIELDVAL2, …)
RUN_SQL|INSERT INTO TABLE(FIELD1, FIELD2, ….) VALUES (FIELDVAL1, FIELDVAL2, …)
For this, we created FF Schema with elements with tag_name=”RUN_SQL|”. Hence, it will construct the FF with RUN_SQL as the starting tag and the INSERT commands subsequently following it after a pipe delimiter. We used XSLT and C# to build the values with RUN_SQL|INSERT INTO TABLE … and assigning the dynamically built insert query to the XSD element. The built schema XML can be converted to/from FF using the pipeline tools FFAsm.exe/FFDAsm.exe.
As part of PCI DSS compliance, the credit card number should be encrypted during transfer and decrypted only during placement of the file in client location
The credit card number when the TenderId is PRIVATE_CC needs to be decrypted. We achieved this using xSLT templates as below. In case TenderId is PRIVATE_CC, the below XSLT code copies all the elements except AccountNumber from source to target. If it is AccountNumber, it calls a C# function to decrypt it and stores it in the same element. The below code when applied to a map does the job of decryption of credit card number using C# function fcnDecryptString.
<xsl:template name="Copy" match="@* | node()">
<xsl:param name="param" />
<xsl:copy>
<xsl:apply-templates select="@* | node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match=" CREDITDEBIT_TENDERLINEITEM[TenderId='PRIVATE_CC']">
<xsl:variable name="varEncryptedAcctNo" select="AccountNumber"/>
<xsl:element name="{name()}">
<xsl:for-each select="./*">
<xsl:choose>
<xsl:when test="name()='AccountNumber'">
<xsl:element name="AccountNumber">
<xsl:variable name="result" xmlns:ScriptS0="http://SPFunctionHelper"
select="ScriptS0:fcnDecryptString($varEncryptedAcctNo)" />
<xsl:value-of select="$result"/>
</xsl:element>
</xsl:when>
<xsl:otherwise>
<xsl:copy-of select="."/>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</xsl:element>
</xsl:template>
Calling a C# function present in an external assembly from XSLT is done using the approach suggested in this blog.
Handling optional elements in FF, required making all the FF XSD elements in Sales schema as Choice with minOccurs=0 and maxoccurs as unbounded
We received FF XML files (based on FF Schema) that had the elements present or sometimes some of the elements would be missing. And the FF XML schema needs to successfully create the Flat file or parse it into XML even if some of the elements would not be present.
For this, we need to create Root element and have a complex type inside the root with a choice element with minOccurs 0 and maxOccurs unbounded as below. This will support the scenarios where the FILE_HEADER or FILE_TRANSLINEITEM to be present or missing and in both cases, the pipe delimited FF will be generated from XML or parsed to XML file successfully.
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
…
<xs:element minOccurs="0" maxOccurs="unbounded" name="FILE_HEADER">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="delimited"
child_delimiter_type="char" child_delimiter="|"
child_order="prefix" preserve_delimiter_for_empty_data="true"
suppress_trailing_delimiters="false" sequence_number="1"
tag_name="INSERT|TRANS_HEADER" />
</xs:appinfo>
</xs:annotation>
…
<xs:element minOccurs="0"
maxOccurs="unbounded" name="FILE_TRANSLINEITEM">
<xs:annotation>
<xs:appinfo>
<b:recordInfo structure="delimited" child_delimiter_type="char"
child_delimiter="|" child_order="prefix"
preserve_delimiter_for_empty_data="true"
suppress_trailing_delimiters="false"
sequence_number="2" tag_name="INSERT|TRANS_LINE_ITEM" />
</xs:appinfo>
</xs:annotation
…
Header level items in FF requires calculation of sum or average of the different line level values such as Amount, Tax, etc.
This has been done using node set concept and the xslt has been provided for this. If the order has multiple line items, we had some requirements to calculate the total of all the amounts in the line items and store them in a field at the header level.
<xsl:variable name="tmpTotal">
<total_amount>
<xsl:for-each select="Item">
<itemtotal>
<xsl:value-of select="format-number(Item/Amount,'###,###,##0.00')" />
</itemtotal>
<xsl:variable name="varTotal" select="msxsl:node-set($tmpTotal)"/>
<xsl:variable name="varTotalofAllItems" xmlns:ScriptS0="http://SPFunctionHelper"
select="ScriptS0:RoundValue(sum($varTotal/total_amount/itemtotal)" />
The above XSLT creates a variable called tmpTotal which maintains the individual values of all the line item amount. It stores the individual amounts in the itemtotal variable which can be accessed using the node-set variable $varTotal. The expression sum($varTotal/total_amount/itemtotal) calculates the total amount of all the line items and gives a single Total value which can be used at the header level.
FF Disassembler and assembler command line tool have been used to test the FF schema if they are without any issues
BizTalk provides several pipeline tools that help us to test the FF or XML pipelines if they are generating or parsing the files correctly. Refer to this link for more information. In our case, we used FFAsm.exe to convert FF XML Schema to actual Flat file. Also FFDAsm.exe was used to convert the actual pipe delimited Flat file to FF XML Schema.
They are present in BizTalk Installation Path>\SDK\Utilities\PipelineTools.
FFAsm.exe file_inp.xml –bs myBodySchema.xsd
The above command generates Flat file from file_inp.xml of the format myBodySchema.xsd.
FFDasm.exe file_in.txt –bs myBodySchema.xsd
This command generates XML file of format myBodySchema.xsd from flat file file_in.txt.
Conclusion
Hope this article addresses several common problems faced during BizTalk development and provides solutions. Feel free to provide your suggestions/comments if any.
