COZYROC Dynamics AX components, part of COZYROC SSIS+ suite since 2010, are third-party plug-ins for Microsoft SSIS that make it easy to integrate or migrate data to and from Microsoft Dynamics AX. They will preserve the integrity of your data by always using the recommended Microsoft .NET Business Connector for Dynamics AX. You can use any application, service or database supported by SQL Server Integration Services (SSIS) or COZYROC’s toolkit as the source or destination for Dynamics AX. The toolkit is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components. In addition, COZYROC also provides short and informative demonstration videos, providing step-by-step instructions on how to get started.
The COZYROC SSIS+ library also includes the powerful and elegant Data Flow Task Plus, not found in any other competitive product on the market. Data Flow Task Plus allows creation of dynamic data flows at runtime and can be used with COZYROC's Dynamics AX adapters. You can create processes where the addition or deletion of Dynamics AX table columns doesn't necessitate modification of existing SSIS packages. This powerful feature requires no programming skills to use and greatly simplifies your maintenance efforts.
The Dynamics AX integration package consists of four parts:
- Well documented API for use by user-defined scripts.
- Uses the .NET Business Connector for Dynamics AX.
- The SELECT statement or the input table used by the component can be dynamically modified at runtime using a variable and an expression.
- Supports three actions on tables: Create, Update or Delete.
Technical Specifications
- Support Dynamics AX 2012, 2009, 4.0 systems.
- Requires installation of Microsoft .NET Business Connector for Dynamics AX.
Case Studies
Dynamics AX Source
In this section we will show you how to set up a Dynamics AX Source component.
- Before you begin, configure a Dynamics AX connection manager. You must have the Dynamics AX .NET Business Connector for the connection manager to work.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the Dynamics AX Source component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas to open the component dialog.
Congratulations! You have successfully configured the Dynamics AX Source component.
Dynamics AX Destination
In this section we will show you how to set up a Dynamics AX Destination component.
- Before you begin, configure a Dynamics AX connection manager. You must have the Dynamics AX .NET Business Connector installed for the connection manager to work.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- Configure a source component that will provide the data to be written to Dynamics AX.
- In the SSIS Toolbox, locate the Dynamics AX Destination component and drag it onto the Data Flow canvas.
- Connect the blue arrow from the source component or the data flow transformation that you want to immediately precede the Dynamics AX Destination component.
- Double-click on the component on the canvas to open the component dialog.
Dynamics AX Connection
In this section we will show you how to set up a Dynamics AX Connection Manager to access Dynamics AX.
Overview
Dynamics AX Connection Manager is SSIS Connection Manager for establishing Dynamics AX connections. The connection manager is based on Microsoft Dynamics AX .NET Business Connector. Use the API to build components based on Dynamics AX Connection Manager.
Quick Start
In this section we will show you how to set up a Dynamics AX Connection Manager to access Dynamics AX.
Parameters
Server
Use the Server page of the Dynamics AX Connection Manager dialog to specify properties for connecting to Dynamics AX server.
- Test Connection
- Confirm connection manager configuration by clicking Test Connection.
Specify user name to access Dynamics AX server. Do not use the Microsoft Dynamics AX user name; use the NT alias that is connected to the Microsoft Dynamics AX account. This parameter is optional.
Specify the domain associated with the Windows user. This parameter is optional.
Specify if all clients should use the same connection. This property has the options listed in the following table.
Value Description True All clients use same connection (default). False Every client use different connection.
Advanced
Use the Advanced page of the Dynamics AX Connection Manager dialog to specify additional properties if your server configuration is different from the default.
Specify Business Connector Proxy user. This parameter is optional.
Specify Business Connector Proxy password. This parameter is optional.
Specify Business Connector domain. This parameter is optional.
Specify company you want to select for the session. This parameter is optional.
Specify language to use for Microsoft Dynamics AX labels. For example, "en-us". This parameter is optional.
Specify Microsoft Dynamics AX Application Object Server (AOS) to connect to. For example, InstanceName@ServerName:PortNumber. This parameter is optional.
Specify name of the Microsoft Dynamics AX configuration to use while logging on. This parameter is optional.
Knowledge Base
- Where can I find the documentation for the Dynamics AX Connection?
- Error Message: The connection type "xxxx" specified for connection manager "xxxx" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
What's New
- New: Introduced connection.
Related documentation
Overview
The Dynamics AX Source Component is an SSIS Data Flow Component for consuming data from a Dynamics AX server. The configuration parameters are provided in the standard Data Flow Component dialogs.
Quick Start
In this section we will show you how to set up a Dynamics AX Source component.
- Before you begin, configure a Dynamics AX connection manager. You must have the Dynamics AX .NET Business Connector for the connection manager to work.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- In the SSIS Toolbox, locate the Dynamics AX Source component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas to open the component dialog.
Congratulations! You have successfully configured the Dynamics AX Source component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing Dynamics AX connection manager.
Related Topics: Dynamics AX Connection Manager
Select one or more Dynamics AX tables from which data will be extracted.
Use SELECT statements to specify the data to be processed from the tables selected with the InputTables parameter above. To indicate which table the data should be extracted from, use %1 for the first table in InputTables, %2 for the second table in InputTables and so on.
Knowledge Base
- Where can I find the documentation for the Dynamics AX Source?
- Dynamics AX Source: How to modify the statement parameter dynamically at runtime
- Dynamics AX Source: How to modify InputTable names dynamically at runtime
- After modifying the default Data Types in an ETL source, my modifications are lost the next time I open the Source component
What's New
- New: Support for Dynamics AX 2012 derived tables (Thank you, Tom).
- Fixed: Component failed with error "Value cannot be null" (Thank you, Frederic).
- New: Support for Dynamics AX 4.0 version (Thank you, Taylor).
Related documentation
Overview
The Dynamics AX Destination Component is an SSIS Data Flow Component for loading data into a Microsoft Dynamics AX table. The configuration parameters are provided in the standard Data Flow Component dialogs.
Quick Start
In this section we will show you how to set up a Dynamics AX Destination component.
- Before you begin, configure a Dynamics AX connection manager. You must have the Dynamics AX .NET Business Connector installed for the connection manager to work.
- Drag the Data Flow Task from the SSIS Toolbox to the Control Flow canvas.
- Click the Data Flow tab to go to the Data Flow canvas.
- Configure a source component that will provide the data to be written to Dynamics AX.
- In the SSIS Toolbox, locate the Dynamics AX Destination component and drag it onto the Data Flow canvas.
- Connect the blue arrow from the source component or the data flow transformation that you want to immediately precede the Dynamics AX Destination component.
- Double-click on the component on the canvas to open the component dialog.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing Dynamics AX connection manager.
Related Topics: Dynamics AX Connection Manager
Select the destination table action. This parameter has the options listed in the following table.
Action Description Create Create a new record in the destination table. Update Update an existing record in the destination table. The appropriate table record identifier must be specified for the update to work. Delete Delete an existing record from the destination table. The appropriate table record identifier must be specified for the delete to work. After changing the action, you must use the Refresh command in the 'Column Mappings' tab to reload the destination list metadata.Specify the destination Dynamics AX table where the data is to be loaded.
Specify how to handle rows with errors.
Knowledge Base
- Where can I find the documentation for the Dynamics AX Destination?
- Dynamics AX Destination: How to modify the Dynamics AX destination table dynamically at runtime
What's New
- Fixed: Component failed to create records in non-derived tables.
- New: Support for Dynamics AX 2012 derived tables (Thank you, Tom).
- Fixed: Component failed with error "Cannot call NEXT, update(), or delete() on buffer where data is selected or inserted in another transaction scope" when updating certain tables (Thank you, David).
- Fixed: Component failed with error "The supplied method arguments are not valid." when trying to process input data containing the percent (%) symbol (Thank you, Fabio).
- New: Support for Dynamics AX 4.0 version (Thank you, Taylor).
- Fixed: Component failed to update string fields containing quote (') character.
- New: Component can now update array fields.
- New: Component now utilizes optimized bulk-data insert.
- New: Introduced component.
Related documentation
Overview
These public interfaces are used in Dynamics AX components. They are provided for implementing advanced functionality in your scripts. In order for you to use these interfaces in your project, you have to reference CozyRoc.SSISPlus.dll and Microsoft.Dynamics.BusinessConnectorNet.dll libraries.
IAxConnection
When you call Dynamics AX Connection Manager AcquireConnection method, it returns object implementing IAxConnection interface. This is the interface used for interaction with Dynamics AX server.
Methods
Connects to Dynamics AX server. The method result is Axapta object.
Closes the connection to Dynamics AX server. Use always after you finish working with the server.
Returns true if connected to the server.
Get list of Dynamics AX tables. These are the method parameters:
- accessType (int) - table access type. It can be one of the options listed in the following table.
Value Description 0 Returns all tables, including tables without access. 1 Returns tables, which can viewed 2 Returns tables, which can be updated. 3 Returns tables, which permit add. 4 Returns tables, which permit delete.
Gets table fields. The method returns array of IAxTableField objects. These are the method parameters:
- table (string) - Dynamics AX table.
Properties
Specify user name to access Dynamics AX server.
Specify the domain associated with the Windows user.
Specify if all clients should use the same connection. This property has the options listed in the following table.
Value | Description |
---|---|
True | All clients use same connection (default). |
False | Every client uses different connection. |
Specify Business Connector Proxy user.
Specify Business Connector Proxy password.
Specify Business Connector Proxy domain.
Specify company you want to select for the session.
Specify language to use for Microsoft Dynamics AX labels. For example, "en-us".
Specify Microsoft Dynamics AX Application Object Server (AOS) to connect to. For example, Company1@AosInstance:PortNumber.
Specify name of the Microsoft Dynamics AX configuration to use while logging on.
IAxTableField
This interface is used to retrieve table field information.
Properties
Returns field identifier.
Returns field name.
Returns field type. The type can be one of the types listed here.
Returns field length. Note: This property applies only to fields of type string.
Returns field access rights. It can be one of the options listed in the following table.
Value | Description |
---|---|
0 | Field cannot be accessed. |
1 | Field can be viewed. |
2 | Field can be updated. |
Knowledge Base
Related documentation
Dynamics AX Destination
- Fixed: Component failed to create records in non-derived tables.
- New: Support for Dynamics AX 2012 derived tables (Thank you, Tom).
- Fixed: Component failed with error "Cannot call NEXT, update(), or delete() on buffer where data is selected or inserted in another transaction scope" when updating certain tables (Thank you, David).
- Fixed: Component failed with error "The supplied method arguments are not valid." when trying to process input data containing the percent (%) symbol (Thank you, Fabio).
- New: Support for Dynamics AX 4.0 version (Thank you, Taylor).
- Fixed: Component failed to update string fields containing quote (') character.
- New: Component can now update array fields.
- New: Component now utilizes optimized bulk-data insert.
- New: Introduced component.
Dynamics AX Source
- New: Support for Dynamics AX 2012 derived tables (Thank you, Tom).
- Fixed: Component failed with error "Value cannot be null" (Thank you, Frederic).
- New: Support for Dynamics AX 4.0 version (Thank you, Taylor).
Knowledge Base
- Where can I find the documentation for the Dynamics AX Connection?
- Where can I find the documentation for the Dynamics AX Destination?
- Where can I find the documentation for the Dynamics AX Source?
- Dynamics AX Source: How to modify the statement parameter dynamically at runtime
- Dynamics AX Destination: How to modify the Dynamics AX destination table dynamically at runtime
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.