Loading millions of records from Oracle to CRM

(0) Share

Report

Posted on by Durgesh

1,290

Hello All,

So I am here trying to load around roughly 20 Millions records from Data Warehouse to CRM and i would like to run this job on a weekly basis. I have the data in Oracle and i would like to load that data in CRM as Price List. On the database side the process will truncate all the data and reload on weekly basis so i will have to design the job on weekly basis which will load that data into CRM after the data has been loaded into the database.

Please advice me if anyone have an idea of a good and faster integration tool which i can use.

And please let me know if you have any questions for me on this.

Thanks,

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Aileen Gusni 44,524 on at

Like (3)

Report

Hi Durgesh,

Usually, I use SSIS (SQL Server Integration Services)

and plus Data Connector to CRM, which is last time I used Kingswaysoft for the Telco project, and yes, I would still recommend this.

You could use Pentaho, but I think SSIS + Kingswaysoft Data Connector is better for integration to CRM because you don't need to call any CRM Web service or custom code during the data passing from data source to destination.

You can see here:

https://www.kingswaysoft.com/

You can download the trial or developer license to do some test drive, you can use the Visual Studio Integration Services Project to run the task, you just cannot deploy it using SQL Server Object Schedule.

For the Oracle data, to retrieve the tons of data, if you use Enterprise or Developer edition of SQL Server 2012, you could use Attunity Connector, this is the link

www.microsoft.com/.../details.aspx

I found that this connector make faster during data reading.

Then for data destination, you can use Kingswaysoft CRM Connector.

Hope it helps!

Thanks.

Was this reply helpful? Yes No
Durgesh 1,290 on at

Like (0)

Report

Aileen,

I really appreciate the response but is there any way that I can design Scribe jobs on a weekly basis to load data into CRM?

I am trying to load data into Price List entity which is not big as Account or Contact, but i am looking for this process to finish like within 8 hours or so.

Can you please give your thought on this?

Thanks,

Durgesh

Was this reply helpful? Yes No
Suggested answer

Nikesh Das 105 on at

Like (0)

Report

SSIS is a good option. You could also use a tool like Inaport, website: www.inaplex.com/.../inaport.aspx

This tool will allow you to run a command to start a job and this can be triggered by scheduled task or from an event. There are plenty of ways to integrate data into CRM, it just depends on what is best for your requirements.

Was this reply helpful? Yes No
Suggested answer

Jay1459 230 on at

Like (0)

Report

Microsoft Supports a company called SCRIBE for integrations, it requires little knowledge to set up and allows you to daily, weekly etc. create integration runs between two databases.

if you need a hand I work at a Microsoft Partner and I would be more than happy to get you set in the right direction.

Was this reply helpful? Yes No
Durgesh 1,290 on at

Like (0)

Report

Joe,

I really appreciate for the response and currently I am using Scribe to integrate data into the CRM, but there are roughly around 18 Million records and I need to create job on Weekly basis. I think it's impossible for Scribe to load that much of data into CRM. Do you think I need to do something on the Source table side? If you do have a suggestion please let me know.

Thanks,

Durgesh

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Durgesh,

Rather than diving in with products, it might be better to look at your problem.

You need to delete and insert 20m records in a overnight/end of day process eg. 8 hours.

I would look very hard to see if you can identify a change set and then update the CRM system based upon it. It might be a better idea to identify the weekly changes in Oracle PL/SQL first and then use a tool to push the changes into Dynamics CRM. The change set will the thinner (less columns to update + no create, update, delete workflows) and smaller (less read and write times).

SSIS can be fast but neither scribe or SSIS is not going to meet your 1400 rows per second (delete then insert in 8 hours) without some serious configuration. Even your indexes will cause major issues.

As for speed I would suggest Simego DS3 as it was designed with high performance Reconcile -> Sync.

Disclaimer: I work for Simego and help clients (mostly financial) synchronise their large balances/transaction data every day.

David Evans

Was this reply helpful? Yes No
pmdci 1,032 on at

Like (1)

Report

I agree with Aileen. I used Scribe but after working with SSIS (along with the Kingsway adapter), I feel that the investment on Scribe is unjustified (this is of course my personal opinion).

In particular, I feel that working with SSIS will appeal more to those with RMDBS and data warehousing background.

As for speed, this might be of little comfort right now (Jul/2014) but SQL Server 2014 has some in-memory enhancements that will help greatly with the I/O process. However CRM 2013 is not supported with SQL 2014 as of yet (but it will be with an eventual update).

Was this reply helpful? Yes No