Best practice for adding a header record and then child records

Question

Best practice for adding a header record and then child records

MikeRen

Ten Centuries

Points: 1295
More actions
July 14, 2022 at 10:45 am

#4063202

Hi,
I'm migrating sales data from one ERP into D365 via SSIS and wanted to know if there was a best practice on how to configure the package so that I can take a header record from my ERP, create the corresponding header record in D365, then get the ERP detail records and create them in the D365 environment so that they are details of the header record I just created there?
I must be missing something as I'd have expected to have to loop through the ERP headers one at a time, create the D365 header and in doing so get some key value to be able to apply that to the D365 detail records as a foreign key, but I can't see how to do that in SSIS.
Any pointers please?
Ta

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Presumably your ERP already has the relevant PK/FK information in it, so why can't you just use that? Or are you generating new keys as part of the migration?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

are you using any specific connector for D365 or are you doing straight SQL inserts/updates? if SQL then instead of trying to process that in SSIS load both headers and details onto staging tables and then use sql to generate the new headers, outputing the generated keys if required, and use those to do the detail inserts.

SSIS can also be done with lookups but bound to be slower and harder for you to verify that you are indeed getting the correct values.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

I believe the previous answers are the better options - but if you are stuck with doing this in SSIS and need to query the header data, then query for the detail data it can be done in SSIS.

I would start with a data flow that gets the header data from the ERP system (source) and send that to a recordset object and to D365 (multicast). Then you can use the recordset object as a source in a for each loop - with a data flow in the for each that uses the key(s) from the header as parameters to the source query and output that flow to the details table.

Without more information though - not sure if that is what you are looking for.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

MikeRen Ten Centuries Points: 1295 More actions · Answer 4

Phil, yes my ERP has the PK/FK information but D365 generates new ones when creating its records (my ones get saved into a separate field for reference). I think that I'd somehow have to grab these new keys after the records are created to be able to tie them all together and I'm not sure how that could happen without performing a read of the D365 header data after writing it and retrieving the old ERP key to enable me to write the detail lines.

Frederico, I'm using the Kingsway Soft connector to push the data into D365. I grab the header and detail information from a staging table (populated via SQL), push that into the connector and that generates the new records in D365.

Jeffrey, this sounds promising. Do you have any links to where this is demonstrated? My only concern is that having written to D365 I wouldn't have the new key, but perhaps I can add an additional step to query the data from D365 to get that information and use that to populate the recordset.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

MikeRen wrote:

Phil, yes my ERP has the PK/FK information but D365 generates new ones when creating its records (my ones get saved into a separate field for reference). I think that I'd somehow have to grab these new keys after the records are created to be able to tie them all together and I'm not sure how that could happen without performing a read of the D365 header data after writing it and retrieving the old ERP key to enable me to write the detail lines.

OK, here is one way, using SSIS. Not superbly elegant, but gets the job done.

Import the header data, including the old PK, to your new system.
Import the detail data, using a lookup within your dataflow to retrieve the new FK. The lookup should contain a cache of all the (OldPK, NewPK) combinations from the new header table.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

MikeRen Ten Centuries Points: 1295 More actions · Answer 6

Thanks Phil,

As you say, it's not elegant, but it seems to work for me. I was checking if there was a better way, but this does the job so i'll go with that. Thanks for your assistance.