Best approach to import customer master data from an ERP system.

(0) Share

Report

Posted on by Community Member

What is the best way to create an ODBC connection to a SQL db and have it pull customer master table to AX custTable without disrupting the source database table?

Please take a note of the following first:

1. Customer master table is in SQL DB that current ERP system is using.

2. My project is not a whole ERP system conversion. I'm working on a project that requires me to import Customer Name and Customer Account ID. However, the excel file with customer name and ID records that I've got is not up-to-date so I would like to pull customer master table. Our goal is so that going forward we have data integrity as well as live update on customer master table between our ERP system AX.

Running AX 2012.

Thanks in advance!

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (4)

Verified answer

Community Member on at

Like (0)

Report

RE: Best approach to import customer master data from an ERP system.

I would like to share my finding based on all the input I've got on this thread. Hope this will help somebody else out there.

Using this link as a reference, <Walkthrough: Import data from an ODBC data source (DIXF, DMF) [AX 2012]>, there are few crucial first steps along the way that must be followed:

1. If you already have another SQL server DB with customer master table then you don't have to run a new query to create new database. Go to Admin Tool> select ODBC connection> Created a new User DSN for SQL Server> from here on make sure that you know the name of SQL server (where customer master table is) along with database name and table name.

2. Lastly, when you are at defining processing group step, make sure to type in correct table name in query box. For example, select * from "your source table name".

3. Follow all the steps listed in the above link.

Thanks everyone for your contribution :)

Was this reply helpful? Yes No
Verified answer

Martin Dráb 236,184 Most Valuable Professional on at

Like (1)

Report

RE: Best approach to import customer master data from an ERP system.

You just must be able to connect to your source database (the database containing data for import) through ODBC. It definitely doesn't have to be on the same database server as the AX database.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Best approach to import customer master data from an ERP system.

Does it matter if I create database "DMFLegacyDB" in SQL server that is connected to AX?

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 236,184 Most Valuable Professional on at

Like (0)

Report

RE: Best approach to import customer master data from an ERP system.

Walkthrough: Import data from an ODBC data source (DIXF, DMF) [AX 2012]

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Best approach to import customer master data from an ERP system.

Martin,

How would I do this?

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 236,184 Most Valuable Professional on at

Like (3)

Report

RE: Best approach to import customer master data from an ERP system.

I back others suggesting DIXF, nevertheless I would like to add that you can use it to connect to the SQL Server database directly; you don't have to export the data to files.

Was this reply helpful? Yes No
Verified answer

Faisal Fareed 10,796 User Group Leader on at

Like (0)

Report

RE: Best approach to import customer master data from an ERP system.

Hi,

The easiest and quick way to import customers from your legacy system which you have in excel file is through Data Import Export Framework. You need to install this framework model file first into you environment if you haven't yet. This module comes with Microsoft Virtual Machine for AX 2012 R2 CU7 to onwards.

First you need to generate template from DIXF framework as this framework accept particular form to read from. Then you can copy and paste your data from your excel file to this generated excel format file from DIXF and then good to go with import using the link suggested by Baber.

Was this reply helpful? Yes No
Verified answer

syed baber 11,633 on at

Like (0)

Report

RE: Best approach to import customer master data from an ERP system.

Hi Lhamo,

You can use DIXF (Data import/export framework) to import customer master data in AX using csv or Excel import file. You can also schedule a batch job to read the import file from the folder to import or update customer master data on periodic basis. You can refer to the below link on how to import customer data in AX using DIXF:

daxusers.wordpress.com/.../import-customer-master-data-through-data-import-export-framework-in-ax-2012

The underlying data model is quite complex as there are many tables involve due to the global address book framework and when you use DIXF, the framework will take care of that and populates the data in correct tables while maintaining the relations.

Please let me know if you have further queries or need any help in this regard.

Thanks,

Baber.

Was this reply helpful? Yes No