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.

4. Cannot Track Message In BizTalk

One of my clients report me that, they cannot track BizTalk message though turn on the track message properties in send/receive port or orchestration.

Then I connect to their BizTalk 2009, turn on the tracked messages for some send port, receive port, orchestration to test it, but very strange, when I open the send port, receive port, orchestration again, all the tracked message properties roll back to unchecked(turn off).

I take nearly an hour to check it and finally found that the “Global Tracking” properties was turned off(The client told me they have the performance issue in the last Thanksgiving, that’s why they turn off it, but forgot it). Then I try to turn it on and everything is OK finally.

Here are the steps from Microsoft about how to turn on/off Global Tracking.

How to Turn Off Global Tracking

0 out of 1 rated this helpful - Rate this topic

By default, global tracking is enabled when you install BizTalk Server 2010. The BizTalk Tracking (BizTalkDTADb) database grows in size as BizTalk Server processes data on your system. If the size of the BizTalk Tracking database causes poor disk performance, you can purge the data from the Tracking database. If you are having performance issues that are momentarily addressed by purging the BizTalk tracking database, and you want to configure BizTalk to no longer collect tracking information, you may want to consider turning off global tracking.

It is important to understand that turning off global tracking disables the tracking interceptors for the entire BizTalk Server group. This means that BizTalk will not track events in its tracking tables. Alternatively, you can turn off tracking for individual events.

Note

If you are using Business Activity Monitoring (BAM), you should maintain a dedicated tracking host, even if you disable global tracking. This is because BAM events use the Tracking Data Decode Service (TDDS).

Prerequisites

---

You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.

To turn off global tracking (SQL Server 2008)

---

1.       On the SQL server that hosts the BizTalk Tracking (BizTalkDTADb) database, click Start, click Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

2.       In the Connect to Server dialog box, verify the server name and authentication, and then click Connect.

3.       In Microsoft SQL Server Management Studio, in Object Explorer, expand <computer name>, expand Databases, expand BizTalkMgmtDb, expand Tables, right-click adm_Group, and then click Open Table.

4.       In the table viewer, scroll horizontally until you find GlobalTrackingOption.

5.       In the GlobalTrackingOption column, change the value from 1 to 0, to turn off this feature, and then press ENTER.

6.       Close Microsoft SQL Server Management Studio.

Note

You must restart your BizTalk hosts for the change to take effect.

7.       Click Start, click Programs, point to Microsoft BizTalk Server 2010, and then click BizTalk Server Administration.

8.       In the BizTalk Server Administration console, expand BizTalk Server Administration, expand BizTalk Group, expand Platform Settings, and then click Host Instances.

9.       In the details pane, right-click each host, and then click Restart.

3. C# get DateTime with timezone offset

Use zzz parameter to specify the timezone offset as hours and minutes, for example:

DateTime.Now.ToString("yyyy-MM-ddTHH:mm:sszzz")

Return:

2013-03-12T10:38:45+08:00

2. BizTalk EDI Batch Start Button Disabled

One of my client report me that the EDI batch stop to generate the EDI batch file in our BizTalk production server today, it seems serious.

When I login to the BizTalk server, everything looks fine, BizTalk received the LOB message, transformed it to EDI format and send it to the EDI batch orchestration. But, the “Start”, “Override”, “Stop” button in the batch all are disabled, I can only Refresh it.

Then I tried to restart the host instance and the application, I thought it will be OK, but, all the button are the same--disabled.

I try to check the suspended message and found that, there are some EDI batch messages suspended over there. The suspended services name is :

Microsoft.BizTalk.Edi.BatchingOrchestration.BatchingService, Microsoft.BizTalk.Edi.BatchingOrchestration, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

Then I terminated all these type of service and all of the button enabled again after restart the host instance. I can Start, Override, Stop the instance now.

I don’t know why Microsoft design the Edi Batch at this way----one EDI message failed, then all of the batch failed. But I really don’t like it, it make me have to check the EDI status always, maybe I have to develop a tool to monitor it.