Microsoft Dynamics AX 2012 – File Exchange (Export) using SQL

Microsoft Dynamics AX 2012 – File Exchange (Export) using SQL

 

Purpose: The purpose of this document to illustrate how to implement integration with Microsoft Dynamics AX 2012 based on File Exchange.

 

Challenge: In certain scenarios you need to implement integration with Microsoft Dynamics AX 2012 by means of File Exchange which is dictated by the software you integrate to or other architectural considerations. For the purposes of integration with Microsoft Dynamics AX 2012 based on File Exchange you can SQL approach.       

 

Solution: The recommended way to integrate with Microsoft Dynamics AX 2012 is by using Web Services. However in some scenarios it may make sense to integrate with Microsoft Dynamics AX 2012 using SQL approach. Example of this may be a Shipping Carrier system integration done using staging tables in Microsoft Dynamics AX 2012 to push data into or pull data from. In this article I'll implement integration with Microsoft Dynamics AX 2012 based on File Exchange using SQL approach. My goal for this integration will be to organize continuous data export (CSV data feed) from Microsoft Dynamics AX 2012 with file periodically generated for consumption by external system. 

 

Walkthrough

 

In my scenario I'll be exporting a custom data based on a brand-new data model I've introduced

 

Table

 

<![if !vml]><![endif]>

 

In order to expose the data from Microsoft Dynamics AX 2012 I'll create a View. In general case View can expose the data from multiple tables. The idea to use View resembles how the data is exposed from SQL Server Master Data Services using Subscription Views

View

 

 

When I synchronize the data model I'll see table and view on SQL side

 

SQL Table

 

 

SQL View

 

 

Please note that in SQL Server you can update info via Views with certain limitations and restrictions. That's why for my data export scenario I will precisely define permissions for users to allow read only and avoid updates. Please find more info about updatable Views here: http://technet.microsoft.com/en-us/library/ms180800.aspx

 

My next step will be to introduce a SQL login for integration service account

 

SQL Server Login - General

 

 

SQL Server Login - Securables

 

 

After that I'll add a Database user to Microsoft Dynamics AX 2012 database

 

Database user- General

 

 

Database user- Securables

 

 

Please note that I only allow select operation for View

 

Now you can try out your newly created SQL login

 

Connect to Server

 

 

I can see table and view only based on permissions set

 

SQL

 

 

And in case I will try to modify the data in the table

 

Table

 

 

I'll see the following exception

 

Exception - Table

 

 

Exception - View

 

 

In order to organize continuous data export I'll go ahead and create SQL Job

 

SQL Job

 

 

New Job

 

 

Please note that in order to export data as file I can leverage bcp utility as shown below

 

Job Step Properties

 

 

New Job - Steps

 

 

Now when I created a Step I can also create a Job Schedule to execute this job (data export) periodically

 

New Job Schedule

 

 

New Job - Schedules

 

 

When I'm done creating a job I'll see it listed as shown below

 

Jobs

 

 

Please find more info about Exporting data from Query to Data file here:  http://technet.microsoft.com/en-us/library/ms189569(v=sql.105).aspx

 

Please note that when you execute SQL Job you may face with the following error

 

Error

 

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

 

In order to resolve it as error message suggests we are going to execute appropriate SQL commands to enable use of 'xp_cmdshell'

 

SQL

 

EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO EXEC sp_configure 'show advanced options', 1

 

After that when we test SQL Job again it will execute successfully

 

SQL

 

EXEC xp_cmdshell 'bcp "USE MicrosoftDynamicsAX; SELECT * FROM dbo.ALEXTABLE" queryout "C:\Alex\Out\AlexSQL.txt" -T -c'

 

Result

 

 

As the result we'll have the following file

 

File

 

 

Please note that you can run SQL Job interactively

 

Run Job

 

 

Or what we truly want in this scenario to run it as a Batch job

 

Batch Job

 

 

Please see this article for more info about how to create SQL Jobs: http://technet.microsoft.com/en-us/library/ms190268.aspx

 

At this point we successfully created SQL Job to export data as is from Microsoft Dynamics AX 2012. However in some scenarios you may need to transform exported data before it may be consumed by external system. In this case you can create and execute SSIS Package

 

In order to proceed with SSIS package creation I had to install Data tools for Visual Studio 2012 from here: http://www.microsoft.com/en-us/download/details.aspx?id=36843

 

Then I can go ahead and create SSIS Project

 

New Project

 

 

Remark: Please note that when you install Data tools for Visual Studio 2012 for installation to be successful please select "Install new instance" and not "Add new features"

 

Next in order to extract the data from Microsoft Dynamics AX 2012 database I'll create OLE DB Connection

 

New OLE DB Connection

 

 

Configure OLE DB Connection Manager

 

 

Connection Manager

 

 

As the result Connection Manager will be created as shown below

 

Connection Manager

 

 

Next logical step is to add Data Flow Task to organize data export

 

Data Flow Task

 

 

Inside a Data Flow Task I'm going to create OLE DB Source and Flat File Destination

 

Data Flow Task

 

 

I'll bind OLE DB Source to AlexTable table or AlexView view

 

OLE DB Source – Connection manager

 

 

OLE DB Source – Columns

 

 

Then I'll define Flat File Destination details starting from Flat File Format. I'll select Delimited there

 

Flat File Format

 

 

Flat File Destination – Connection Manager

 

 

Then I'll specify a file name to be used

 

Flat File Destination - General

 

 

Then I'll make sure my file will contain needed columns

 

Flat File Destination - Columns

 

 

You can also review explicit mapping between Source and Destination on Mapping screen. Please note that you Source and Destination must be connected

 

Flat File Destination - Mapping

 

 

When configured Data Flow Task will look like this

 

Data Flow Task

 

 

And now we'll be ready to execute integration. Preliminary result will look like this

 

Result

 

 

As the result of execution AlexFile.txt file will be generated in predefined folder

 

File

 

 

Next step would be to deploy AlexProject to SSIS

 

Project - Deploy

 

 

At this point you may see the following message

 

Message

 

 

All you need to do at this point is to create SSISDB catalog as shown below

 

Catalog

 

 

Folder

 

 

After that deployment will be successful

 

Deployment Results

 

 

And your package will show up in the list of packages

 

Packages

 

 

Package

 

 

The last step will be to set up this package for periodic execution as a Batch job

 

In order to automate and schedule the execution of SQL Server Integration Services packages you can use SQL Server Agent. Please find more info about SQL Agent job for packages here: http://technet.microsoft.com/en-us/library/ms141701.aspx

 

Thus we'll go ahead and create a new Job

 

New Job - General

 

 

Similar to the first scenario we'll create a Step

 

New Job - Steps

 

 

Please note that when I create a new step I'll specify Job Step Type = "SQL Server Integration Services Package" which will allow me to bind this execution step to SSIS package I created earlier

 

New Job Step - General

 

 

Select an SSIS Package

 

 

Again similar to previous scenario I'll define a schedule

 

Job Properties - Schedules

 

 

New Job Schedule

 

 

As the result my new job gets created and it is ready for periodic execution

 

Jobs

 

 

Remark: In this article I used a simple SQL approach to organize data export. Depending on your requirements you may have some more sophisticated requirements around file generation logic, additional requirements around data logging, XSLT engine interaction, advanced security, etc. which can lead you to other approaches to consider. Please also review other articles in this series which highlight different approaches to the same task and their details   

 

Summary: This document describes how to implement File Exchange data export integration with Microsoft Dynamics AX 2012 using SQL approach. In particular I described how one can implement data export by using SQL Job or SSIS package. I also focused on several important aspects for continuous integration (data export) such as process automation using batch jobs, generation of files with predefined names, etc.

 

Author: Alex Anikiev, PhD, MCP

 

Tags: Dynamics ERP, Microsoft Dynamics AX 2012, Integration, File Exchange, Data Export, SQL, CSV, XSLT.

 

Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the concepts and describe the examples.

 

Call to action: If you liked this article please take a moment to share your experiences and interesting scenarios you came across in comments. This info will definitely help me to pick the right topic to highlight in my future blogs