Arijit Basu: Dynamics AX 2009 Bulk Data insert using SSIS

[Blog bot]

Источник: http://daxguy.blogspot.com/2010/03/d...ert-using.html
==============

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



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 :)




Источник: http://daxguy.blogspot.com/2010/03/d...ert-using.html