Translate

Tuesday, July 19, 2011

Dynamics AX 2009 Bulk Data insert using SSIS

Scenario: Require 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
bulkupload.JPG

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


3. Created a sample target table & form 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.


bulkupload.JPG

SSIS Data Flow objects.

bulkupload.JPG

4 comments:

  1. Hi Mukesh,

    It seems interesting post.
    JPG images are missing on this post though.

    Could you also advice download url for XPO please.

    Thanks
    Nag

    ReplyDelete
  2. Check out the below vedio..

    http://www.youtube.com/watch?v=cYYij45N90c&feature=player_embedded

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete