One of the most wanted topics these days is using SQL Server Integration Services (SSIS) to Integrate with Microsoft Dynamics Business Solutions. Of course one of these solutions is the CRM .
A while back ago I had to transfer data from the CRM to an Internal ERP System that uses SQL Server 2008 Database as a Backend and vise versa.
So I had to think in a way that will really help me to do this in an efficient way and this way was using SSIS with the CRM SDK.
This post will be the first part of a series of walkthroughs that will help you to do almost a full integration with the CRM.
So Let’s Start 
My Lab Environment consist of the following :-
- Windows Server 2003 R2 that have Microsoft Dynamics CRM 4.0 and SQL Server 2008 Enterprise Edition.
- SQL Server 2008 Enterprise Edition with SSIS & BIDS Installed on my local machine.
The first step is that we need to create a database to demonstrate the external System and we will call it External_Data and we will create two tables one for the Customers and one for the Vendors and will fill them with a Dumb Data.
Open SQL Server Management Studio and Click New_Query.
Write the following Command :-
-- Create the DatabaseUse master;Create Database External_Data;
Go-- Create The TablesUse External_Data;Create Table Customers
(Cust_No Varchar(20) PRIMARY KEY,
Cust_Name Varchar(100),Cust_Email Varchar(100),Cust_Phone Varchar(50))Create Table Vendors
(Vend_No Varchar(20) PRIMARY KEY,
Vend_Name Varchar(100),Vend_Email Varchar(100),Vend_Phone Varchar(50))Go-- Insert RecordsInsert into Customers Values('Cust_0001','A Bike Store','someone@example.com','39654154')
Insert into Customers Values('Cust_0002','Affordable Sports Equipment','someone@example.com','97672579')
Insert into Customers Values('Cust_0003','Cash and Carry Bikes','someone@example.com','31459752')
Insert into Customers Values('Cust_0004','Friendly Neighborhood Bikes','someone@example.com','15498732')
Insert into Customers Values('Cust_0005','Metro Bike Works','someone@example.com','59285989')
Insert into Customers Values('Cust_0006','Modular Cycle Systems','someone@example.com','12958295')
Insert into Vendors Values('Vend_0001','Weekend Tours','someone@example.com','87652584')
Insert into Vendors Values('Vend_0002','Tuned cars','someone@example.com','97672579')
Insert into Vendors Values('Vend_0003','Variety cars','someone@example.com','31459752')
Insert into Vendors Values('Vend_0004','Transport Bikes','someone@example.com','15498732')
Insert into Vendors Values('Vend_0005','Sporty Store','someone@example.com','59285989')
Insert into Vendors Values('Vend_0006','Solid Bike Parts','someone@example.com','12958295')
Also we are not going to insert any records in the CRM as we are going to transfer these data to the CRM.
Now Let’s Open Visual Studio 2008 and create a new Integration Services Project per the following picture:-
Now in the Solution Explorer Under the SSIS Packages Node Rename Package.dtsx to Something like Transfer from Ext to CRM.dtsx .
The next step is to add two Data sources one for the CRM DB and the other for the External System DB.
So we will repeat the following steps to to add a Data Source for each Database :-
1. Right Click on the Data Sources Node in the Solution Explorer Window and Choose New Data Source.
2. Click Next.
3. You will now have two options. The first one is ( Create a data source based on an existing or new connection ) and the second one is ( Create a data source based on another object ) . We will choose the first one and we will click New .
4. Now we need to connect to our Data Source per the Following:-
(A) Provider: SQL Server Native Client 10.0 .
(B) Server Name: Server Name\Instance Name .
(C) We will choose SQL Server Authentication as I created one SQL Server user on each SQL Server Instance ( the one on My Local Machine and the one on CRM Server ) and I name it Integration_Admin and I gave him db_owner Permission on External_Data Database and db_datareader on Org_MSCRM DB.
(D) We will enter the Database Name and verify the Connection is Successful by clicking on Test Connection .
5. After clicking Ok in the previous picture We will Click Next and we will choose a name to the Data Source and Click Finish.
I think this is enough for the first part of the series as the next part will be more into our subject.
The next post will show you how to Insert records from the External Database to the CRM.
Thank you all for reading and Happy New Year
. Actually it is my first experience implementing a Microsoft communications server ever.
.
. Save your changes and Synchronize the Database Again.
.