Data Integration/Migration using SQL Integration Services (SSIS) 2008

Today’s guest blogger is CRM MVP Darren Liu who is a CRM specialist at Sonoma Partners in Chicago.
I wrote a blog article last year on how to integrate Microsoft Dynamics CRM using SQL Integration Service (SSIS) 2005 (http://tiny.cc/BQiiR) . I hope that article provided you with an alternative solution for your integration/data migration project with CRM. Due to the limited capabilities in SSIS 2005 with CRM web services, we created a proxy class as a work around to provide easy access to the CRM API.
With the new release of SQL server 2008, there were few improvements that simplify the integration of SSIS and CRM. It does not require you to create the proxy class anymore since SSIS 2008 allows you to add web references within the script component object. It also allows you to code in the language that I like the most, C#.
Here I would like to share how to leverage SSIS 2008 to integrate with CRM without the proxy class so that you can use it on your next CRM data integration/migration project.
Before we get started, here’s the list of requirements:

• SQL Server 2008 Standard/Enterprise Edition with SQL Integration Service Installed
• Microsoft Dynamics CRM 4.0
• Visual Studio 2008 Professional Edition SP 1 with Business Intelligence Tools Installed
• CRM SDK and C# knowledge

In this blog, I will use an example to show you how to send contact data stored in an Excel document to MSCRM 4.0 via CRM Web Services using SSIS.
Source Data
Source data is data from the other system that you would like to send to the CRM system. Your source data can be a text file, a database, etc… Since we often use Excel to collect our information, I will use a simple Excel document as my source data for this blog article.
Source Data: Excel Spread Sheet


Create SSIS Package
Launch Visual Studio 2008 to start a New Integration Services Project
After creating the project, follow the steps below to set up the SSIS package.

- Rename Package.dtsx to Contact.dtsx

Add Control Flow Items
Drag and drop a “Data Flow Task” from the Toolbox to the Control Flow Design Pane.

Add Data Flow Items
Double click on the Data Flow Task item that you just added and it will take you to the Data Flow Design Pane. Here we will specify the source data and also to write script to send data to CRM.
Specify Source Data

- Since our source data is an Excel document, drag and drop the Excel Source from the Toolbox to the design pane.
- Double click Excel Source to open the Excel Source Editor.
- Click New… button to open the Excel Connection Manager to specify the Excel file path, and then click OK.

- Select “Table or View” from Data access mode dropdown box.
- Select “Sheet1$” from Name of the Excel sheet dropdown box.
- Click OK to close the Excel Source Editor window.

Setup Script Component

- Drag and Drop Script Component to the design pane.
- Select Transformation and then click OK.
- Connect the two shapes by dragging the green arrow from Excel Source to Script Component.
- Double click the script component to open up the Script Transformation Editor.
- Select the columns that you would like to send to MSCRM from the Input Column window. In this example, I selected First Name, Last Name, Phone and Email Address.
- Remove Output in the Inputs and Outputs section since we are not going to output anything in this example.
- Click on the Script tab, click on Edit Script button. The Visual Studio window should open.

Add CRM Services
Since SSIS 2008 allows you to add web references in the Script Component, we will add the two web service references in this step. Please be aware that you must save the script component project by clicking the Save button on the toolbar after you added the CRM web references, otherwise the web references will not load next time you reopen the script component.
- Right click on the project in the Project Explorer window.
- Select Add Web Reference… from the menu.

- Repeat the steps above to add the CRM metadata service if necessary.
Coding the Package
In order to use the web reference in our code, we need to include the CrmSdk web reference to the script component project. To get the script component namespace, right click on the project and select Properties… from the menu. The namespace is in the Default namespace textbox. In this example, my script component name space is SC_ad0e4b91cb7e48cdb8fa2d240e3e5c30.csproj.

I added the following statement to my project.

using SC_ad0e4b91cb7e48cdb8fa2d240e3e5c30.csproj.CrmSdk;

Lastly, copy and paste the following code to the ScriptMain section:

private CrmService service = null;

    public override void PreExecute()
    {
        base.PreExecute();

        CrmAuthenticationToken token = new CrmAuthenticationToken();
        token.AuthenticationType = 0;
        token.OrganizationName = "AdventureWorkCycles";

        service = new CrmService();
        service.Url = "http://localhost/mscrmservices/2007/crmservice.asmx";
        service.CrmAuthenticationTokenValue = token;
        service.Credentials = System.Net.CredentialCache.DefaultCredentials;
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void ContactInput_ProcessInputRow(ContactInputBuffer Row)
    {
        contact cont = new contact();

        if (!Row.FirstName_IsNull)
        {
            cont.firstname = Row.FirstName;
        }

        if (!Row.LastName_IsNull)
        {
            cont.lastname = Row.LastName;
        }

        if (!Row.Phone_IsNull)
        {
            cont.telephone1 = Row.Phone;
        }

        if (!Row.Email_IsNull)
        {
            cont.emailaddress1 = Row.Email;
        }

        service.Create(cont);
    }

Execute the SSIS package
After coding the SSIS package, right-click on the Contact.dtsx package and then select Execute Package. After the package has executed successfully, you should see the records in CRM.

Deploy the SSIS Package
After successfully testing the package, deploying the package is pretty easy. It requires the same steps as the previous version of SSIS. I have included the steps again below.

- Right-click on the CRM 4.0 SSIS project and then select Properties.
- Click on the Deployment Utility tab and set the Create Deployment Utility property to True.

- Recompile the CRM 4.0 SSIS project. You should see CRM 4.0 SSIS.SSISDeploymentManifest in the bin\Deployment folder.
- Double-click on the manifest file and follow the wizard to deploy the SSIS package to your SQL server.

Summary
That’s all there is to it! Hopefully you have gotten the idea of how to use the latest version of SSIS to send data to CRM. SSIS 2008 has a lot of improvements to make our jobs easier to integrate systems. In this sample, I only demonstrated how to import records in CRM. In an actual data integration or migration implementation, we still have a lot more to consider such as updating, deleting and error handling. This is one of the many approaches that you can use to integrate/migrate data with CRM. I hope this will help you in your next CRM project.

Cheers,
Darren Liu

Read more »

Posted in: CRM 2011 Integration,CRM 4.0 - CRM 2011 Migration

Lessons Learned Migrating Data to Microsoft Dynamics CRM 2011

Lessons Learned Migrating Data to Microsoft Dynamics CRM 2011

When companies using Microsoft Dynamics CRM 3.0 or 4.0 move to Microsoft Dynamics CRM 2011, there are some decisions to be made around how to move your current environment and data to CRM 2011. If you have an on premises installation, you can install CRM 2011 on premises and upgrade your MSCRM database to CRM 2011. This will bring in all of your customizations and data from CRM 4.0.
Upgrade or Migrate?
In some cases, a direct upgrade may not be practical, and you may want to selectively migrate data:

1. If your existing data is not clean, you may want to start clean with 2011 and selectively migrate just the good data. As an example, consider if at one time you used contracts in CRM 4.0, but you changed your configuration to manage contracts in a different way—you may not want to bring in the legacy contract data if it no longer fits your current approach.
2. If your environment is older than CRM 4.0, you may want to do a migration. if you are on CRM 3.0 or CRM 1.2, you cannot directly upgrade to CRM 2011—you have to do incremental upgrades. Say you have CRM 1.2, to upgrade you would need to upgrade to 3.0 on your 1.2 server, then install crm 3.0 on a Windows 2003 server with SQL 2005 and upgrade to CRM 4.0, then install CRM 2011 on a 64 Bit CRM Windows 2008/SQL 2008 environment and import and upgrade your 4.0 environment to 2011. The further away you are from 4.0, migration of the data becomes more practical then upgrading, given that there is potential risk of the process failing at each point of the upgrade.
3. If you are moving from CRM on premises to CRM Online, you will need to migrate your data. There is currently no automatic upgrade process for Dynamics CRM on premises to CRM Online. You can import customizations from CRM on premises to CRM Online; however, moving data still requires a migration.

Migration Options
If you decide to do a data migration to CRM 2011, there are a couple of options to consider
1. Data import utility: CRM 2011 includes a data import utility, and it can take data exported from flat files and import them into CRM 2011. This tool is much improved from CRM 4.0, and it now handles larger data files and improved validation for data types, so you can more reliably import data. Using the tool you can easily import your accounts, contacts, opportunities, and other standard or custom entities.

There are some areas that are not accessible via the import utility. Some of these areas include:

• Activity attachments
• Certain parts of the product catalog
• Activity parties for e-mails with more than one recipient
• Notes with attachments
• Contracts
• If your data is mainly core entities like accounts and contacts, you should be able to use the import utility; however, if you have many activity parties or attachments, the import utility may not be able to completely migrate your legacy data.
• User mapping for ownerid and other user lookups can be problematic, especially if names are different in the new version, or if old users no longer exist.
• In some cases, you will want to update records after they are imported. For example, Accounts and Contacts can present a classic “chicken and egg” scenario. Contacts reference accounts, and accounts reference primary contacts. If you insert companies first, you won’t be able to populate the primary contact on the company record before the contact is created. You will need to insert the companies, insert the contacts, and then come back and update the company to set the primary contact. The import utility can update records if the GUID is in the first column of your csv file; however, this can require a bit of manual work to update the existing records.

2. Scribe Insight: Scribe is the leading vendor for CRM data migrations and integrations. Scribe provides an adapter to connect to CRM 2011 as well as earlier versions of Microsoft Dynamics CRM, and can easily load data into entities that cannot be loaded with the standard import utility.
We have migrated several of our clients as well as our internal environment from CRM 4.0 to CRM 2011 online. As part of these migrations, each had data in entities that could not be migrated using the standard import utility. For these migrations, we chose to use Scribe.
As referenced in my earlier post, Scribe includes an auto-map feature that can simplify the mapping of the data for your import. Just specify the legacy environment as your source, select the new environment as your target, and auto link by name.
There are a few things you won’t want to auto map:

• userid—chances are that your user id’s will be different in the target than they are in the source. I did a dblookup formula in Scribe to translate the fullname of the user in the legacy system to the userid of the user in the new system.
• Transactioncurrency—unless you have more than one currency, you can leave this field blank.
• AddressID—you will see for each address on accounts and contacts a field called addressid (Address1addressid and address2addressid). Do not auto map these.
• Statecode and statuscode—when I brought in my accounts, contacts, e-mails, appointments, etc, I did not map the statecode and statuscode fields. The reason is that if you set a record to be inactive, you cannot relate other records to it. There is a good chance that you will have activities, notes, contacts, or other records that are linked to inactive records. By leaving them active you can establish all relationships, then run a simple update dts at the end of the process to close out the inactive records

By doing this, I was able to quickly import the records from my on premises CRM environment to CRM Online.
Limitations
There are a handful of limitations that I found through this process that Scribe and the import utility cannot import. These are mainly some deeper areas of CRM that are not exposed through the API.

• Quick campaigns are entity bulkoperation, and they cannot be imported by Scribe or the import utility. This also means that activities like email where the regardingobjectid is set to a bulkoperation record will also not be able to be imported
• Contracts have limitations around how they can be imported—they can only be imported in draft status, and you cannot associate other records, such as cases with them if they are not Active status and have an expiration date in the future. Manual intervention will be required to import cases and set them to a state that can be updated.
• When importing opportunities, the actualclosedate will be set to today’s date, even if you try to load another date to that field. The recommendation is after you update the status of the closed opportunities to Closed, run an update dts against opportunities updating the actualclosedate to the correct date.
• If you are reading from your legacy CRM database as a SQL or ODBC connection, be aware that if there are any ntext or long varchar fields, they must come last in your source query. If they don’t, the field will appear null and data will not come across for these fields. This is especially applicable for things like activity description fields. If your entity has more than one long varchar field, you may need to run subsequent update dts with the other long field listed last in the source query. An alternative approach is to install the new CRM 2011 adapter and point the old adapter to your legacy CRM system as your source, and point the 2011 adapter to the new CRM environment as your target. The crm adapter does not have the long varchar limitation.

Lessons Learned
In an effort to help others learn from my mistakes, here are some of my lessons learned after a couple of migration upgrades:
1. Determine which entities are being used. The easiest way to do this is to look at your SQL MSCRM database. View tables by number of records—this will help you determine where data is, and where it is not.
2. Determine the order—It is crucial that you import your data in the right order, so that data referenced in lookup fields is present when the record is imported. For example, you need to have your accounts in before you load your opportunities. As a general rule, have any accounts, contacts, opportunities, quotes, orders in before you load cases, and load activities last.
3. Don’t close cases or other records until all activities have been loaded, and don’t close activities until all activityparties and attachments have been loaded.
4. Activityparties are probably the most difficult thing to import. If you don’t know, activity parties are the people and companies associated with activities like e-mails in the to: field or the appointment in the requiredattendee field.

• If you have deleted any activities from your crm system, there is a good chance that some activityparties will be left behind.
• When you create an e-mail and track it in CRM, if one of the recipients is not in CRM as a contact, account, or user, it will create an activity party not linked to any partyid in CRM. These records cannot be imported.
• When you create an activity in CRM, activity parties are created for the sender, the recipient, but also the owner and the regarding of the activity. When you import the activities, these activityparties will automatically be created. If you then import the activity parties including these parties, some rows will fail telling you that they already exist.
• Activityparties cannot be easily deleted.

To avoid these headaches, set up your activityparty dts source query to inner join activityparty to activitypointer to filter out any activityparties linked to an activity that was deleted from the system. Also, filter out where activitypartyid is null and activitypartytypecodemaskname = “Regarding” or “owner.” This will cut the list down to just the legitimate activityparties, and save much time.
Remembering that activityparties can’t be deleted, if you have several hundred thousand activityparties in your database, you will want to break this process up into smaller chunks, maybe by year. That way you minimize the chance of the job failing, and having to re-load any data. You can also run them simultaneously and load the data faster.
Once the activityparty load was complete, I then ran a cleanup dts that joined the activityparties where partyid is null to contacts on addressused = emailaddress1. This successfully matched most of the unmatched activityparties, and I loaded setting partyid to contactid.
5. Users: If you have been using CRM for several years, there is a good chance that there are users in your system who have been deactivated, and that those users own records, such as activities. Keep in mind that to assign a record to a user, the user must be active. So if you have a former employee named Bob Smith, if you want to import activities and assign them to Bob, you will need to temporarily have his user record active in the new system. This means creating records for users who are no longer at the company.
For our migration, we created users for former employees. Given that CRM Online uses Windows Live ID for authentication, you might think this means that you have to set up legitimate Windows Live ID’s for each old user; however, this is not the case. You can create users using any made-up address, and save the user without sending an invitation to that address. You can then assign records to the user, and then disable the user record.
An alternative approach is to create a generic user called “former employee” and have Scribe assign any records owned by a former employee to this user. While this approach is faster, it may detract from visibility to who created the record.
6. Do not load directly to the activity entity. You have to load to the individual activity type entities—email, appointment, phonecall, or task.

Keeping these best practices in mind, you can quickly migrate your data from CRM 4.0 to CRM 2011 Online.

Read more »

Posted in: CRM 4.0 - CRM 2011 Migration

Migrating from CRM 4.0 to Dynamics CRM 2011

Scenario: You’ve installed Dynamics CRM 2011 on a fresh set of Windows Server 2008 and SQL Server 2008 64-bit servers, and successfully performed a test migration on your CRM 4.0 production organization .
Microsoft refers to this as the “migration” upgrade path, and while it’s not the only approach you can take, it’s probably the best one. Some of the advantages:

• Can test an upgrade from CRM 4 to CRM 2011 with no impact on your current 4.0 production environment.
• If upgrade fails no impact on production.
• Minimal downtime for upgrade (just the time it takes to actually import the CRM 4.0 database into the CRM 2011 format in the deployment manager)

Anyway, you test away for a few weeks, implementing some customizations and maybe even configuring IFD. Your users have been happily updating the CRM 4.0 production database, and now you’re ready to upgrade, so you make a backup (to a BAK file) of the 4.0 SQL Server database, create what will become your production database on the 64-bit SQL Server 2008 in your CRM 2011 deployment, and restore the database to it.
You then go into the much improved CRM 2011 Deployment Manager and run the Import Organization wizard, and when you get to the point to import the organization into CRM 2011 format…you get this message:
“you have already imported and upgraded this organization and cannot perform the operation again…”
And see something like the following screen shot:

There are lots of questions about it, as you can see if you search Google or Bing for the error message. Not a lot of answers, however. So if you can’t import a 4.0 organization more than once, how can you test?
I’m not quite sure why this would be “by design”, as most of the threads you’ll find say, and it doesn’t seem very elegant, but here’s a workaround:
In CRM 2011

1. Create a solution package and import any customizations you want to preserve
   
2. Export the solution package to the solution XML file
   

In the CRM 2011 Deployment Manager

1. Disable the organization.
2. Then delete it.

In the CRM 2011 SQL Server

1. Delete the database from SQL Server

In the CRM 4.0 SQL Server

1. Back up the 4.0 organization database (the one with the _MSCRM after it), to a BAK file on disk.

In the CRM 2011 SQL Server

1. Create a new database, and restore the .BAK file to it, overwriting everything in the process.

In the CRM 2011 Deployment Manager

1. Run the Import Organization wizard
2. Import the SQL database you restored in the previous step
3. Import the Solution Package with your customizations you saved out to the XML file in the first step.

I know this is a pretty rough writeup. But I’ve run into this twice now, and the first time I made the mistake of not writing everything down! Hopefully this will never make it into Bridget’s List of Things to Know, but it’s definitely in Richard’s List of Things to Know about Upgrading to Dynamics CRM 2011.

Read more »

Posted in: CRM 4.0 - CRM 2011 Migration