Sunday, May 4, 2014

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. 
In my scenario I'll be exporting a custom data based on a brand-new data model I've introduced
<![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

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:
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
And in case I will try to modify the data in the 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
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
Please find more info about Exporting data from Query to Data file here:
Please note that when you execute SQL Job you may face with the following 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'
EXEC sp_configure 'xp_cmdshell', 1
EXEC sp_configure 'show advanced options', 1
After that when we test SQL Job again it will execute successfully
EXEC xp_cmdshell 'bcp "USE MicrosoftDynamicsAX; SELECT * FROM dbo.ALEXTABLE" queryout "C:\Alex\Out\AlexSQL.txt" -T -c'
As the result we'll have the following 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:
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:
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
As the result of execution AlexFile.txt file will be generated in predefined folder
Next step would be to deploy AlexProject to SSIS
Project - Deploy
At this point you may see the following message
All you need to do at this point is to create SSISDB catalog as shown below
After that deployment will be successful
Deployment Results
And your package will show up in the list of packages
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:
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
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


  1. Nice blog post If you are searching for the qualityMicrosoft Dynamic Jobs than, DFSM Recruitment is known as the best Microsoft Dynamics Recruiter and recruit Microsoft Dynamics 365, CRM, SharePoint, Office 365 clients

  2. Great info & thanks for sharing this post with us. In addition, if anybody looking for MS Dynamics CRM jobs, Please visit at Microsoft Dynamics CRM Jobs & Vacancies

  3. Nice blogpost and thanks for sharing such useful information about Microsoft Dynamics.To get more best work opportunity in USA you should visit at Microsoft Dynamics 365 Recruitment.

  4. Great blogpost and thanks for sharing such useful information about Microsoft Dynamics. If you are looking for the best Microsoft Dynamics Recruiters in USA then you should visit atMicrosoft Dynamics jobs and recruitment.DFSM Recruitment provide various kinds of best Jobs hiring and Vacancies in the field of Microsoft Dynamics AX and CRM In USA.

  5. Apply online for Microsoft Recruitment. Get the information about the current and upcoming vacancies here.