Build file with parent and child records

I need to build a file for our FSA provider that includes header and detail rows for participating employees. We are on v10 and still have access to directly query the database, so I'm likely to do this with SQL Server Integration Services. I'd like to consider using IPA, but I'm not sure how to mesh separate queries for the differing record types.

Find more posts tagged with

Process Automation (IPA) - General Discussions

Accepted answers

jreese

rather than use assign nodes and writing out the results every time to the file access node, use the MsgBuilder node, with the same variable name to write out each header record and detail record and then at the end of the query use the file access node to write out the data stored in the MsgBuilder.

All comments

unknown

We use SQL. Query EMPLOYEE, PAEMPLOYEE, BENEFIT UNION EMPLOYEE, BENEFIT, HRDEPBEN and EMDEPEND tables. Interesting you would need to include dependent information for FSA. That is not typical.

rm_benefit_administration_aca_training.pptx

unknown

There are a couple of ways to mesh separate queries. If you had one query that pulled the employees you could have that information write to a file (header), then perform other queries/writes within the initial SQL loop. It completes the write for all the data of one individual then starts on the next employee.

brian-dittman

You're right; I didn't mean parent/child records literally. I meant in a generic primary/secondary sense but should have been more precise, especially in the context of HR data.

brian-dittman

Thanks, Kyle. So that has me thinking:

> query node for header

> assign node to place data in header row variable (e.g. vRec1)

> inner query for detail record

> assign node to place data in detail row variable (e.g. vRec2)

> file access node after exiting inner loop to append header and details rows and then loop back to beginning of outer query

jreese

unknown

jreese has a great point here. This is also much faster. If you think in terms of speed and efficiency, each time that file is accessed, the flow will have to check security for permissions to the folder/location of the file and then write to the file. It is much more efficient to store everything in the MsgBuilder.

brian-dittman

Thanks. I'll try that. I see that the file access node has an explicit append selection, whereas the message builder does not. Is it implied that iterative results from the query append to the message variable rather than overwrite, thus negating the need for assign nodes?

brian-dittman

Thanks to all for the great advice. Much appreciated!

Copyright © 2025 Infor. All rights reserved.