I am running a data transformation that enters data into two tables using a multicast. The problem is that one table is the parent of the other, and therefore the parent must be populated first. However a multicast transoformation executes both table inserts at the same time. Is there a way to specify an execution order at the multicast level? BTW - I can't do the obvious solution of creating two data flow tasks at the control level because the data source in an XML file and I am using the dynamically generated element ids as my primary and foreign keys.

Maybe try this: Have the one Data Flow read the XML file and generate all the necessary keys to maintain the foreign key relationships between parent and child, but instead of sending to a multi-cast, send to a Raw File destination. Now have two more Data Flow tasks, each one reads that same Raw File (Source this time) and, I assume filter out records that are parent or child accordingly, and write to the correct table. First Data flow of this type would populate the parent table. Then when you read the Raw file a second time, you populate the child table. When finished delete the Raw file with a File System task. FYI: Raw file is a SSIS proprietary format and is supposed to be pretty fast.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

One more solution could be: Populate the parent table and a staging table in first data flow task and use an execute sql task (after the data flowt task) to do an insert into the child table using the staging table. For ex: Insert Into Child table Select * from staging table. The staging table has to be created and dropped within the package itself.Nitesh Rai- Please mark the post as answered if it answers your question

Short answer - no. You cannot specify the execution tree or path of the multicast. You have to have 2 data flows.. Todd offers a viable solution and RAW files are very fast.Please 'Mark as Answer' if found helpful - Chris@tier-1-support

I think you only need two data flows in this scenario, Todd. Use the first data flow to get the keys generated, etc, then multicast to a OLE DB Destination to insert the parent table, and to a RAW file destination. Then the second data flow can read the RAW file source, and write to the child table. I think Todd's approach works fine here, but I recently posted a couple of slightly different patterns for this here: http://agilebi.com/jwelch/2010/10/29/insert_parent_child_pattern2/ and here: http://agilebi.com/jwelch/2010/10/29/insert_parent_child_pattern1/. They might be worth looking at, to see some alternative approaches. But again, this would be more informational in natural - Todd's suggestion is the same way I'd probably handle this one.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

Excellent information gentlemen. I ended up using a RAW file source to store child data to be transformed later. Thank you all. Eric