Using SSIS to Import Data into Dynamics CRM

I am constantly being asked to import data into Dynamics CRM from various sources, and over time I have discovered that, even though the sources of data that I am importing vary, the process of performing the import is similar. Which tool I choose to use depends heavily on the source of the data, along with the format it comes in, and whether or not any manipulation needs to be performed prior to the actual import.

Even though 3rd party tools like Scribe <http://www.scribesoft.com/ > are great for doing these data imports, sometimes it isn’t the best fit for a client. There are times when SQL Server Integration Services (SSIS) are a better choice, and I will describe the steps to create an SSIS package to import data into Dynamics CRM. Using the out-of-the-box data import feature in Dynamics CRM is another option, but I would like to focus on the SSIS package in this article.

Data Source Definition

Before we can create an SSIS package to perform the import, we need to define a data source. This may already be defined for your scenario, but for this article I created a SQL database that represents account information.

SSISData1

Figure 1: Data Source for Accounts

 

Creating the SSIS Package

Once the data source has been defined, we can now use SQL Server Data Tools (SSDT) to create an Integration Services project. This project will define our SSIS package, which has the steps we need to perform to do the data import.

SSISData2

Figure 2: Integration Services Project

When a package is created in the project, it starts you out on the Control Flow tab (Figure 2). I can add either a Data Flow Task or an Execute SQL Task. I’m using a Data Flow Task in the package, as this will allow me to define the data source connection, the destination to import the data to, as well as any steps I want to perform.

The Data Flow tab displays a designer for the Data Flow Task(s) you define in the package (you can have more than one). This designer surface allows you to build out your source and destination connections, along with any manipulation steps you need to perform before the data is imported.

SSISData3

Figure 3: Data Flow Task in SSIS Project

For my package, I don’t need to manipulate the data before I import it into Dynamics CRM, so the only items I need to define in the Data Flow task are the source and destination. For the source, I’m using an OLE DB Source object, and this has been configured to go directly against the Accounts table in the database (Figure 4). I also mapped all the columns, as I want to import all this information, as seen in Figure 5.

SSISData4

Figure 4: Source Connection Definition

SSISData5

Figure 5: Column Mapping

 

Adding the Script Component

For the destination, we need to setup a Script Component as we will be using c# code (using the Dynamics CRM SDK assemblies) to import the data into our Dynamics CRM organization, as shown in Figure 6.

SSISData6

Figure 6: Script Component in SSIS

I then mapped the columns coming from the source data, so that my code can access this to perform the import. Figure 7 shows how I mapped the columns – you can select only the data columns you want to import, and also have the ability to rename the column names (if you want friendly names, for example).

SSISData7

Figure 7: Script Component Column Mapping

 

Adding Code to Perform the Data Import

Now we are ready to add the code that does the actual import of data into Dynamics CRM. Clicking on the Edit Script… button (Figure 6) will open Visual Studio with a special project type (VstaProjects) that contains a “shell” project which you can add code to.

There are 2 methods that we need to add code to, in order to connect to a Dynamics CRM instance and perform the data import. The first method is PreExecute() and we override this method so that we can first connect to a Dynamics CRM instance using our credentials. Figure 8 shows the format for connecting to a Dynamics CRM Online organization, along with the code (commented out) on how to connect to an on-premises Dynamics CRM organization. Please note that the UserName you use when connecting to CRM Online must be a valid CRM user in the organization you are connecting to.

SSISData8

Figure 8: Visual Studio Project from Script Component

The other method we need to add code to is Input0_ProcessInputRow(), and this method is executed for each row of data coming from the source. We want to create a new Account record in Dynamics CRM for each row, so Figure 9 shows the code that does this. Note that this code can get a lot more sophisticated, based on what you need to do during the import.

SSISData9

Figure 9: Code to Import Data

 

Final Thoughts

I came across a couple of issues when developing the Script Component for my SSIS package and would like to mention them here.

  • In order to use the Dynamics CRM SDK, I needed to add references to the Microsoft.xrm.sdk.dll and Microsoft.crm.sdk.proxy.dll files. I found that I received an “Assembly not found” error when running the SSIS package, and this was due to the fact that I needed to copy these DLL files to a specific directory under the local SQL Server installation directory location. Here is a link that describes the problem:

http://ssisdevelopers.wordpress.com/2013/04/09/ssis-script-task-filenotfoundexception-could-not-load-file-or-assembly-while-refering-to-third-party-dll/

  • I also ran across problems when attempting to use early binding in the Script Component Visual Studio project. When I added the cs file generated from the CrmSvcUtil.exe program to the project, I would get the “Assembly not loaded” error when running the project. Adding the DLL files (as described above) didn’t resolve the problem, so I resorted to use late binding.

So, if you are familiar with SSIS and need greater control over your data import process, the process explained here may be an option for you. I hope this article is helpful in providing another option for importing data into Dynamics CRM.

Cheers!

 

PowerView Reports in Dynamics CRM 2013

Dynamics CRM does a really good job of hosting and tracking a company’s critical business data, and the PowerBI technology stack does a great job of modeling and displaying business data to end users. While Dynamics CRM does a decent job of providing dashboards to surface this business data, I thought “wouldn’t it be great if CRM users had native access to PowerView reports while using Dynamics CRM”? Well, it turns out that with a little configuration you can provide these types of reports right in Dynamics CRM! Let me explain the process I took to do this:

First off, you need to use the PowerBI stack to extract data from a source (Dynamics CRM, for example), manipulate it in a PowerPivot (or SQL Server Analysis Services) data model and create a PowerView report. I did this for the Top 100 Global Companies and created a PowerMap report hosted in a SharePoint Online instance, as shown in Figure 1. Please refer to this post <<link to my other blog on surfacing LOB data into PowerView reports>> for further details on creating PowerView reports from LOB data sources.

PVR1

Figure 1: PowerMap Report in SharePoint Online

Once this is setup, you can then create a system dashboard in Dynamics CRM to host the PowerView report (Figure 2). Please note that this won’t work with Personal Dashboards, due to the fact that you need to configure the IFrame without restricting cross-domain scripting.

PVR2

Figure 2: Creating a System Dashboard in Dynamics CRM

You will use an IFrame on the dashboard to reference the PowerView report in SharePoint, so you can open the report in a separate window and copy the URL into the IFrame properties, under the URL field. You need to make sure the Restrict Cross-Domain Scripting checkbox is cleared, and should also verify that the URL you copy has the &action=embedview in the querystring (so it renders properly within the page).

PVR3

Figure 3: Creating an IFrame in Dynamics CRM

Once you configure this and publish to Dynamics CRM, you will then have a fully-functioning dashboard that displays PowerView reports! One final note to mention: authentication is an issue here, as the CRM user should also have a login to the SharePoint site that the PowerView report is hosted in, otherwise you will get a nasty error when displaying the dashboard (Figure 5).

PVR4

Figure 4: PowerMap Report in Dynamics CRM Dashboard

PVR5

Figure 5: Authentication Error with PowerView Reports in Dynamics CRM

I hope this blog helps others to quickly get these awesome reports working in Dynamics CRM. Enjoy!

 

Microsoft Dynamics CRM Use Rights (IUR) Program Changes

If you work for a Microsoft Partner and use Dynamics CRM or CRM Online, the current Internal Use Rights (IUR) program is changing. I recently spent some time planning for these changes in my organization, and I’d like to details these experiences so other partners can save some time and headache moving to the new IUR program.

 

Microsoft announced the changes to the IUR program a few months ago, so I won’t cover the details of those changes. More information can be found here (you will need login credentials to PartnerSource to access the page):

https://mbs.microsoft.com/partnersource/northamerica/news-events/news/msdcrmiurdisclosureofupcomingchanges

The preparation for the new IUR program is to have a migration plan in place prior to signing up for this new program, so you can be assured your users will be migrated successfully to the new plan. For myself, all this meant was to verify our tenant was upgraded from Commerce Transaction Platform (CTP) to Office 365 (which it was previously), and then check that the new program had enough CRM licenses to handle the number of users my organization had in our current system. Since we are a Dynamics CRM Gold partner, we receive 80 CRM Online licenses (down from the 250 in the retiring IUR program). Fortunately, the number of users we have in our current CRM system is below 80, so all I had to do was sign up for the new program and migrate over the users, and I should be good to go.

The instructions I received from Microsoft were very detailed and were easy to follow, so I proceeded to step through them. The starting point was to login to the Partner Digital Download site and navigate to the Microsoft Online Services tab:

MPNPage

This area displays the product keys that can be used to redeem for the IUR benefits that my organization is eligible for:

MPNOnlineServices

Once I clicked the link to redeem, I was taken to our Office 365 administrative panel where I logged in and followed the steps to redeem using the eligible product key token. The webpage then gives you an opportunity to purchase additional licenses but I didn’t need to do that.

At this point I was able to see the new IUR subscription listed in our Office 365 admin panel, alongside the IUR program that is being retired. The next step I took was to migrate the CRM licenses for the users to the new IUR program. I did that and verified in CRM that users with licenses in this new IUR program were listed as enabled users, while users that didn’t have a license were listed as disabled users in CRM.

There was only one issue I experienced when performing these steps and it really threw me for a loop, as it affected our CRM users in our production environment. We purchase additional storage space for our CRM Online environment, as the size of our database is larger than the 5GB provided when you provision a CRM environment.

When I completed the steps of signing up for the new IUR program and migrating the CRM user licenses, I began receiving messages that our production CRM environment has run out of space and that users cannot add records. This puzzled me and when I went to the Resources In Use screen (in Settings) I saw that our instance was only allocated the initial 5GB of space:

CRMOutOfStorage

I went back into Office 365 to verify that we were still purchasing the additional space for CRM and when I verified that, I felt it was time to bring in Microsoft Support to help.

In Microsoft’s defense, I usually have a good experience when reaching out to their support teams for help, but this issue was such a strange one that I think Microsoft was unclear how to help me resolve it. I won’t bore you with the details of each support team I talked to (I spoke with more than a few) but the bottom line is that the support team I spoke with that was able to help me was the Billing Support team. Let me repeat this for emphasis: Microsoft Billing was able to resolve a production-level technical problem I had.

As it turns out, if you previously purchased additional space for your CRM system (like we did), signing up for the new CRM IUR program somehow doesn’t automatically move your additional space purchase to the program, and thereby the CRM system doesn’t recognize that you are already paying for that additional storage space. MS Billing was able to provide me with a one-time signup web page that allowed me to request additional storage space that the new CRM IUR program does recognize, so once I did that our CRM environment recognized the additional space I purchased.

CRMPurchaseAddtlStorage

I hope this post helps if other partners experience issues migrating to the new IUR program, and also to show that Microsoft Support has a lot of great people that are dedicated to help partners solve their problems.

 

 

 

 

 

Integrating Yammer into Dynamics CRM 2013

Social selling is becoming more important these days, as companies are looking to find creative ways to generate more opportunities. Microsoft provides support for this in the Dynamics CRM space by providing integration with Yammer and CRM 2013. I’m going to show you how easy it is to setup Yammer to work with Dynamics CRM 2013, so your sales force can begin using its social features.

 

Prerequisites

  1. Yammer Enterprise. Integrating Yammer with CRM 2013 requires the enterprise version of Yammer
  2. The user performing the integration with Yammer requires system administrator privileges in CRM 2013
  3. The user performing the integration with Yammer requires system administrator privileges for your organization’s Yammer account
  4. Install the most recent product updates for Dynamics CRM 2013

 

Steps

  1. Login to Dynamics CRM 2013 and navigate to the Settings area. This is done by clicking on Microsoft Dynamics CRM –> Settings
  2. Navigate to the Administration section by clicking on Settings –> Administration
  3. Click on the link titled “Yammer Configuration” to begin the wizard steps to enable the integration. Please note that you will not see this link if you do not have System Administrator privileges in Dynamics CRM.

0

  1. A Yammer Disclaimer page will display, asking you to agree to the disclaimer. Click on Continue to proceed.

1

  1. A configuration page will appear, with steps listed on how to connect your Yammer environment to Dynamics CRM 2013. Notice that only the first step (“Authorize Microsoft Dynamics CRM Online to connect to Yammer”) will be enabled at this point. Click on this link to perform the authorization step.

2

  1. A dialog will display requesting you enter your Yammer credentials for the authorization. Enter these credentials and click the “Log In” button.

3

  1. Another dialog will be displayed that asks you to confirm the connection between Yammer and Dynamics CRM 2013. Click on the “Allow” button to allow this connection.
  2. At this point the integration is performed and complete, but the process takes you back to the Yammer configuration page, and you will notice that steps 2 and 3 are now enabled. These are optional steps that you can configure for specific items/behavior with the Yammer integration.

4

  1. (Optional) Select a Yammer Group ID to control conversation access. This optional step allows you to configure whether all the conversations initiated from Dynamics CRM should be placed in a specific group in Yammer. If you don’t specify a group, the All Company Group is used for this.
  2. (Optional) Set the level of security for Yammer activity stream messages. This optional step allows you to configure whether the conversations in Dynamics CRM are visible to everyone (Public). Selecting Private for this setting will require users in Dynamics CRM to follow the appropriate record in order to see the Yammer conversations.

 

Configuring Entities

Once Yammer has been connected to Dynamics CRM 2013, you need to specify which entities in CRM you wish to enable for use with Yammer. This can be done by navigating to the Post Configuration section in Settings (click Settings –>Post Configurations).

In order to activate an entity for use with Yammer, select the desired entity and click the Activate selection in the top navigation bar. Deactivating an entity for use with Yammer is done by clicking the Deactivate selection in the top navigation bar. Please make sure you Publish All Customizations to verify that the changes you made take effect in Dynamics CRM.

6

Reactivating an inactivated entity for Post Configurations will restore any conversations in Yammer (they are not deleted when you inactivate an entity as the messages are stored in Yammer, not Dynamics CRM).

 

Issues to be Aware Of

There is only one issue I encountered when configuring the Yammer integration with Dynamics CRM 2013. You need to make sure you have your URLs for your Dynamics CRM organization as well as the URL for Yammer in your Trusted Sites zone in Internet Explorer options. Since my Dynamics CRM environment is online, the URL I used was https://*.crm.dynamics.com, and https://www.yammer.com. If you forget to do this, you may run into an issue when you complete Step 6 (I encountered a blank screen that sat there forever).

5b

Conclusion

That’s it! You now have the social power of Yammer integrated into Dynamics CRM 2013, and can begin to leverage the benefits of social tools for your Dynamics CRM environment!

 

What’s New in Dynamics CRM 2013

With the newest release of Microsoft Dynamics CRM, there have been some interesting enhancements that I’m excited to show you today! This blog is the first in a series that will detail out the new features and functionality in Dynamics CRM 2013. I will describe in more detail the new 2013 functionality in later blog posts.

Dynamics CRM 2013 has been improved in the following areas:

UI Changes

The user interface (UI) in Dynamics CRM 2013 has been redesigned to provide a cleaner and more efficient experience for CRM users. Navigation has changed, along with “flattening” out the screens displayed (i.e. no popup dialogs). Auto-saving, integrated Bing maps, and inline editing of information on forms are just a few of the changes in this redesigned UI experience.

A brief overview video can be found here: http://www.youtube.com/watch?v=EScDpwxRuiI&feature=youtu.be

Business Processes

Guided business processes can now be created in Dynamics CRM 2013 that allows users to follow a clearly outlined set of process stages. These stages help define the tasks to be completed before moving to the next stage in the process. Processes are configurable, and are able to be designed for multiple types of processes, based on desired needs.

A brief overview video can be found here: http://www.youtube.com/watch?v=9cXoivs0ZMA

CRM 2013 for Tablets

The new mobile support created in Dynamics CRM 2013 is designed to be an immersive, touch-first experience for CRM users who need to stay connected to their customers, team members, and data. This mobile application is designed for use with a Windows 8 tablet device. The new mobile support allows CRM users to update data or work their business processes in CRM directly while in the field, when the information is still fresh in their minds.

A brief overview video can be found here: http://www.youtube.com/watch?v=1H23cYvQcys

Yammer

Yammer is now integrated into Dynamics CRM 2013, allowing users the ability to collaborate socially in the context of the task they are working on. Opportunities, Cases, or Campaigns are examples of entities that you can have specific, contextual Yammer conversations with. This “social CRM” capability allows users to put their business strategies into action more quickly and effectively.

More information can be found here: http://www.microsoft.com/en-us/dynamics/crm-social.aspx

Functionality present in Dynamics CRM 2011 is intact (for the most part), so if you are familiar with that version of CRM, a lot of what is in CRM 2013 will already be familiar. Be on the lookout for future blog posts that dive deeper into the functionality areas listed above.

 

SharePoint 2010 Web Parts and CRM 2011

Recently I was asked to create a web part in SharePoint 2010 that would allow users to create and update entities in CRM 2011. There were some interesting challenges to overcome, so I thought I’d detail the process I went through, so others could benefit from my experience.

Initially, I thought this task would be easy, as the CRM 2011 SDK provides sample code that calls into the CRM 2011 web services to perform CRUD (Create, Read, Update or Delete) operations. However, I quickly discovered that the CRM 2011 SDK code requires references to the .NET Framework 4.0 DLLs, and since SharePoint 2010 web parts do not support .NET 4.0, I encountered compilation errors in my web part project.

Pic1

The solution I came up with was to create a “middle tier” WCF web service that would perform the calls to the CRM 2011 web services. This WCF web service can be created using the .NET Framework 4.0 to get past the compilation errors, and the ServiceContract can be defined with methods that mirror what is provided in the CRM 2011 web services.

I started by creating a new WCF Services project in Visual Studio 2010, selecting the WCF Service Application project template, and making sure I selected .NET 4.0 as the framework:

Pic2

Once the new project is created, I was able to define the ServiceContract for the web service, as shown below:

namespace CRM2011MiddlewareService 
{ 
   [ServiceContract] public interface IService1 
   { 
      [OperationContract] string GetMessage(); 
      [OperationContract] void AddCRMAccount(string strAccountName); 
      [OperationContract] void AddCRMCase(string strCaseName, string strAccountName); 
   } 
}
Figure 1: Code for ServiceContract

I also needed to add service references in the project to the CRM web services, as listed here:

http://<<server name>>/XRMDeployment/2011/Deployment.svc

http://<<server name>>/XRMServices/2011/Discovery.svc

http://<<server name>>/XRMServices/2011/Organization.svc

Pic3

 

Once this “middle tier” web service is published to IIS, it can be consumed by a typical web part project in Visual Studio 2010. I created this visual web part project and added a service reference to my “middle tier” web service, so I can call the methods that will create the CRM entity I am interested in creating.

namespace VisualWebPartProject1.VisualWebPart1 
{ 
   public partial class VisualWebPart1UserControl : UserControl 
   { 
      protected void Page_Load(object sender, EventArgs e) { } 
      protected void Button1_Click(object sender, EventArgs e) 
      { 
         Service1Client proxy = new Service1Client(); 
         string strCaseName = txtCaseName.Text; 
         proxy.AddCRMCase(strCaseName, ""); 
      } 
   } 
}
Figure 2: Code for Visual Web Part Project

 

From here, creating a simple visual web part for SharePoint 2010 is a piece of cake, and once it is deployed and activated in SharePoint, I was able to test it out to make sure it works properly.

 

In conclusion, creating a web part in SharePoint 2010 to consume methods from the CRM 2011 web service requires that you create a “middle tier” WCF web service to call the CRM 2011 web service. This “middle tier” web service can then be called by your web part in SharePoint 2010. My example here just scratches the surface of what the CRM 2011 web service provides, but shows the process I took to get a web part in SharePoint 2010 working with CRM 2011.