Changing SQL Services to run under domain account.

Well, At first, I thought that we may have to reconfigure the biztalk server or apply the SSO master secret from the backup. But to my Surprise, BizTalk is running as normal after changing the SQL services user to domain user.

Things to be noted here are

1. BizTalk still uses the same local user to connect to the database.

2. Only the SQL services run under different domain user now.

BizTalk Admin & Disaster Recovery

Well our BizTalk Server tracking database was growing large and the Job that was configured to purge and Archive was not running well.

Use the following to purge.

–– Change to the BizTalk Tracking database.



–– Purge tracking data older than two hours.


SET @dtLastBackup = GetUTCDate()

EXEC dtasp_PurgeTrackingDatabase 2, 0, 1, @dtLastBackup

Read more:

As I started executing this statement. It took a while and I could notice the drastic change in my BiztalkDTADb_log.ldf located it C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA.

It has started to grow to almost the size of my mdf file. When I thought it was done, Damn!! By log file grew out of the max limit size and threw an error.

Well, We would have to work on this task again.

Additionally, this article explains in detail on how to truncate tracking database.

If you ever want to plan for Disaster recovery, consider reading this article

Backup the Inbound file using the Pipeline and Pipeline component in BizTalk Server 2010

Ever wondered how to back up the HL7 file or for any file for that matter using the pipeline component?

Here is the code implementation of Execute method of pipeline component in decoder stage.

public IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg)
            Stream objOriginalStream;
            StreamReader objStreamReader;
            string strFinalMessage;
            IBaseMessagePart bodyPart = pInMsg.BodyPart;

            string fileName = String.Empty;

                if (pInMsg.Context.Read("InboundTransportType", "").ToString() == "MLLP")
                    fileName = System.Guid.NewGuid().ToString("d") + ".hl7";
                    fileName = pInMsg.Context.Read("ReceivedFileName", "").ToString();
                    fileName = Path.GetFileName(fileName);

            catch // Catches any exception and assigns file name to be guid.
                fileName = System.Guid.NewGuid().ToString("d") + ".hl7";  

            if (bodyPart != null)

                objOriginalStream = bodyPart.GetOriginalDataStream();

                if (objOriginalStream != null)
                        objStreamReader = new StreamReader(objOriginalStream);
                        strFinalMessage = objStreamReader.ReadToEnd();

                    //Backup the file unaltered
                        using (StreamWriter sw = new StreamWriter(Path.Combine(backupfilePath, fileName)))

                        //Code to Replace Only CR and LF to {CR}{LF}
                        strFinalMessage = strFinalMessage.Replace("\r\n", "{#EOL#}");        //Replace all types EOL – End of line to one unique string pattern- Start with \r\n.
                        strFinalMessage = strFinalMessage.Replace("\r", "{#EOL#}");
                        strFinalMessage = strFinalMessage.Replace("\n", "{#EOL#}");
                        strFinalMessage = strFinalMessage.Replace("{#EOL#}", "\r\n");

                        int intMSHIndex = strFinalMessage.IndexOf("\r\n");
                        string strMSHSegment = strFinalMessage.Substring(0, intMSHIndex);
                        string strBodySegment = strFinalMessage.Remove(0, intMSHIndex);

                        List<String> lstStr = strMSHSegment.Split(‘|’).ToList<String>();
                        while (lstStr.Count < 16)  //lenght is 1 based index, bounds is 0 based index
                        lstStr[14] = String.IsNullOrEmpty(strMSH15ConfigurableValue) ? "NE" : strMSH15ConfigurableValue;
                        lstStr[15] = String.IsNullOrEmpty(strMSH16ConfigurableValue) ? "ER" : strMSH16ConfigurableValue;

                        strMSHSegment = String.Join("|", lstStr);
                        strFinalMessage = strMSHSegment + strBodySegment;


                      { //Try to assign the new stream for downstream components.

                          byte[] byteArray = Encoding.GetEncoding(0).GetBytes(strFinalMessage);
                          bodyPart.Data = new MemoryStream(byteArray);
                      catch(Exception ex)
                      { //else just pass whatever came in
                          System.Diagnostics.EventLog.WriteEntry("BTIS", "BackupHL7Files – In Catch block – Exception occurred, letting the same data to pass" + ex.ToString());
                          bodyPart.Data.Seek(0, SeekOrigin.Begin);

            return pInMsg;


Errors Encountered :

The Pipeline component worked great, when the files were submitted via MLLP from other servers, but it was failing when I received the file from external sources.

Reason: The method or operation is not implemented.

Solution: I implemented the try catch block at the end of Execute method as you can see in the code example.

Hl7 Accelerator and xslt.

Actual Problem :

HL7 file coming out of HL7 Accelerator is having empty new line character in-between segments.

Segment looked like


 Cause for this problem.

The XML file that was fed into HL7 Accelerator had as new line character in those elements

<ORC_2_PlacerOrderNumber><EI_0_EntityIdentifier> {cr}{lf}



Solution :

Pass the XML through xslt transformation before sending it to HL7 accelerator

Here is the code that does the transformation.

 public static String  xslRemoveEmptyNodes(String xdoc)
            string strXmlOutput = xdoc;
                XmlDocument xDoc = new XmlDocument();
                string xslstring = @"<xsl:stylesheet version=’1.0′ xmlns:xsl=’’&gt;
                        <xsl:output method=’xml’ omit-xml-declaration=’yes’ indent=’yes’/>                         
                        <xsl:template match=""node()|@*"">
                           <xsl:apply-templates select=""node()|@*""/>
                         <xsl:template match=""*[not(@*) and not(*) and not(text()) ]""/>

                Stream xslOutStream = new MemoryStream();
                StringWriter writer = new StringWriter();
                using (XmlReader reader = XmlReader.Create(new StringReader(xslstring)))
                    XslCompiledTransform myXslTransform;
                    myXslTransform = new XslCompiledTransform();
                    myXslTransform.Transform(xDoc, null, writer);

                strXmlOutput = writer.ToString();
                int index = strXmlOutput.IndexOf("<ns1:") < 0 ? 0 : strXmlOutput.IndexOf("<ns1:");
                strXmlOutput = strXmlOutput.Substring(index);



Errors encountered during the process

Alternate Error Number: 301

Alternate Error Description: XmlReader not positioned at root elment of ‘ORU_R01_24_GLO_DEF’

Alternate Encoding System: HL7-BTA

Fix for this Error

Add a line <xsl:output method=’xml’ omit-xml-declaration=’yes’ indent=’yes’/>  in your xslt transformation.


LINQ to XML Challenge 1

Recently I came across with the unique requirement to do the extravagant changes on the incoming Hl7 data in XML document. At first, I handed it over to my offshore subordinate and explained the logic. After couple of days, when I started reviewing the code. I could clearly realize, it was not meant to be released to production. For that matter, not even to test. Lots of XML document, XMLNodes..etc. Overall the code looked old school lacking performance and scalability.

So I decided to write it myself.

Requirement : We get the ORU HL7 message in XML format with only one ORC and OBR and had to split it into multiple ORC and OBR. Also associate the corresponding OBX segments with the correct ORC and OBR, These associations would be taken form the SQL table.

public static void FormatSeperateORCOBR(ref XmlDocument doc, string trackingNo, string hl7Id)
                XElement xe;
                xe = XElement.Parse(doc.OuterXml);

                //Get the data from table
                DataSet dsResultSet = new DataSet();
                dsResultSet = DAL.DataAccess.GetTestResultAssociation();
                DataTable dtTestResults = dsResultSet.Tables[0];

                Dictionary<string, List<int>> testResultDictionary = new Dictionary<string, List<int>>();

                //Get the unique test codes from the datatable
                var testcodes = from t in dtTestResults.AsEnumerable()
                                group t by t.Field<int>("iTestCode") into testcode
                                select testcode;

                //populate dictionary with Test and results association
                foreach (var testcode in testcodes)
                    string testId = testcode.Key.ToString();

                    var results = from r in dtTestResults.AsEnumerable()
                                  where r.Field<int>("iTestCode") == testcode.Key
                                  select r.Field<int>("iResultCode");
                    testResultDictionary.Add(testId, results.ToList());         //List of result codes in the dictionary.

                StringBuilder strObservation = new System.Text.StringBuilder();

                // Create ORC and OBR Templates
                string strORCTemplate = xe.XPathSelectElement("//Observation/ORC_CommonOrder").ToString();
                string strOBRTemplate = xe.XPathSelectElement("//Observation/OBR_ObservationRequest").ToString();

                //create OBX with PDF and OBX without PDF.
                string strOBXwithPDF = xe.XPathSelectElements("//ObservationResults").
                                        Where(item => item.XPathSelectElement("OBX_ObservationResult/OBX_2_ValueType").Value == "ED")

                //Take the OBX with PDF and replace the elements using below statment to get OBXwithoutPDF
                XElement xeOBXwithPDF = XElement.Parse(strOBXwithPDF);
                                    ForEach(item =>
                                        item.XPathSelectElement("OBX_5_ObservationValue").Value = "$FileName$";
                                        item.XPathSelectElement("OBX_2_ValueType").Value = "ST";
                                        item.XPathSelectElement("OBX_3_ObservationIdentifier/CE_0_Identifier").Value = "IR";
                                        item.XPathSelectElement("OBX_3_ObservationIdentifier/CE_1_Text").Value = "IMAGE";
                string strOBXwithoutPDF = xeOBXwithPDF.ToString();

                XElement xeOBR;
                string strtestName;

                var last = testResultDictionary.Last();
                foreach (var item in testResultDictionary)

                    //1. Get the Templates and modify them accordingly
                    xeOBR = XElement.Parse(strOBRTemplate);
                    xeOBR.XPathSelectElement("OBR_4_UniversalServiceIdentifier/CE_0_Identifier").SetValue(item.Key);  //item.Key holds the test code.

                    strtestName = dtTestResults.Select("iTestCode=" + item.Key).FirstOrDefault().Field<string>("vchTestName");

                    //2. Append the template to sb.
                    strObservation.Append("<Observation xmlns=\"\">" + strORCTemplate);

                    //3. Get the Results from the XML belonging to Test code into the List.
                    string testcode = item.Key;
                    var query = from o in xe.XPathSelectElements("//ObservationResults")
                                from resultcode in item.Value  //item.Value is List of ResultCodes
                                where o.XPathSelectElement("OBX_ObservationResult/OBX_3_ObservationIdentifier/CE_0_Identifier").Value == resultcode.ToString()
                                select o;
                    //4. Join the list to get the bunch of ObservationResults beloging to Test code in context
                    strObservation.Append(String.Join("", query));

                    //5. Append the OBX without out PDF to all of them expect the last one.
                    if (item.Equals(last))


                //6. Replace the old Observation with new Observations.
                XElement xeObservations = XElement.Parse("<Root>" + strObservation.ToString() + "</Root>");     //Adding root to make the XElement parse the string.

                //7. Update OBR Index Number
                int iOBRCounter = 1;
                foreach (var observation in xeObservations.XPathSelectElements("//Observation"))


                //8. Replace the Observations

                // 9. Remove the dummy root element
                string strFinalXml = xe.ToString().Replace(@"<Root xmlns="""">", "").Replace("</Root>", "");

                //Convert to xmlDocument
                //XmlDocument xDoc = new System.Xml.XmlDocument();
                //xDoc.LoadXml(xe.ToString().Replace(@"<Root xmlns="""">", "").Replace("</Root>", ""));

                doc = new System.Xml.XmlDocument();
                //11. Update OBX index number
                UpdateOBXIndexNumber(doc, 1);
            catch (Exception ex)
                Ameritox.BTIS.Core.Components.Log.Info(trackingNo, hl7Id, "HL7Transform.cs :: Error occured in FormatSeperateORCOBR method\r\n" + ex.ToString(), "General", null);
                throw ex;