Using BizTalk 2006 to join records

Question

All

I have a problem I’m trying to solve with BizTalk and was hoping you would have some thoughts on the best way to solve it. I am loading in a flat file using a flat file adapter. This file contains records if a number of different types. Type 1 records are parent records. The rest of the types all link to the parent records using various foreign keys. I am looking to develop a transform that will take the message created by loading the flat file (with records of all types) and transform that to a message which is the result of joining all the records based on the foreign keys. Each record in the resulting message would therefore have columns from all of the record types as the message records will be the result of a join.

For example, the flat file might contain the following records:

* Type1
* Type1
* Type2
* Type2
* Type3
* Type4
* Type4

The message after the transform might contain records like this:

* Type1 columns, type2 columns, type3 columns, type4 columns
* Type1 columns, type2 columns, type4 columns

I have been looking into the options for achieving this but was hoping some people might have advice on the way to go.

Things I have tried are:

* Splitting the original message into messages with records of each type then using a multi-source map to join them up. I had difficultly achieving this as I wasn’t sure what functoids to use.
* Inserting the child records into a SQL database so that the additional columns in the parent records can be populated using SQL lookup functoids. This was a bit slower than I would have hoped and has the additional dependency of using a database.
* Using DTS to load the file, transform it into the format and writing out a file in the joined format for BizTalk to consume. This is simple and fast but isn’t using BizTalk.

Any advice on how to go forward to make the best use of BizTalk for this would be appreciated.

Cheers,

Andy.

Answer 1

This sounds more like an ETL problem. I'd suggest SSIS. I've used BizTalk for ETL in the past and it never ended well.

Answer 2

Maybe you could try to write a custom xslt for the mapping using the socalled the 'Muenchian method'. It's a way to sort xml input using xslt. The input in this case could be the xml representation of your flat file as provided by BizTalk.

See the Q2 2009 issue of HotRod magazine at http://biztalkhotrod.com/Documents/BizTalk_HotRod_Issue6_Q2_2009.pdf This issue also handles the subject of using custom xslt in BizTalk.