BizTalk SOA Evangelist

• GetMessage
• GetMessageFlow
• GetRotingDetails
• GetServiceInstance
• GetTrackedMessage

• ResumeInstance
• SuspendInstance
• TerminateInstance

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.

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);
                               

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,

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.

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