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 tothe 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
— =============================================
CREATEPROCEDURE [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