12. Handle ANSI X12 EDI Escape Character by Custom Pipeline in BizTalk

Since ANSI X12 do not support the escape character (EDIFACT supported), if the EDI content contains any character that the same as the element separator, component separator or segment terminator, it will cause some exceptions in both inbound and outbound EDI.

Let’s say an example, see the screen shot below, in this case, ‘123456-*22’ is the vendor part number, * is part of the part number, not the element separator, and the ‘#’ is the escape character:

 If you drop the file to process by BizTalk, you will receive the exception as below, BizTalk report that too many element in this segment, because BizTalk recognize the ‘*’ in the part number as the element separator.

Also, when you send out any EDI that within *, BizTalk report that invalid character found in data element.

So how to handle it? Not difficult, just need 2 steps in either inbound or outbound.

Inbound:

1.     Develop a custom pipeline to replace the in the part number * to other characters in the decode step, for example, in this case, we replace ‘#*’ to ‘/+’;

So after the custom pipeline processing, the part number looks like below:

 

2.     Replace the ‘/+’ back to ‘*’ in the mapping. Here is the final result:

Outbound:

1.     Replace ‘*’ to ‘/+’ in the mapping;

Here it the original message;

Here is the message output from the mapping.

2.     Replace ‘/+’ to ‘#*’ in the encode step in custom pipeline.

Here is the final result:

Here is part of the code in custom pipeline:

11. BizTalk 2009 SSO Configuration Error: Ox80131700

I got an error as below while configuring BizTalk 2009 in Windows 2003:

Could not start the Enterprise Single Sign-On Service service on Local Compuer. Error Ox80131700:

Here is the solution how to fix it:

Run Visual Studio Command prompt as Administrator and run 'regasm "C:\Program Files\Common Files\Enterprise Single Sign-On\SSOSQL.dll"'.

9.Compare string with single quote in XSLT mapping

8.BizTalk special character in XML beginning, EF,BB,BF, PreserveBOM

Some of our clients report that, there are receive our XML file with a special character at the beginning of each file.

Then I try to open the file in Visual Studio, XML Spy, notepad, it looks fine, no spcecial character over there.

 

 

But if I open it in Ultra Edit or Compared It, I can see there is really a special character over there, view it as binary mode; you can see that the special character is EF BB BF

Then I go to check other XML file send out by BizTalk, all within this special character, I’m very surprised in it. Although it will not cause any errors, but some client’s application cannot recognize this special character and cause some errors.

After an hour investigation, I found that there is a property configuration –PreserveBOM in the XML send pipeline, it is “true” by default, if we configure it to “false”, the special character “EF BB BF” will not present anymore.

7. Wrap/Enter in BizTalk mapping

If you are using custome XSLT, you can use the following code to wrap in Biztalk mapping.

<xsl:text> </xsl:text>

6. C# Excel Save As XML

In my last post, I show a way to convert Excel as XML by OLDEDB and DataSet.

Here is another way to save Excel as XML, no OLEDB, no DataSet necessary.

Remember to reference

C:\Program Files\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Excel.dll

Or

C:\Program Files\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office11\Microsoft.Office.Interop.Excel.dll

I try it in both *.xsl and *.xslx format, all works fine.

public void MySaveExcelAsXml()

        {

            Microsoft.Office.Interop.Excel.Application _excelApp = new Microsoft.Office.Interop.Excel.Application();

            Microsoft.Office.Interop.Excel.Workbook workBook = _excelApp.Workbooks.Open(@"G:\input.xls",

            Type.Missing, Type.Missing, Type.Missing, Type.Missing,

            Type.Missing, Type.Missing, Type.Missing, Type.Missing,

            Type.Missing, Type.Missing, Type.Missing, Type.Missing,

            Type.Missing, Type.Missing);

            workBook.SaveAs(@"G:\test.xml",

                Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet,

                null,

                null,

                true,

                true,

                Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,

                Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges,

                true,

                null,

                null,

                false);

            workBook.Close(null, null, null);  

        }

5. C# Convert Excel to XML by OLEDB and DataSet

public XmlDocument MyConvertExcelToXml(string excelFilePath)

        {

                XmlDocument excelData = new XmlDocument();

                DataSet excelTableDataSet = new DataSet();

                StreamReader excelContent = new StreamReader(excelFilePath, System.Text.Encoding.Default);

                string stringConnectToExcelFile = string.Format("provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=Excel 12.0;");

                System.Data.OleDb.OleDbConnection oleConnectionToExcelFile = new System.Data.OleDb.OleDbConnection(stringConnectToExcelFile);

                System.Data.OleDb.OleDbDataAdapter oleDataAdapterForGetExcelTable = new System.Data.OleDb.OleDbDataAdapter(string.Format("select * from [Output$]"), oleConnectionToExcelFile);

                try

                {

                    oleDataAdapterForGetExcelTable.Fill(excelTableDataSet);

                }

                catch

                {

                    return null;

                }

                string excelOutputXml = Path.GetTempFileName();

                excelTableDataSet.WriteXml(excelOutputXml);

                excelData.Load(excelOutputXml);

                File.Delete(excelOutputXml);

                return excelData;

        }

Remember to using the namespace:

using System.Xml;

using System.Data;

And change the sheetname “Output” to your sheet name.