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

2 comments:

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

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

    ReplyDelete