Integrating CRM using SSIS

Question

Integrating CRM using SSIS

ashaaban

SSC Veteran

Points: 227
More actions
May 11, 2011 at 2:24 pm

#250162

Can any one provide any help on how to pull data from a database and integrate it with CRM using sql server 2008? I would really appreciate any hlep. thank you.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Could you be more specific as to your objective?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ashaaban SSC Veteran Points: 227 More actions · Answer 2

We have CRM implemented at my company, we have an application that deals with partipants data and benefits. what we are trying to do is to integrate the data(firstname, lastname, address, dob, dot.....) from this database into CRM. so that if a participant calls we can search CRM and find all the info that relates to this ptp. I hope this makes my idea more clear. thank you.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Please provide the schema and so dummy data...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ashaaban SSC Veteran Points: 227 More actions · Answer 4

sorry for the delay in responsing, I attached sample data that will be needed to be pulled into CRM. also I attached the query the I will need to use along with the structure with the tables being used. I just need to get an idea on how it works.

Thanks for your patience and your help.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

So you want to load the data in the Excel Document into the Tables that you have defined?

Is that correct?

Then you want to "integrate" into a CRM. What CRM you are using? Do you want to load the data into the CRM or exactly what do you want to do?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ashaaban SSC Veteran Points: 227 More actions · Answer 6

That is not correct. The 3 tables that I sent you currently exist in the application that uses sql server database. The data that I sent you was extracted out from those 3 table by using the query that I also included.

what I am trying to do is to see if it is possible to send these data to Microsoft Dynamic CRM 4.0. (read and allow update for some fields).

Thanks!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Yes, you can load into Dynamics. I did it 8-9 years ago, not a problem.

So are you asking how to do it, as in create a SSIS Package or what?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ashaaban SSC Veteran Points: 227 More actions · Answer 8

Yes please. Can you provide info on how to do this?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

You have to map the data from the data that you have in your SQL Server Database to the Tables in Great Plains (GP).

You need to make sure that you load the data in the correct sequence so that you do not violate any FK Constraints.

You need to ensure that the data source has all of the columns needed and that it does not violate any FK, check constraints, etc.

You can create an SSIS Package to load the Data from your tables into the GP.

I had to perform an initial load from legacy data into GP.

I hope this helps.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ashaaban SSC Veteran Points: 227 More actions · Answer 10

This gives me an idea Thanks, but if you can provide an example on this process or maybe useful links that would be great.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

I would first start out by comparing the schema in your tables against the Great Plains Tables (Map the Columns to the tables and the columns).

Then look at the data in your SQL Tables and determine if it violates any constraints.

You probable don't need to export to excel.

If you need help mapping then we need to take a look at tables in the CRM as well.

Regards,

Welsh

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ashaaban SSC Veteran Points: 227 More actions · Answer 12

thanks for the help.

Just to see if I got this idea right, so at the end I will need to schedule the job that will import the data to CRM database? right?

and if I need to update any of these fields, I am guessing I will need to export back the data from CRM to the other database, Is this correct?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

ashaaban (5/13/2011)
Just to see if I got this idea right, so at the end I will need to schedule the job that will import the data to CRM database? right?
and if I need to update any of these fields, I am guessing I will need to export back the data from CRM to the other database, Is this correct?

How much data (size) are we talking? You should be performing this on a Development Server that mimics your production environment.

The data should be current so that when you go to QA you can perform a somewhat realistic reconciliation of the data.

You may want to consider not starting out with a job but run the package(s).

ashaaban (5/13/2011)
and if I need to update any of these fields, I am guessing I will need to export back the data from CRM to the other database, Is this correct?

Are you trying to synchronize the data? Could you explain the requirement in greater detail so that I can understand exactly what you are attempting to do?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ashaaban SSC Veteran Points: 227 More actions · Answer 14

there are about 10000 ptps records.I am working on a development environment. Yes I want to make sure when I go to CRM and check ptp data, to be able to view the live data in the other application related to that ptp and vice versa. example: if an address was changed in the application for a ptp, I want to be able to see the new address when I check this ptp in CRM. Is this clear?