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
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.
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.
Hi Mukesh,
ReplyDeleteIt seems interesting post.
JPG images are missing on this post though.
Could you also advice download url for XPO please.
Thanks
Nag
Images are not visible
ReplyDeleteCheck out the below vedio..
ReplyDeletehttp://www.youtube.com/watch?v=cYYij45N90c&feature=player_embedded
This comment has been removed by a blog administrator.
ReplyDelete