debatch big input flat file into smaller multiple output files with specific count

Question

I have a positional input flat file schema of the following kind.

<Employees>
    <Employee>
        <Data>

In mapping, I need to extract the strings on position basis to pass on to the target schema.

I have the following conditions -

If Data has 500 records, there should be 5 files of 100 records at the output location.
If Data has 522 records, there should be 6 files (5*100, 1*22 records) at the output location.

I have tried few suggestions from internet like

Setting “Allow Message Breakup At Infix Root” to “Yes” and setting maxoccurs to "100". This doesn't seem to be working. How to Debatch (Split) a Flat File using Flat File Schema ?
I'm also working on a custom receive pipeline component suggested at Split Flat Files into smaller files (on row count) using Custom Pipeline but I'm quite new to this so it's taking some time.

Please let me know if there is any simpler way of doing this, without implementing the custom pipeline component.

I'm following the approach to divide the input flat file into multiple small files as per condition and write at the receive location, then process the files with native flat file dissembler. Please correct me if there is a better approach.

If each record is one line, then your option 1 should work. What is it doing instead? — Johns-305, Feb 04 '19 at 14:06
@Johns-305 He doesn't wan't to have 1 record per output, he wants to re-batch it into 100. — Dijkgraaf, Feb 04 '19 at 19:48
@Dijkgraaf I could be mis-remembering this but I think I've used MaxOccurs > 1 before. — Johns-305, Feb 04 '19 at 20:10

score 1 · Answer 1 · answered Feb 04 '19 at 20:13

You have two options:

Import the flat file to a SQL table using SSIS.
Parse the input file as one Message, then map to a Composite Operation to insert the records into a SQL table. You could use in Insert Updategram also.

After either 1 or 2, call a Stored Procedure to retrieve the Count and Order of messages you need.

score 0 · Answer 2 · answered Feb 04 '19 at 19:47

A simple way for a flat file structure without writing custom C# code is to just use a Database table. Just insert the whole file as records into the table, and then have a Receive Location that polls for records in the batch size you want.

Another approach is called the Scatter Gather Pattern, in this case you do set the Occurs to 1 which will debatch into individual records, and you then have an Orchestration that re-assembles it into the batch size you want. You will have to read up about Correlations Sets to do this.

debatch big input flat file into smaller multiple output files with specific count

2 Answers2