AX Source Component

Using the AX Source Component

The 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 the 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)

Connection Manager

AX source component requires an 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.

Max Rows Returned (since v22.1)

The Max Rows Returned option allows you to limit the number of rows returned.

• When specified, the AX source component will stop paging as soon as the exact number of records has been retrieved.
• When the option is set to 0, it will retrieve all satisfying records by automatically paging through the entire data set.

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.

Order By

The Order By field can be used to specify the order by clause. Clicking on the ellipsis (...) button would open up the below Order By Builder window.

• Add condition (+): Adds a field to be used in Order by clause.
• Remove condition (-): Removes the field that was added.
• Ascending/Descending: Choose either to be ordered in ascending or descending order.
• Remove All: Removes all fields from the order by clause.

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

Connection Manager

AX source component requires an AX connection in order to connect with the 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. Simply 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 changes 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.

Expression fx Icon

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

Generate Documentation Icon

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

Columns Page

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

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.