July 14, 2022 at 10:45 am
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
July 14, 2022 at 3:49 pm
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 14, 2022 at 3:59 pm
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.
July 14, 2022 at 10:15 pm
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
July 15, 2022 at 6:45 am
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.
July 15, 2022 at 8:26 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 22, 2022 at 7:11 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply