Using BAM APIs for Tracking

Sometimes, in BizTalk development, a developer needs to develop an out of the box solution for message tracking, etc. Even though the BizTalk Server itself provides an excellent tool such as BAM for Business Analyst (precise to say) and HAT for developer. We encountered the same kind of problem where client wanted us to develop such kind of custom solution to display the message body and message flow.
I will be discussing about the details as how to use BAM APIs and message tracking databases used to display the message and its flow. It also details about the message needs to tracked in the Tracking database. I would be dividing this in three parts.
Enabling Tracking on BizTalk Send and Receive Ports in BizTalk Administration Tool
Developers has to make sure that Tracking has been turned on the Send and Receive ports. This step is done to track the messages and as a result of the this message is inserted in the Tracking Database along with their instance id and activity ids.
Enabling Tracking on the Receive Port
       Go To BizTalk Server Administration --> Receive Ports Properties ---> Tracking
       Check the options Track Message Bodies and Track Message Properties
Enabling Tracking on the Send Port
       Go To BizTalk Server Administration --> Send Ports Properties ---> Tracking
       Check the options Track Message Bodies and Track Message Properties.
Database Tables Involved
The developer must also needs to be aware of the tables involved. I would just like to tell about the below two tables who stores the data related to message flow and message body.
The dta_ServiceInstances, Tracking_Parts1 and Tracking_Parts2 tables
BizTalk Server Micorsoft.BizTalk.Operations class and their methods
When I worked with the BizTalk Server 2002, the messages were track based on the Interchange Id but in the Biztalk server 2004 and onwards the database model of the BizTalk Databases has been totally chaged.The BAM APIs helps you a lot to track these messages. Below are the few APIs which will help you to do the same. You need to use Microsoft.BizTalk.Operations class which has below methods to get the routing details, message flow details etc. This DLL is placed in C:\\Program Files \ BizTalk Server 2006.
  • GetMessage
  • GetMessageFlow
  • GetRotingDetails
  • GetServiceInstance
  • GetTrackedMessage
Apart from this also has few very important methods but they are not of our use in this context.
  • ResumeInstance
  • SuspendInstance
  • TerminateInstance
This DLL could be located as below, in case if you need to develop and out of the Box application you must have the License for the same. Below is the look of this DLL in ILDASM. This is an cool tool provided with Visual Studio which allows you to look at the methods, properties, metadata etc. of the class.
In order to track the message the you need to enable to tracking of the message only then it will go to the tracking database. Other thing is you should be aware of the database structure of the BizTalk Tracking database. Below is the look at the tracking databases. The database name is BizTalkDTADB and the TrackingParts1 and TrackingParts2. Apart from this these are two tables which a developer must look into while developing such kind of solutions.
 

BizTalk EDI Development : EDI Schema not deployed.

You must need to deploy the standard EDI schema against which you need to receive your EDI document. Most of the time in the initial stage of the EDI developments, developer assume that just by adding the edireceive pipeline and configuring the party is suffiecient but this is not the case. I will be detailing about this scenario. If the schema is not deployed you will receive below error messages.

--------------------------------------------------------------------------------------------

An output message of the component "Unknown " in receive pipeline "AS.MS_BTS.Common.Pipeline.EDI.EDIReceiveAS.EDIReceiveAS, AS.MS_BTS.Common.Pipeline.EDI.EDIReceiveAS, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5753f508dd524570" is suspended due to the following error:
     Unknown .
 The sequence number of the suspended message is 1.

---------------------------------------------------------------------------------------------

If you look at the error in more details by double clicking on the Service Details under the BizTalk Group page and browse to the Context properties and try to see the value of Message Type, you will see the below information against the Message Type.

http://schemas.microsoft.com/Edi#WildcardSchema

This value as WildcardSchema shows that the related schema has not been deployed. Once the schema is deployed you should see the MessageType value as below which tells that message type of the received EDI message is X12 version 00401 and 990.

http://schemas.microsoft.com/BizTalk/EDI/X12/2006#X12_00401_990

Also if you look at the message it will come like as below before deploying the schema as the EDI disassembler component has not been able to resolve the EDI message.

<UnrecognizedSchema>

<UnrecognizedSegment>ST*990*1300001</UnrecognizedSegment>

<UnrecognizedSegment>B1*RWIT*164428*20091028*A</UnrecognizedSegment>

<UnrecognizedSegment>N9*CN*0635670</UnrecognizedSegment>

<UnrecognizedSegment>SE*4*1300001</UnrecognizedSegment>

</UnrecognizedSchema>

So make sure that you deploy the required schema. Please refer below in the Post to see How to develop First BizTalk EDI project.

 

 

 

Writing Custom Pipeline component to modify the source file name.

In BizTalk EDI application I needed to have custom file name of the destination file and I end up writing a custom component. I could have used orchestration to modify the "ReceivedFileName" but somehow it was not working. May be becasue of EDI Encode component but that should not be the case. Any way.. the other thing was I also needed to add the seuence of the file to the FileName. I found that BizTalk databases does not store the sequence of the received file so either I needed to add some tracking info or I should use some application configuration values. I chose to use application configuration value. I needed to get the file name as "SO_Date_Sequence". Below is the code for the same.

#region IComponent interface public methods

        //public bool isRecordSequence = true;
        public IBaseMessage Execute(IPipelineContext context, IBaseMessage message)
        {
           
           
            try
            {

                string customSourceFileName = RecordSequence();
                    IBaseMessageContext messageContext = message.Context;
                    messageContext.Write("ReceivedFileName", "http://schemas.microsoft.com/BizTalk/2003/file-properties", customSourceFileName);
                               


            }
            catch (Exception ex)
            {
                if (message != null)
                {
                    message.SetErrorInfo(ex);
                }
                throw ex;
            }
            return message;
        }

        #endregion
        #region IComponent interface private methods

        private string RecordSequence()
        {
            string filename;
            try
            {
                //Get the current date
                string currentDate = System.DateTime.UtcNow.Date.ToString("MMddyyyy");

                //Read the xml file
                XmlTextReader reader = new XmlTextReader("C:\\BT Projects\\BungeeTemp\\Sequence_output.xml");
                XmlDocument xdoc = new XmlDocument();
                xdoc.Load(reader);

                //Close the reader
                reader.Close();

                //Get the current count
                int count = Convert.ToInt32(xdoc.SelectSingleNode("//SequenceCount").InnerText);

               
                    if (xdoc.SelectSingleNode("//EDIReceiveDate").InnerText == currentDate)
                    {
                        count++;
                        xdoc.SelectSingleNode("//SequenceCount").InnerText = count.ToString();
                    }
                    else
                    {
                        xdoc.SelectSingleNode("//EDIReceiveDate").InnerText = currentDate;
                        xdoc.SelectSingleNode("//SequenceCount").InnerText = "1";
                    }

                    xdoc.Save("C:\\BT Projects\\BungeeTemp\\Sequence_output.xml");
                    //isRecordSequence = false;

                    filename = "SO_" + currentDate + "_" + xdoc.SelectSingleNode("//SequenceCount").InnerText;
                    return filename;
                }               
                           
            catch (Exception ex)
            {
                throw (ex);
            }

        }

        #endregion

Cheers,

Developing First BizTalk EDI Project

If you need to start on BizTalk EDI Development, you can refer below articles. If you face any issue please do write to me.

BizTalk EDI Development Part 1:

http://geekswithblogs.net/VishnuTiwariBlog/archive/2009/10/21/develop-your-first-biztalk-edi-project---part-1.aspx

BizTalk EDI Development Part 2:

http://geekswithblogs.net/VishnuTiwariBlog/archive/2009/10/21/develop-your-first-biztalk-edi-project---part-2.aspx

BizTalk EDI Development Part 3:

http://geekswithblogs.net/VishnuTiwariBlog/archive/2009/10/22/develop-your-first-biztalk-edi-project---part-3.aspx

Cheers,


How to concatenate and return row values in single result field.

Recently I encountered a problem wherein I need to concatenate and return the row values in single field. I was getting the results as below in rows and I needed to return the concatenated values of these row values.

Results
Load Number Temperature Code Purchase  Order Number
141421 F PO1
141421 D PO2
141421 S PO3

In fact I was not having any column like LoadNumber which could have worked as ID and the implementation should have been a bit easy.

I am mentioning two different solutions below. On works fine in case of standalone query and the other one is very straight forward. I would suggest to follow the second one. But it all depends on you requirement as what and where you need to implement this. I believe both the below mention queries should be helpful.

FIRST QUERY:

DECLARE @MYXML XML
SET @MYXML= (SELECT ( SELECT DISTINCT TemperatureCode
                               FROM Results WITH(NOLOCK) where 'Mention the condition'
                               FOR XML Path('TEMPPROT'), BINARY BASE64,TYPE)

SELECT (
                   Isnull(a.b.query('/TEMPPROT/TemperatureCode').value('/TemperatureCode[1]','varchar(5)'),'')
                   +''+
                   Isnull(a.b.query('/TEMPPROT/TemperatureCode').value('/TemperatureCode[2]','varchar(5)'),'')
                   +''+
                   Isnull(a.b.query('/TEMPPROT/TemperatureCode').value('/TemperatureCode[3]','varchar(5)'),'')
                   +''+
                   Isnull(a.b.query('/TEMPPROT/TemperatureCode').value('/TemperatureCode[4]','varchar(5)'),'')
                   +''+
                   Isnull(a.b.query('/TEMPPROT/TemperatureCode').value('/TemperatureCode[5]','varchar(5)'),'')
  
                )
AS 'ConcatenatedTemp' 
FROM @MYXML.nodes('/TEMPPROT/TemperatureCode') a(b)

 

SECOND QUERY:

SELECT (SELECT distinct TemperatureCode + ''
                  FROM Results WITH(NOLOCK) where 'Mention the condition'
                  FOR XML Path(''), BINARY BASE64,TYPE)
                  )
AS ConcatenatedTemp