Use of AX Destination Component
AX Destination Component is an SSIS destination component that can be used to write data to Microsoft Dynamics AX server. You may create, update, upsert, delete AX records using the AX Destination Component.
AX Destination Component includes the following three pages to configure how you want to write data to Microsoft Dynamics AX server.
- Error Handling
The General page is used to specify the general settings of the AX destination component. The Columns page allows you to map the columns from upstream components to AX fields in the destination table. The Error Handling page allows you to specify how errors are handled should they happen.
The General page of AX Destination Component allows you to specify the general settings of the component.
- AX Connection Manager
AX destination 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.
Action option allows you to specify how the data should be written to Microsoft Dynamics AX server. There are four (4) action types available.
- Create - Create new record(s) in AX
- Update - Update existing record(s) in AX
- Upsert - Update any existing record(s) in AX if matching can be found, otherwise create a new record with the information from the upstream pipeline components
- Delete - Delete record(s) from AX
- Destination Table
Destination Table option allows you to specify which AX table to write the data. When the option is clicked, SSIS Integration Toolkit will retrieve a list of all available AX tables for the selected AX connection.
- Ignore Unchanged Fields
The Ignore Unchanged Fields option allows you to ignore any fields that have not been changed in the target AX system. With this option selected, the AX destination component will check the target AX system and compare each field to see if there are any changes for them. The component will only post the fields that have actual changes. All unchanged fields will be skipped and therefore, not posted to AX.
- Ignore Null-Valued Fields (since v2.4)
The Ignore Null-Valued Fields option allows you to ignore any fields that have a null value. By ignoring a field, the null value will not be posted to the target AX system. This can help avoid the situation that you overwrite non-empty values with an empty value, if your requirement dictates so.
- Include Non-Editable Fields (since v8.0)
The Include Non-Editable Fields option allows you to view all fields in the specified AX table.
- Bypass Table Method (since v8.0)
The Bypass Table Method option allows you to import data into target AX system without any data validations.
- Refresh Component button
By clicking this button, the component will retrieve the latest metadata from AX server and update each field. After clicking this button, you will receive the following screen once the refresh is done.
- Map Unmapped Fields button
By clicking this button, the component will map any unmapped AX fields by matching their names with the input columns from upstream components. This is useful when your source component has recently added more columns, in which case you can use this button to automatically establish the association between input columns and unmapped AX fields.
After clicking this button, you will receive the following message.
- Clear All Mappings button
By clicking this button, the component will remove all field mappings. You can use this button to reset your mappings.
The Columns page of AX Destination Component allows you to map the columns from upstream components to AX fields for the destination entity.
In Columns page, you would see a grid that contains four columns as below.
- Input Column - You can select a column from upstream component to be used as the input of the corresponding AX field.
- Destination AX Field - The AX field that you are writing data.
- Data Type - This column indicates the type of value for the current AX field in AX system. Typically you would need to pass in the value using the format indicated in Data Type column.
- Unmap - This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.
Error Handling page
The Error Handling page allows you to specify how errors should be handled when they happen.
There are three options available.
- Fail on error
- Redirect rows to error output
- Ignore error
When Redirect rows to error output option is selected, the error handling behavior might be different depending on the version of our software you are using.
- If you are using v2.0 or a later version, it will only redirect the rows that have failed to the error output (the successful ones will be directed to the Default Output of the destination component which is a new output in v2.0).
- If you are using v1.0, it will redirect all rows to the error output including those that have succeeded and those that have failed. In case you need to further process AX records after the destination component (such as logging AX records to a different system, or writing to a different AX table using a subset of available fields) when using v1.0, you must choose the "Redirect rows to error output" as the Error Handling option. Then attach a Conditional Split component to the AX destination component. In the Conditional Split component, evaluate the ErrorCode column (an output field added by the AX destination component) and check to see whether it has actually erred out. The Conditional Split should typically have two output branches, one is ErrorCode == -1, which is the success path, and the other one is ErrorCode != -1, which is the failure path indicating that an error has occurred when writing to the CRM.
In the error output, you can see the following columns:
- ID (version 1 or earlier) - Contains the newly created AX record's ID, which you can use to write to log or further process using additional data flow components. Note that this column has been moved to the Default Output of the destination component in v2.0 which has a new name called AxRecId.
- AxErrorMessage - Contains the error message that is reported by Dynamics AX or the component itself
NOTE: Use extra caution when selecting the Ignore error option, since the component will remain silent for any errors that have occurred.
In the Error Handling page, there is also an option(since v2.1) that can be used to enable or disable the following output fields for the destination component.
- AxRecId - Contains the newly created AX record's ID, which you can use to write to log or further process using additional data flow components.
- IsNew - Contains value to indicate whether it is a newly created AX record, or an existing one. This is useful when you use Upsert action.
NOTE: If you don't plan to use any of those fields for any further processing, it is generally recommended to turn them off, so you don't get any warning from SSIS by complaining that those fields are never used, and it should also provide a slightly better performance by doing so. Note again this feature is only available since v2.1.