CODEWITHSUNDEEP
c#visual-studio-2010axaptadynamics-ax-2012
NewbieAX
NewbieAX

Reputation: 31

How to transfer data from a SQL Server database to AX 2012?

I want to transfer HUGE amounts of data from SQL Server 2008 to Microsoft Dynamics 2012 using C#. There is master data as well as relational data. Which is the best method to do this?

Can we maintain the foreign key relationships if we use the .Net Business Connecter?

Upvotes: 3

Views: 2030

Answers (2)

Kenny Saelen
Kenny Saelen

Reputation: 894

I totally agree with Jan on this one...

First, Data Migration Framework for now would NOT be the weapon of choice here if you need to transfer HUGE amounts of data for the following reasons:

  • It is beta and by that it still contains lots of bugs (for example when creating custom entities on a custom table that does not have the right clustered and primary index setup, the import of your data is not correct, it updates the wrong records, ...)
  • Secondly, the first step of the process is loading your data into a staging table by using SQL Server DTS packages. The package being built by Dynamics also contains validation which has it's impact on the performance
  • Next, for every record that will be read, the mapping metadata is retrieved instead of caching when reading the same type of records.

So I think you have two options here:

  • You could use the same architecture more or less as the DMF and create a staging table. Then use the SQL Server Data Export/Import wizard to import the data FAST (no validation, ...) and afterwards process the staging to real life tables (But as Jan said, that would require Ax knowledge)

  • Lastly what you could do (depending on the complexity of the data you want to import) is using a AIF Custom Service (WCF). You could expose a service with an operation that creates a record in Dynamics Ax. The benifit here is that you could use parallel processing here to speed up things. For example, at a customer site, we have BizTalk in the middle of all the traffic and BizTalk is able to perform many calls in parallel to Dynamics AX. And if your environment is Multi-AOS, it can scale up nicely.

Upvotes: 0

Jan B. Kjeldsen
Jan B. Kjeldsen

Reputation: 18051

Take a look on the Data Migration Framework User Guide, install guide, usage guide, demo files.

Background info ...

This is not a fast track, but the alternative would require a 100 percent understanding of the AX data model.

AX foreign key relationships are stored in the AX data dictionary but are not populated to the SQL Server database.

If your data happens to be an AX 2012 database then I would recommend a backup/restore :)

Upvotes: 3

Related Questions