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 .

Sunday, October 10, 2010

Error When Publishing Workflows in Dynamics CRM 4.0 (an error occurred when the workflow was being created. try to save the workflow again).

A couple of days ago I received the above error when trying to publish a workflow in CRM. After some searching I found out that this error may occur after installing Update Rollup2 Update Rollup2 For Microsoft Dynamics CRM 4.0  .
What actually happened is the the following line has been removed from web.config for CRM Web Application.

<authorizedType Assembly="mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" Namespace="System.Globalization" TypeName="CultureInfo" Authorized="True"/> 

So what you need to do is adding the above line under <authorizedType> Node in web.config  and execute IISReset /Noforce .

That’s it.

Thursday, October 7, 2010

Error While Synchronizing Database In Dynamics AX 2009 (There is a field mismatch in the union query. Field AccountNum is not compatible with field PartyId.)


Yesterday I was trying to install Dynamics AX 2009 . Suddenly I received the following error when I started the Synchronize Database Step in Initialization Checklist :-

Error While Synchronizing Database In Dynamics AX 2009 (There is a field mismatch in the union query. Field AccountNum is not compatible with field PartyId 1

Then I started to think why this error occurred. Maybe because I changed some of the properties in the Modify Data Types Step . So I returned back to Modify Data Types Step and changed back everything to their Default Values but unfortunately everything is the same . I tried to Synchronize again and the error occurred.

So I finished all the Steps in Initialization Checklist and started to investigate.

I opened AOT > Data Dictionaries > Views > Dirpartyview > Fields .

And then I saw the StringSize Property of both the fields ( AccountNum, PartyId ). and Woooh they are not the same. so that’s why they are not compatible.

Error While Synchronizing Database In Dynamics AX 2009 (There is a field mismatch in the union query. Field AccountNum is not compatible with field PartyId 2


Error While Synchronizing Database In Dynamics AX 2009 (There is a field mismatch in the union query. Field AccountNum is not compatible with field PartyId 3


If you noticed both fields Data Source Property is the CustTable . so what you need to do is going to CustTable and right click on PartyId field and choose Add-Ins > Open new window > Open used Extended Data Type

Error While Synchronizing Database In Dynamics AX 2009 (There is a field mismatch in the union query. Field AccountNum is not compatible with field PartyId 4


Then you will notice StringSize Property of DirPartyid changed it to be the same as AccountNum StringSize Property.

Error While Synchronizing Database In Dynamics AX 2009 (There is a field mismatch in the union query. Field AccountNum is not compatible with field PartyId 5


That’s it Smile . Save your changes and Synchronize the Database Again.

Maybe you will have warnings that few tables will be dropped and recreated but it is okay as long as you don’t have any data yet.



I didn’t know what is exactly the reason for this error. Maybe it is bug or maybe something wrong I did in the Initialization but there’s a solution for it Smile.

Sunday, October 3, 2010

Microsoft Dynamics CRM 4.0 Build Numbers ( Rollups)


I've seen many times people asking what is my CRM current version ? Which Rollup i should apply ? . Below is a table that will show you each CRM build number and the Rollup Versions:-

Microsoft Dynamics CRM 4.0 Build Numbers

Version Build Number Release Date KB Article
RTM 4.0.7333.3 12/19/2007 ###
Rollup 1 4.0.7333.1113 11/24/2008 952858
Rollup 2 4.0.7333.1312 or
Rollup 3 4.0.7333.1408 3/12/2009 961768
Rollup 4 4.0.7333.1551 5/7/2009 968176
Rollup 5 44.0.7333.1644 or
7/2/2009 970141
Rollup 6 4.0.7333.1750 9/27/2009 970148
Rollup 7 4.0.7333.2138 10/22/2009 971782
Rollup 8 4.0.7333.2542 12/17/2009 975995
Rollup 9 4.0.7333.2644 02/11/2010 977650
Rollup 10 4.0.7333.2741 04/08/2010 979347
Rollup 11 44.0.7333.2861 or
06/03/2010 981328
Rollup 12 4.0.7333.2935 08/02/2010 2028381
Rollup 13 4.0.7333.3018 09/23/2010 2267499
Rollup 14 4.0.7333.3135 11/18/2010 2389019
Rollup 15 4.0.7333.3231 13/01/2011 2449283














Mouse Over the Rollup Version or KB Article Number to go to the appropriate Page.

To determine what is your current Rollup Number in CRM Web Client or CRM Outlook Client go to:-

Help > About Microsoft Dynamics CRM .

CRM 4.0 Build Numbers About

CRM 4.0 Build Numbers About 2

Also if you noticed a different number than the above mentioned, than you've likely installed a Hotfix Click here to Go to the Hotfixes Page.

Of course I will keep this Post Up-to-date with the Upcoming Rollups .

Happy updates Smile.

Wednesday, September 29, 2010

Welcome To My Blog

Hello All :)

Welcome to my Blog. As i mentioned in the subject , i will be focusing on how to build and improve Microsoft Dynamics CRM Implementations and make your life more Easier :)

In this Blog i will Post about :-
  1. Problems and Solutions.
  2. Customization.
  3. Building a Custom Integration Module Using SDK ( Plugins, Workflows, SSIS Integration, etc....  )
  4. Exploring the new features inside Microsoft Dynamics CRM 2011 .
  5. Implementing a Good Business Strategy Using SureStep Methdology.
  6. Answer your Question and Concerns :):) .
If you have any questions you can ask me by Replying to my Posts or by Sending an email to me . I will do my best to help you.