AX 2012 | Import Data From an ODBC Through DIXF

Microsoft Dynamics AX 2012 Data Import/Export Framework provide the functionality to import data from an ODBC data source into Dynamics AX. In this post we will illustrate how to import Vendor data from MS SQL database into AX. First we will create sample database that contain vendors data. Then create an ODBC data source, Set up DIXF to import the data to the vendor entities.



To do that please follow the steps below:

1- Open Microsoft SQL Server Management Studio

2- Run the query below to create a database (DIXFDB), create a table (VendorEntity), and populate the table.

USE [master]GO/****** Database [DIXFDB] ******/CREATE DATABASE [DIXFDB] 
GO
USE [DIXFDB]
GO/****** Table [dbo].[VendorEntity] ******/CREATE TABLE [dbo].[VendorEntity]([ACCOUNTNUM][nvarchar](20)default(N'') NOT NULL,[FIRSTNAME][nvarchar](25)default(N'') NOT NULL,[MIDDLENAME][nvarchar](25)default(N'') NOT NULL,[LASTNAME][nvarchar](25)default(N'') NOT NULL,[LANGUAGEID][nvarchar](7)NOT NULL,[VENDGROUP][nvarchar](20)NULL,[CURRENCY][nvarchar](10)NULL,[PartyType][nvarchar](10)NULL) ON [PRIMARY]
GO
INSERT [dbo].[VendorEntity] ([ACCOUNTNUM], [FIRSTNAME], [MIDDLENAME], [LANGUAGEID], [LASTNAME], [VENDGROUP], [CURRENCY], [PartyType]) VALUES (N'V001', N'001 first', N'001 middle', N'en-us', N'001 last', N'10', N'USD', N'Person')
INSERT [dbo].[VendorEntity] ([ACCOUNTNUM], [FIRSTNAME], [MIDDLENAME], [LANGUAGEID], [LASTNAME], [VENDGROUP], [CURRENCY], [PartyType]) VALUES (N'V002', N'002 first', N'002 middle', N'en-us', N'002 last', N'20', N'USD', N'Person')
INSERT [dbo].[VendorEntity] ([ACCOUNTNUM], [FIRSTNAME], [MIDDLENAME], [LANGUAGEID], [LASTNAME], [VENDGROUP], [CURRENCY], [PartyType]) VALUES (N'V003', N'002 first', N'003 middle', N'en-us', N'003 last', N'20', N'USD', N'Person')
INSERT [dbo].[VendorEntity] ([ACCOUNTNUM], [FIRSTNAME], [MIDDLENAME], [LANGUAGEID], [LASTNAME], [VENDGROUP], [CURRENCY], [PartyType]) VALUES (N'V004', N'002 first', N'004 middle', N'en-us', N'004 last', N'20', N'USD', N'Person')

3- To preview the table data, select The VendorEntity table > Right Click > Edit Top 200 Rows.

4- four vendor records was inserted in the table. 

5- Open Control Panel > Administrative Tools >  Data Source (ODBC). Click Add to create New ODBC.

6- Select the SQL Server driver then click finish.

7- Enter the ODBC Name, Description then select the Server. Click Next

8- Select how the SQL should verify the authenticity of the login ID. Click Next

9- Select the database which we created in step 2 as default Database. Click Next

10- Click Finish

11- Test Data Source.

12- Click OK.

13- Open AX then Go To DIXF > Setup > Source data format >

•  Click New
•  Enter the Data Source Name, Description then select ODBC from the Type List
•  From the DNS Type Select USer DNS
•  From DNS Location Select Client.
•  From The name List select the ODBC name which we created in step 7.
•  Click Validate.
•  Enter password then Click OK.

14- Close the Infolog then close the form.

15- Go To Common > Processing group > Click new enter the Processing group Name, Description > Click Ctrl + S to save > Click Entities.

16- Click New > Select the Vendor entity > Select the ODBC data source > in the Query box, enter the following

select * from VendorEntity

Then click Generate source mapping.

17- Close the infolog

18- To preview to data Click Preview source file button, and then close the form.

19- Select the Processing group > Click Get staging data button.

20- Click Ok to create Job

21- Click Run in the staging data execution form.

22- Click Ok.

23- The records will be inserted in the staging. close the infolog.


24- select the Processing group > Click Copy data to target.

25- Select the Job ID that we created in step 20 then click OK.

26- Click run to import.

27- Click Ok.

28- The data will be written to target.

29- Go to the Vendor List form, note that vendor accounts was imported successfully.

See Also