BizTalk Varying Length Flat File using Single Schema for Transform

325 views Asked by At

I have a pipe delimited .txt Flat File that I'm using to do bulk insert to SQL. Everything works well for straight one to one. However, the Flat File now contains 2 new fields that can repeat an unknown number of times.

Is there a way to create a single flat file schema where I can have an unbounded child within the main unbounded child? I think the place I'm getting tripped up is how to make the ChildRoot listed below just a "group heading" like Root is where ChildRoot doesn't correspond to a location in the flat file. How do I insert something like that?

Schema:
-Roots
--Root (unbounded)
---ChildID
---ChildName

Roots gets a direct link to my sql stored procedure to do a bulk insert on as many "Root" rows that come in.

Now I have:

Schema:
-Roots
--Root (unbounded)
---Child
---ChildName
---ChildRoot (unbounded)
----ChildRootID
----ChildRootName

**EDIT I should also add that ChildRootID & ChildRootName can repeat an indefinite number of times until the row delimiter (carriage return) is found

0

There are 0 answers