Geeks With Blogs
Evan Koch Musings on BizTalk Server and SQL Server

When dealing with existing systems, sometimes a challenge presents itself in the form of a flat file.   Trying to impose structure upon a flat file can be achieved, however, using the Table Looping and Table Extractor functoids.  Consider the following schemas:

Source schema:
 

Target schema:
 

One’s first attempt at a map to transform the source to destination might look something like this:
 

We’ll use the following input file to test the map.
 

The output isn’t quite what we were hoping for.
 

The Table Looping functoid is the key to what we’re trying to achieve.  Below is the map that uses the Table Looping and Table Extractor functoids to create the desired output.
 

The Borrower fields are used as inputs into the Table Looping functoid as well as some definitions about how many rows and columns there will be.
 

By opening up the Table Looping Grid, we’re able to define what fields will go into certain columns/rows:
 

The Table Extractor functoids are used to define which columns from the table map to use as inputs.  Each Table Extractor functoid corresponds to a column within the Table Looping Grid.
 

And finally the output from Table Looping functoid to the Borrower node dictates that a Borrower node be created for each row within the Table Looping Grid.  With that said, here’s the output from testing the second map:
 

 Source code for this example can be found here.

Posted on Tuesday, August 7, 2007 6:06 PM | Back to top


Comments on this post: BizTalk Maps and the Table Looping/Table Extractor Functoids

# re: BizTalk Maps and the Table Looping/Table Extractor Functoids
Requesting Gravatar...
Great Article!
The first EASY to read/understand I've seen online!

I'm getting the error:
Warning 4 A first input of the Table-Looping functoid must be a link from a Source Tree Node which acts as the scoping parameter.

my vars are:
2
13
...26 values from my source schema...


What could I be doing wrong?

Thanks in advance!
Left by Shahbaz Mirza on Mar 10, 2008 4:45 PM

# re: BizTalk Maps and the Table Looping/Table Extractor Functoids
Requesting Gravatar...
Shahbaz,
If can email your SSIS package and source files to me, I'll take a look at it. Please email them to evankoch23ATgmailDOTcom.

Thanks,
Evan
Left by Evan Koch on Mar 11, 2008 9:04 AM

# re: BizTalk Maps and the Table Looping/Table Extractor Functoids
Requesting Gravatar...
Evan, did you ever resolve Shahbaz's error? I'm getting the same thing myself, and while it's just a warning, I'm mystified.
Left by Paul Brazelton on Aug 18, 2008 1:17 PM

# re: BizTalk Maps and the Table Looping/Table Extractor Functoids
Requesting Gravatar...
Hi Paul\Shahbaz

Shahbaz is getting a warning not an error. Warning is due to hardcoding the scoping parameter i.e first parameter as 2.
Left by Pradeep on Oct 12, 2008 2:43 AM

# re: BizTalk Maps and the Table Looping/Table Extractor Functoids
Requesting Gravatar...
Amazing article. Explain looping crystal clear.
Left by AShok on Mar 06, 2009 6:50 PM

# re: BizTalk Maps and the Table Looping/Table Extractor Functoids
Requesting Gravatar...
Wonderful Article, Helped me a lot.
Left by SAI on Dec 16, 2009 10:02 AM

# re: BizTalk Maps and the Table Looping/Table Extractor Functoids
Requesting Gravatar...
Is this still an active thread?

I have a source doc with a looping PID segment. I need to map to a segment on the target. The trick is there can be 1-4 PID segments in the souce, but I ALWAYS need 4 output segments. so if there were 2 PIDs in the source, then I would output those two and then just iterate 2 more to complete the necessary 4 output records. I assumed table looping would be the answer, but not sure now.

Thank you
Left by Frank C on Jan 14, 2010 4:25 AM

# re: BizTalk Maps and the Table Looping/Table Extractor Functoids
Requesting Gravatar...
hi,
can u please tell me how to avoid that warning occur while test maps
Left by AboorvaRaja on Jun 01, 2010 5:51 PM

# re: BizTalk Maps and the Table Looping/Table Extractor Functoids
Requesting Gravatar...
hi frnds,
Nothing will affect in the project if the warning comes while deploying the project........ pls reply to my mail aboor18@gmail.com
Really its a amazing article it help me a lot...
like this i want for all the functoids can any one help me......
pls mail to me is der any solution.
Left by AboorvaRaja on Jun 01, 2010 7:27 PM

# re: BizTalk Maps and the Table Looping/Table Extractor Functoids
Requesting Gravatar...
This article really helped a lot. I have been strugling for two weeks trying to get the segments to loop. This article and some experimentation solved my problem. Can this table extraction be used to get different extractions based on a condition. I have a condition, when true, I need two outputs and when false, I need four outputs.
Left by Fred Johnson on Apr 24, 2012 2:25 AM

Your comment:
 (will show your gravatar)


Copyright © Evan Koch | Powered by: GeeksWithBlogs.net