Installation

To install and run SSIS Integration Toolkit for Microsoft Dynamics AX, your system must have the following components installed. 

  • A supported SSIS design-time or run-time, which can be one of the following:
    • SSIS 2016
    • SSIS 2014
    • SSIS 2012
    • SSIS 2008 R2
    • SSIS 2008
    • SSIS 2005

    For SSIS runtime, the installation should be done by using the corresponding SQL Server installation media (most likely in CD/DVD format), and you must select the "Integration Services" component during the installation (as shown below).

    Integration Services Runtime Selection

    Note that for SSIS runtime, SQL Server Standard Edition or above is required to run our software. SQL Server Express or Web editions are not supported due to their own limitations.

    Note that when using SQL Server 2014, a cumulative update is required (a recent service pack, such as one of the following, is more preferred) in order to run our software during runtime.

    For SSIS design-time, you should be installing the version of SSDT (SQL Server Data Tools) or BIDS (Business Intelligence Development Studio) that aligns with the SQL Server version that you plan to use for your final deployment (the runtime).

    • When targeting SSIS 2016, you would use SSDT for Visual Studio 2015 available for download at https://msdn.microsoft.com/mt186501.aspx
      • Note that this SSDT installation can be used to target SSIS 2014 or 2012 as well.
    • When targeting SSIS 2014, you can use either one of the following:
    • When targeting SSIS 2012, you can use any one of the following:
    • When targeting SSIS 2008 R2 or earlier, you would have to install the Business Intelligence Development Studio that is shipped with the installation media of the corresponding SQL Server version. 
  • Microsoft Dynamics AX .NET Business Connector
    • Microsoft Dynamics AX .NET Business Connector is usually shipped with AX installation package. It needs to be installed on the developer machine or your integration servers.
  • .NET Framework 3.5
    • For any version before v2.7, .NET Framework 3.5 is required. For v2.7 or later, .NET Framework 3.5 is only required when you use SSIS 2005.
      • If you are using Windows Server 2003, Windows Server 2008, Windows Vista, Windows XP operating systems, you would install .NET framework by downloading it from Microsoft website.
      • If you are using Windows Server 2008 R2 or 2012 family of operating systems, .NET framework 3.5 should be installed using Server Manager program by adding .NET Framework 3.5.1 features.
      • If you are using Windows 8, go to Control Panel -> Programs and Features -> Turn Windows features on or off, then select .NET Framework (includes .NET 2.0 and 3.0).
  • Windows Installer 4.5
    • If you are using Windows Server 2008, Windows Server 2012 family of operating systems or later (including Windows Vista, Windows 7, Windows 8, Windows Server 2012 R2, Windows Server 2016), you do not need to do anything since the latest Windows Installer has been installed by the operating system.
    • If you are using Windows Server 2003 family of operating systems (including Windows XP), you should install Windows Installer 4.5 by downloading it from Microsoft website.

When you have confirmed that your system satisfies the above prerequisites, you can navigate to KingswaySoft website at http://www.kingswaysoft.com to download the installation package. In the download page you will find two download links, one for x86 and one for x64 system. Make sure to choose and download the right bit for your system. 

Download options

Once you click the download link, save the zip file which contains the .msi file. Double click the .msi file to start the setup. A security warning may appear. You can safely accept the warning and continue the setup.

The SSIS Integration for Microsoft Dynamics AX Setup Wizard will appear. Click ‘Next’ to continue.

AX Integration Installation

Select the checkbox to agree to the terms of the End-User License Agreement. Click 'Next' to continue. 

AX Integration Installation

Next, choose one of the three setup types that best suits your needs.

AX Integration Installation

Click ‘Install’ to begin the installation. 

AX Integration Installation

To complete the installation, select 'Finish'. 

AX Integration Installation

Setup a AX Connection using AX Connection Manager

SSIS Integration Toolkit AX Connection Manager is an SSIS connection manager that can be used to establish connections with Microsoft Dynamics AX Server. 

AX connection manager allows you to specify how you want to connect to your AX server. 

To add a AX connection to your SSIS package, right-click in the Connection Manager area, and choose "New Connection..." from the context menu. You will be prompted the following window, where you can choose the "DynamicsAX" item to add the connection.

new connetion

Add AX Connection Manager

AX Connection Manager contains the following three pages which configure how to connect to Microsoft Dynamics AX server.

  • General
  • Advanced Settings
  • More Info

General page

The General page of AX Connection Manager allows you to specify the general settings of the connection. Specifically, this is where you enter authentication information for your Dynamics AX connection.

AX Connection Manager

User Name

User Name option allows you to specify the Windows user account that you want to use when connecting to Dynamics AX server. Note that the user name should be a Windows domain account instead of the actual Dynamics AX user account. 

User Domain

User Domain option is used to specify the active directory domain of the Windows user. 

Test Connection

After all the connection information has been provided, you may click the "Test Connection" button to test if the user can successfully login to the AX server. 

Advanced Settings page

The Advanced Settings page of AX Connection Manager allows you to specify some advanced settings of the connection. 

AX Connection Manager

Proxy Account - User Name

Specify the .NET Business Connector proxy account's user name that is used for the AX connection. This is an optional parameter.

Proxy Account - Password

Specify the .NET Business Connector proxy account's password. This is an optional parameter.

Proxy Account - Domain

Specify the .NET Business Connector proxy account's Windows domain name. This is an optional parameter.

Object Server Name

Specify the Microsoft Dynamics AX Application Object Server (AOS) used for the connection. This is an optional parameter.

Configuration Name

Specify the name of Microsoft Dynamics AX configuration to be used for the connection. This is an optional parameter.

Company

Specify the company to be used for the connection. This is an optional parameter.

Language

Specify the language to be used for Dynamics AX labels for the connection session. An example of such language is "en-us". This is an optional parameter.

More Info page

More Info page shows some basic information about the toolkit. In this page, you can find the version information of the toolkit.

AX Connection Manager

Add AX Components to SSIS Toolbox

SSIS Integration Toolkit for Microsoft Dynamics AX includes three data flow components. They must be shown in SSIS toolbox before you can use (drag and drop) them in SSIS data flow task.

  • If you are using SQL Server 2008 R2 or earlier, you must add them to SSIS toolbox by following the instructions below.
  • If you are using SQL Server 2012 or later, you should not need to do so, as SQL Server 2012 (or a later version) automatically lists all available pipeline components by scanning the system. Under certain circumstances, your SSIS toolbox might appear empty, in which case, you should click the SSIS Toolbox button (shown below) on the top-right corner of your Control Flow or Data Flow view.

    SSIS Toolbox

Add AX Data Flow Components to SSIS Toolbox (SQL Server 2008 R2 or earlier)

In BIDS 2008 or 2005 (depending on which SQL Server version you are using), create a new data flow if you do not have one yet, and switch to SSIS data flow page. Then right-click on the toolbox area to bring up the context menu and select "Choose Items..." option as shown below.

Choose items

You are now presented with a window called "Choose Toolbox Items". Switch to the "SSIS Data Flow Items" tab, and select Dynamics AX Destination, and Dynamics AX Source components from the list. 

Add AX SSIS Components

You can now use any of the three components by dragging and dropping either of them to the design surface of your SSIS data flow task. 

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

AX Source Editor

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

Query Statement now supports the use of User and System Variables (since v2.8). 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 button

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.

Refresh Metadata

Columns page

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

AX Source Editor

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

Use of AX Next Sequence Number Component

Next Sequence Number Editor

The Ax Next Sequence Number component is an SSIS transformation component that allows you to get the next sequence number from the AX server that you can use as the input for one of the input columns of your AX destination component. The sequencing number capability would first have to be set up properly in AX.

AX Nex Sequence Number Editor

AX Connection Manager

AX Next Sequence Number Component requires an 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.

Sequence Name

The name of the Number Sequence entry specified in AX.

Output Column Name

Specify the name of the Output Column that will be created by this component in SSIS. The output column will be created for you once you have finish configuring the component by clicking the "OK" button.

Scope(since v2.7)

This option allows to specify the scope of the sequence number defined in AX. There are two options available.

  • Global - choose this option if the sequence number is defined in the global scope, which means it is available to all companies in the same installation.
  • Company - choose this option if the sequence number is defined in the company level.

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, updateupsert, 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.

  • General
  • Columns
  • 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. 

General page

The General page of AX Destination Component allows you to specify the general settings of the component. 

AX Destination Editor

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

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. 

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.

Refresh metadata

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.

refresh metadata

Clear All Mappings button

By clicking this button, the component will remove all field mappings. You can use this button to reset your mappings. 

Columns page

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.

AX Destination Editor

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

AX Destination Editor

Error Handling page

The Error Handling page allows you to specify how errors should be handled when they happen. 

AX Destination Editor

There are three options available. 

  1. Fail on error
  2. Redirect rows to error output
  3. 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.

License Manager

SSIS Integration Toolkit comes with a license manager program which helps you manage and activate the product license key to be used for the toolkit.

Without a commercial license, SSIS Integration Toolkit will operate under the Developer License which is free to use for development or evaluation purposes. Under the developer license, you can use the software within the development tool (SSDT-BI, BIDS, or Visual Studio). 

KingswaySoft License Manager

The only limitation with the free developer license is the inability to run the software outside of the development tool (SSDT-BI, BIDS, or Visual Studio). If you would like to run the software outside the development tool, such as running SSIS packages on a scheduled basis or from a command line, you will need to acquire a license from us.

If you want to test out the functionality by scheduling your SSIS packages, a trial license can be requested. To do so, you can launch License Manager program, then click "Change License Key" button, where you can request a free trial license after filling out the necessary Licensee Information.

KingswaySoft License Manager

If you have received a product license key from us after placing an order through our online shopping cart system, you can also click "Change License Key" button. This allows you to enter the product license key to activate the software and use the fully-featured commercial license. 

To request a free trial license or activate a product license key that you have received, use the Web Service option to complete the process by sending the request to our license server directly. An Internet connection is required when the Web Service option is used. This is the option that we recommend.

Alternatively, you can choose the Email option. The license manager will generate an email for you which you can send to us. The Email option should only be used if your system has no Internet access. It requires manual processing so please expect to wait 24 to 48 hours before receiving a license file from us. Once you have received the license file through email, you can save it to a local file, which you can then install by clicking "Install License File..." button in License Manager.

If you have acquired a perpetual license from us, once the software has been activated, your license manager will look something like below.

KingswaySoft License Manager

If you are using a perpetual license, you can see your Maintenance Expiry Date in the License Manager program. By default, your perpetual license comes with a one-year maintenance and upgrade, which entitles you to use any version of the software released before your Maintenance Expiry Date. You can extend it by entering a new maintenance license key which can be acquired from us.

KingswaySoft License Manager

If your commercial license is a subscription license, you will not see the Maintenance Expiry Date option in the License Manager program, since your subscription license comes with maintenance and upgrade for the entire subscription period.

NOTE: You must run License Manager program under a local administrative account due to the privileges required to write license file to the system.

Contact Us

If you need any further assistance with the toolkit, please do not hesitate to contact us