Use of AX Source Component

AX Source Component is an SSIS source component that can be used to read/retrieve data from a Microsoft Dynamics 365 Finance & Operations (F&O) instance or a Microsoft Dynamics AX server (AX 4.0 to 2012 R3).

AX Source Component includes the following two pages to configure how you want to read data from Microsoft Dynamics 365 FO/AX.

  • General
  • Columns

The General page allows you to specify the general settings of the AX source component, while the Columns page shows you all available columns based on the provided query and source tables.

General page

The General page of AX Source Component allows you to specify the general settings of the component. Depending on the selected connection manager, the UI will change accordingly.

Connection API - OData (for Dynamics 365 Finance & Operations)

AX Source Component - OData

Connection Manager

AX source component requires a AX connection in order to connect with Dynamics 365 Finance and Operations. The AX Connection Manager option will show all DynamicsAX connection managers that have been created in the current SSIS package or project.

Source Type

The Source Type option allows you to specify whether you want to read data from a Dynamics 365 Finance and Operations entity or use the RecurringExport option. It is only available after you have selected OData as your connection API in AX Connection Manager.

  • Entity: When this option is selected, the component will retrieve a list of all available entities for the selected AX connection.
  • RecurringExport: When this option is selected, it allows you to export data from a data project that is pre-configured in Dynamics 365 Finance and Operations.
Source Entity

The Source Entity option is only available after you have selected OData as your connection API in AX Connection Manager. When the option is selected, the component will retrieve a list of all available AX entities for the selected AX connection.

Note: When working with RecurringExport Source Type, please make sure the Source Entity matches the Job Entity Name in order to get data successfully.

Legal Entity

The Legal Entity option allows you to specify the legal entity for the data export. This option is only available when working with RecurringExport Source Type.

Export Project

The Export Project option allows you to specify the name of the data project for export. This option is only available when working with RecurringExport Source Type.

Job Entity Name

The Job Entity Name option allows you to specify the name of the entity within the specified data project.This option is only available when working with RecurringExport Source Type.

Batch Size

The Batch Size option allows you to specify how many records you want to retrieve each time. The default value is 1000 and this option is only available when working with Entity Source Type.

Filtering Conditions

The Filtering Condition grid allows you to specify the filtering conditions so that the source component only returns those records that satisfy such conditions.

Connection API - Business Connector (for Dynamics AX 4.0 to 2012 R3)

AX Source Component - Business Connector

Connection Manager

AX source component requires a AX connection in order to connect with Microsoft Dynamics AX server. The AX Connection Manager option will show all DynamicsAX connection managers that have been created in the current SSIS package.

Query Statement

You can use the text editor to enter a Dynamics AX query statement, which is typically a SELECT statement with source tables represented by %N format, where N is an integer. For instance, %1 can be used to represent the first table, %2 for the second, and so on. 
Since v2.8, Query Statement supports the use of User and System Variables. Simple select a variable under the Insert Variables drop down menu, and a placeholder value will be inserted into the filter text.

Source Tables

For each table identifier specified in your Query Statement, you must specify which table it represents. You can use the buttons on the right to add new/delete source table(s), and make change to their sequences.

Refresh Component

By clicking this button, the component will try to retrieve the latest metadata and update each field to its most recent metadata. After clicking this button, you will receive the following screen once the update is done.

AX Source Component - Refresh Component

Expression fx Icon

Click the blue fx icon to launch SSIS Expression Editor to enable dynamic update of the property at run time.

Generate Documentation Icon

Click the Generate Documentation icon to generate a Word document which describes the component's metadata including relevant mapping, and so on.

Columns page

The Columns page of AX Source Component shows you all columns available from the Query Statement or Source Entity that you have defined in General page.

AX Source Component - Columns

On the top left of the grid, you can see a checkbox, which can be used to toggle the selection of all available fields. This is a productive way to check or uncheck all available fields.

The Columns Page grid consists of:

  • AX Field: Column that will be retrieved from AX Server.
  • Data Type: The data type of this field.

Note: When working with an F&O connection (an OData connection), the metadata reported by the component isn't most accurate, mainly because the service does not return data length information. Because of this, you could toggle the Data Type column in order to switch to different data lengths in order to choose more accurate column metadata. In some very rare case, you might want to make specific modifications to a particular column in changing its data length or even data type. To do so, you would have to leverage the SSIS Advanced Editor.

SSIS Advanced Editor is available if you right-click the component in SSIS Data Flow view, and choose "Show Advanced Editor..."

Show advanced editor