Data Import/Export Framework Walkthrough: Importing data

Microsoft AX Data Import/Export Framework, out of the box supports 70 Entities types. In this post I will perform an import of customer data. The same concept is also applicable to other entities types.

Configuration

You must setup a folder with the purpose of sharing data between AOS Service and SQL SSIS.

Open “Data Import/Export Framework > Setup > Data Import/Export Framework parameters”

Screenshot01

Select a folder and press validate.

If the folder exists and has the proper permissions the icon should change to green.  AOS Services and SQL SSIS must have access permissions to this folder.

Data Source Formats

Open “Data Import/Export Framework > Setup > Source Data Formats”

Where possible I prefer using a file format that allows manipulating data directly in Microsoft Excel.

Screenshot02

  1. Create a new record.
  2. In source name, named it “TXT.
  3. Select a description that defines the data format.
  4. The files will be TAB separated values to easily manipulate it in Microsoft Excel. I have selected the option Delimited in File format and Row Delimiter type “Tab {t}”.
  5. I prefer using column names in the first line, so I have selected the “First row
    header” option.

Processing Group

Open “Data Import/Export Framework > Common > Processing Group” and select new. I only want a processing group that will import Customers, so I have named it Customers.

Select Entities option

Capture8

  1. Click New
  2. In the entity Name select “Customers”
  3. In the Source Format, select previous format, TXT file.
  4. Select “Run Business Logic in insert or update method” and “Run business Validation”

Select the option “Generated Source File”

Screenshot05.0

Press Next

Screenshot05.1

Select the fields you would like to import. To work with the proper template file, select “Generate Template”.

Capture9

Now, open the template in Microsoft Excel and fill your data.

Screenshot07

Close Excel, and select the text file in sample data.

19

Click Modify Mapping to validate the mappings and you can amend incorrect configuration

12

Select “Mappings to details” to map data using a tabular method.

Screenshot08

I have chosen the “Auto-generated option” because I want customers numbers being assigned automatically by Microsoft AX.  Confirm if everything is correct and close window.

Press “Preview Source file”

Screenshot08.1

Close “Entities Processing group window” and select “Get Staging Date” in the list of processing group.

Screenshot10

Create a new job.

Screenshot11

Select run.

Screenshot12

Press Ok.

Screenshot13

A log message is displayed.

Now, select “Copy data to Target”, to move the information from the staging area into final entities tables.

25

Select the job that was created in first step to import data into staging area and ok.

Capture10

Run the job.

Capture11

Data Validation

Open Accounts receivable > Common > Customers >All customers and filter, customers using the data in Excel.

30

Open the customer and validate the imported data.

31

Advertisements

7 thoughts on “Data Import/Export Framework Walkthrough: Importing data

  1. Hi ,

    I am trying to import the employees.

    Open “Data Import/Export Framework > Common > Processing Group” and select new. I only want a processing group that will import Employees .

    Click New
    In the entity Name select “Employee”
    In the Source Format, select previous format, TXT file.
    Select “Run Business Logic in insert or update method” and “Run business Validation”
    Select the option “Generated Source File”

    Select the option “Generated Source File” Select the fields you would like to import. To work with the proper template file, select “Generate Template”.

    When i click on the generate sample file , automatically it is opening the MS word.

    Could i know what is the problem and how to make to open the text file ?

  2. I see option to EXPORT TO AX on the processing groups.. this action creates two files DEF and a DAT files.. how do you IMPORT these back into a NEW environment?

    • Hi Zorro,

      Did you get any solution on how to import the DAT and DEF file back to AX?
      Please suggest.

      Thanks,
      Ismail.

      • best way to do this is to put data into the staging tables from the source company then export it into CSV.  You need to define the target entity example customer prior to do this with the fields that you would like to export.

        ________________________________

  3. hi,When I click the button Preview Source file,I get exception,and event log inforamtion:
    -1073450823SSIS.Pipeline”Flat File Source.Outputs[Flat File Source Output]” contains no output columns. An asynchronous output must contain output columns.
    -1073450996SSIS.PipelineOne or more component failed validation.
    -1073594105There were errors during task validation.

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s