Use of AX Source Component

AX Source Component is an SSIS source component that can be used to read/retrieve data from Microsoft Dynamics AX server or Microsoft Dynamics 365 Finance & Operations. The AX source component supports using an AX query as the data source.

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. 

Connection API - Business Connector

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 now 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.

Connection API - OData

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.

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.

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: There is not currently a way to make direct changes to SSIS metadata (field name, data type, etc.) in this window, but you can use SSIS Advanced Editor to make such change if desired. You should rarely need to make direct SSIS metadata changes, but if you have done so using SSIS Advanced Editor, you should be cautious about using the "Refresh Component" button. Clicking this button will essentially revert any metadata changes that you have made.

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

Show advanced editor