Mar 202007
 

From within an Orchestration, there often times occurs the need to retrieve the original filename and/or the archived filename in the %documentshome% directory. The following stored procedure can be run from within the orchestration that consumes the messages that the BaseEDI adapter sends to BizTalk. All you have to do is create the call to the stored procedure and pass it the BTS.MessageID.

In the expression to create the call to the stored procedure looks like this:

 

tempXML = new System.Xml.XmlDocument();

tempXML.LoadXml(“<ns0:Request xmlns:ns0=\”http://GetArchiveFileName\”><ns0:getarchivefilename MessageID=\””+ OrderMsg(BTS.MessageID)+”\” /></ns0:Request>”);

SprocRequestMsg.Request=tempXML;

 

The actual stored procedure needs to be installed in the BaseEDI database:

 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

— =============================================

— Description: Returns the EDI file ID given the MessageID context property in Biztalk

— =============================================

CREATE PROCEDURE [dbo].[getarchivefilename]

@MessageID VARCHAR(255)

AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN

SET NOCOUNT ON;

SELECT icin as archive_file_name,origfilename as originalfilename 

FROM audin

WHERE (ref =

(

        SELECT Replace([MessageInstance/InstanceID],’-‘,”)

        FROM <server name>.BiztalkDTADb.dbo.dtav_MessageFacts

        WHERE ([ServiceInstance/InstanceID] =

                (SELECT [ServiceInstance/InstanceID]

                FROM <servername>.BiztalkDTADb.dbo.dtav_MessageFacts

                WHERE ([Event/Direction] = N’Send’) AND ([MessageInstance/InstanceID] = @MessageID))) AND

(    [Event/Direction] = N’Receive’)

)

)

for xml auto

END