Sunday, December 30, 2007

Finding the Original Filename Processed by BizTalk Server

A common BizTalk implementation starts with business partners (customers, vendors, etc.) transmitting files to you via FTP in their own file format. Then BizTalk transforms that file into your company's standard XML format. Next, your existing systems process that XML file as normal. This simplifies application architecture, because for each new business partner you simply need to add an FTP site, a BizTalk Document Specification, a BizTalk Map, a BizTalk Port, and a BizTalk File Receive Function. While it seems like a large number of items that need to be created for each new business partner, it is much, much easier than creating a new application for each new business partner. The combined time to create all of these new items should be 5 - 30 minutes, depending on how complex the documents are.
If you have a business partner that sends information in the filename (e.g., Check12345.txt) that you need during your processing, you will need to perform a not-so-obvious step, that is not well documented, in order to find out the name of the file sent in by the business partner.

Where Does BizTalk Store Original Filenames
BizTalk tracks the files that it processes in its database (InterchangeDTA). If you have BizTalk write the transformed file out as file%tracking_id%.xml then you will end up with a filename like this: file{544191B6-6755-4AF7-8B82-C53C24B2A2D4}.xml. You can easily parse the Tracking GUID out of this.

Once you have the Tracking GUID, which is the unique identifier that BizTalk uses internally, you can query the InterchangeDTA database for the original name of the file. The query joins four tables:
      Select data.nvcOriginalFileName as Filename
      From  dta_indoc_details inDoc,
            dta_outdoc_details outDoc,
            dta_interchange_details details,
            dta_interchange_data data
      Where outDoc.nInDocKey = inDoc.nInDocKey and
            inDoc.nInterchangeKey = details.nInterchangeKey and
            details.nInterchangeDataKey = data.nInterchangeDataKey and
            outDoc.uidTrackingGUID ='{544191B6-6755-4AF7-8B82-C53C24B2A2D4}'

And there you have it. A (somewhat) simple query will return the original filename from the Tracking GUID.