How can I import SQL data tables into Dynamics 365 CRM?

(0) Share

Report

Posted on by Community Member

So far I have been trying to import data into dynamics CRM using SSIS but it doesn't work. Problem is I have SQL data which has no integrity constraints while Dynamics CRM has it. I just want to simulate the SQL Import/Export wizard in my current requirement. Even those tables that don't have corresponding tables in CRM need to be added(client requirement). I know there are paid tools like Scribe etc. We want to achieve this without using any paid tools/subscriptions. Is there any way possible?

*This post is locked for comments

I have the same question (0)

All responses (14)

Answers (1)

Suggested answer

Mahadeo Matre 17,021 on at

Like (1)

Report

Hi..

You can do using SSIS packages.. you need to user either 3rd party tools like cozyrock, kingswaysoft or write your own SSIS plugin using script component in SSIS to import data.

In SSIS script component, you need to add CRM web service and you can insert record into CRM.

This is not easy way .. but you can do it..

hope this will help..

Was this reply helpful? Yes No
Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report

Hi Sahil,

Check this article might be useful for you.

zappysys.com/.../load-dynamics-crm-using-ssis-insert-upsert-delete-update

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you for the response Mahadev. I realized later I can write a C# code to make a connection with Dyanmics CRM but the main problem further would be mapping the SQL data with Dynamics as the SQL data is also from other CRM. And moreover there are integrity constraints in Dynamics which are not in our SQL tables. Do you know how to overcome this situation?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

It won't be a problem with single table or less than 10 tables. But with more than 100s of tables it's going to be a nightmare.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

@Mahadeo Matre

Was this reply helpful? Yes No
Verified answer

Mahadeo Matre 17,021 on at

Like (1)

Report

Hi Sahil,

do you want to move all CRM data to another CRM? like some production data to testing etc.

If this is the case then you can do just sql database backup and restore to testing evn. and using deployment manager you can create organization.

if this is not the case.. then do you have access to source CRM system, so that using API you can retrieve data from source CRM and then insert into your target CRM.

Retrieving / insert data from SQL for CRM is not recommend.. you need very good knowledge of CRM data structure to pull data from SQL

Please provide more details of your problem

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

So here is my overall problem:

My organisation uses Sage100 CRM portal. I was first asked to get that data into SQL server which I have got. I had to do this using Access as intermediary database because direct import in SQL wasn't working for connectivity with Sage so I had to use the former approach. Now that I have got the data in SQL server, I need to move it to dynamics 365 for analytics and visualization(These are our client requirements). I tried SSRS wherein a specify a for-each loop container for iterating each and every table in SQL server and move to CRM db but this won't work out as I can't keep on matching schemas of all 500 tables and moreover the referential integrity constraint in Dynamics. Moreover most of the tables in Sage either don't match or they are absent in Dynamics CRM.

I agree with you on the point:

"Retrieving / insert data from SQL for CRM is not recommend.. you need very good knowledge of CRM data structure to pull data from SQL"

Was this reply helpful? Yes No
Ummacrm 270 on at

Like (1)

Report

I think Mahadeo is on the right track trying to unpack how to help. I was thinking it might be helpful to take different approach to better understand what is happening here, with the following question that may be able to help cut through it all: Why do you want to achieve this without using any paid tools/subscriptions? KingswaySoft is going to be your best bet here. Are you trying to save money? The development man hours that are going to be required to architect and map the relationships and constraints you mention without the help of one of these paid tools will dwarf the amount of money you will have spent on said paid product. Here is an example: How are you going to handle activityPointerBase->Email->Attachment imports? That is an easy one, but just an example. I was under the impression your client had a small Source CRM system / SQL database you are looking to import, but you mention 100's of tables worth of data. If you are migrating a large CRM to Dynamics CRM, this is a large project to undertake and i am suspicious of the hesitation to purchase a tool that will make your life and your project many magnitutes easier.

Was this reply helpful? Yes No
Ummacrm 270 on at

Like (0)

Report

I replied before I saw this. There are still some concerns here regarding your approach: "Moreover most of the tables in Sage either don't match or they are absent in Dynamics CRM" amongst other statments. That would and should be anticipated. Migrating data from one system to another requires an immense amount of planning, along with a deep level of expertiese in either both target and source system, or database architecture in general. I mean, just trying to match a table that says Contact in Sage, and hammering that into Dynamics is not going to result in an excellent outcome

So how do we help? I'm not sure man. This is a big question and the reason most of us get paid to do what we do. I wish i could help further.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, my company is trying to save money.

I think you are right. Because whatever I found on Google asks to have a third-party tool to do the integration else it is going to be a hectic job.

Was this reply helpful? Yes No