Dynamics AX 2009 Bulk Data insert using SSIS
Recently I was working on a POC which required me to insert 1,00,000 records into AX 2009 from an external LOB application and post these lines in AX. Well that got me thinking since it was a huge amount of import. .NET BC would be very slow, excel import would not be feasible. Direct SQL inserts seemed to be the feasible solution but I had to deal with RecId, RecVersionId & Company. Well after much R&D I hit upon using SSIS and some custom scripts to get the data inside AX. My test environment was:
Environment: WIN 2008 Enterprise 32 Bit, 4 GB RAM, MSSQL 2008 Developer SP1, AX 2009 SP1.
Test Dataset: 1,00,000 records into a table in a separate database in SQL 2008.
Objective: Insert these records in AX 2009.
Results: Inserted 1,00,000 records inside AX 2009 in 13.6 seconds (along with RecId, RecVersionId & DataAreaId). Used SSIS to bulk insert the records.
So how I went forward??
1. Created a sample database & sample table and populated with demo data.
The script for sample table create is as follows.
USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_PortalTrans]( [TransactionId] [nvarchar](20) NOT NULL, [TransactionReference] [nvarchar](30) NULL, [TransactionAmount] [numeric](28, 13) NULL) ON [PRIMARY]
GO
The script for sample data in the above table is as follows.
declare @counter int
set @counter = 0
while @counter <>
begin
set @counter = @counter + 1
insert into tbl_PortalTrans values ('TRID-'+cast(@counter as char),'Transaction Ref : ' + cast(@counter as char),
1000)
end
2. Created a sample target table & form in AX 2009.
This would be the ultimate target of these 1,00,000 records from the source table. The source xpo is attached in the download url.
3. Created a SSIS package to extract, transform & load data in AX 2009.
This was the meat of the application. I creates a SSIS package which reads the source tables, uses a few transformations to add extra columns to the data, use the custom script to generate the recid’s on the fly and finally push it inside AX 2009. I have also uploaded the VS 2008 SSIS package.
SSIS Control Flow objects.
SSIS Data Flow objects.
I’m also attaching a video on how this works :-)
The source files are present below at Sky drive. In case you want to use the package, you need to have a good understanding about SSIS 2008 in order to modify the package. To use it, please modify the package. The upload also contains the AX 2009 XPO files and scripts for creating the Source Table & demo data. The files also contain a detailed video.
Happy DAX-ing :)
Posts
3 comments:
Thanks for this tutorial, look into it.
I've figured out a fast way to import data in MS Dynamics as well. It uses almost only sql statements:
First let's create a table (classroom) in dynamics. It has two collumn: RoomNumber and LocationDescription (both String).
Now you want to load 1.500.000 (exact!) classrooms via a CSV file. It has the following sturcture:
A01;Teachers Room
A02;Green Room
C2002;Gymn
....
If you want to add lines in a table without using AX you should fill in the RECIDs properly: AX allows you to reserve X number of recids for a certain table. Use the following code:
static void reserve_classRooms(Args _args)
{
SystemSequence seq;
;
seq = new SystemSequence();
if(seq){
Seq.suspendRecIds(tablenum(classroom));
//In the next commando you reserve 1.500.000 recids. The system will not use these!
print seq.reserveValues(1500000,tablenum(classroom));
// Remove the recId suspension.
Seq.removeRecIdSuspension(tablenum(classroom));
pause;
}
}
The system will print a number to your screen. e.g: 3.000.000.000 Now you know you can use 3.000.000.000 until 3.001.500.000.
Your work in dynamics is now done.
In sql you recreate your table. Only you have to default your Dataareaid and you create an identity on your recid (starting with the value of previous job)
e.g.
create table tmp_classroom (
RoomNumber varchar(20),
LocationDescription varchar (50),
DataAreaId varchar(4) default 'dat',
Recid, bigint identity(3000000000,1)
)
Next you create a view on this table. This view represents the format of the csv file (in case extra collumns are not in the csv or the order is different)
e.g.
create view w
as
select roomnumber,locationdescription from classroom
Now you use the bulkinsert commando to load your csv in your view (or in your tmp_Classroom table)
e.g.
bulk insert w from '\\networklocation\classrooms.csv' with (FIELDTERMINATOR = ';')
everything is now correct in your tmp_table (you can do checks on it)
last thing you need to do is copy everything from the tmp_ table in the real table.
insert into classroom(roomnumber,locationdescription,dataareaid, recid) select roomnumber,locationdescription,dataareaid,recid from tmp_classroom
this is it. You can insert 4.500.000lines in less than 2 minutes. Because you're using powerfull sql server functions
Great hint to start with, but even easier with your job Karel.
Thanks
Hi Arijit,
Nice and very useful article, Please share post, Best method of Importing data into AX. Since there are many ways to insert data through writing Jobs, Classes, Built-in Excel Import functionality etc.
Based on your experience bulk data import and multiple tables which one you suggest.
Post a Comment