Friday, December 31, 2010

Integrating Dynamics CRM using SSIS (Part 1)

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 Smile.

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 Database
Use master;
Create Database External_Data;
-- Create The Tables
Use 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))
-- Insert Records
Insert into Customers Values('Cust_0001','A Bike Store','','39654154')
Insert into Customers Values('Cust_0002','Affordable Sports Equipment','','97672579')
Insert into Customers Values('Cust_0003','Cash and Carry Bikes','','31459752')
Insert into Customers Values('Cust_0004','Friendly Neighborhood Bikes','','15498732')
Insert into Customers Values('Cust_0005','Metro Bike Works','','59285989')
Insert into Customers Values('Cust_0006','Modular Cycle Systems','','12958295')
Insert into Vendors Values('Vend_0001','Weekend Tours','','87652584')
Insert into Vendors Values('Vend_0002','Tuned cars','','97672579')
Insert into Vendors Values('Vend_0003','Variety cars','','31459752')
Insert into Vendors Values('Vend_0004','Transport Bikes','','15498732')
Insert into Vendors Values('Vend_0005','Sporty Store','','59285989')
Insert into Vendors Values('Vend_0006','Solid Bike Parts','','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:-

CRM_SSIS_Integration Create Solution

Now in the Solution Explorer Under the SSIS Packages Node Rename Package.dtsx to Something like Transfer from Ext to CRM.dtsx .

CRM_SSIS_Integration Rename Package 1

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.

CRM_SSIS_Integration Add DS1

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 .

CRM_SSIS_Integration Add DS2

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 .

CRM_SSIS_Integration Add DS3

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 Smile.

Monday, December 27, 2010

Microsoft Dynamics CRM 2011 Global Launch Event

A couple of weeks ago Microsoft Announced the availability of Microsoft Dynamics CRM 2011 Release Candidate (RC).

Also In a statement, Microsoft confirmed that this is the last external milestone before the CRM Online Release to Web (RTW) launch in January 2011, and the on-premises Release to Manufacturing (RTM) in Q1, 2011.

Also Microsoft announced that the Global Virtual Launch will be on January 20th, 2011.

To register please follow this link:-
Microsoft Dynamics CRM 2011 global launch event

Thank you all for reading :).

Saturday, December 25, 2010

Installing & Configuring Microsoft Lync 2010 Standard Edition ( First Experience )

A couple of days ago I had my first experience Installing & Configuring Microsoft Lync 2010 Standard Edition and it was AMAZING Smile. Actually it is my first experience implementing a Microsoft communications server ever.

Microsoft Lync 2010 is the new Unified Communications Platform from Microsoft ( Replacing Microsoft OCS ) and it has many new features that you really needs to get your hands on.

Microsoft Lync Features

Top 10 reasons to try Lync 2010

One of the new features that I really love is the Integration with the Office Suite and the SharePoint ( Skill Search ). This is of course in the End User side. Meanwhile in the Administration Side the most Beautiful thing is new Lync Control Panel and the Topology Builder.

Of course my next step is starting to discover the new Lync SDK and starting to do some Integration with the CRM. And of course I will share it with you Winking smile.

Below is some Great articles and Step-By-Step Guide that really helped me to implement the Lync Server (Of course big thanks to the great IT Professionals who wrote these guides) :-

See you soon in a new post regarding CRM.

Thursday, December 2, 2010

How to Implement a Successful Sales Process with CRM

It's been a while since i wrote anything . i was really busy at work and with my family.

But actually i prepared few Walkthroughs on Developing Custom CRM Integration using SSIS. I will post it Soon :).

Meanwhile Read this great post on How to Implement a Successful Sales Process with CRM - CRM Software Blog - CRM Non-Technical Blogs - Microsoft Dynamics Community .