Migrate Quote From One Dynamics CRM System to Another

CRM quote entity plays a very important role in the CRM sales life cycle. However, when it comes to data migration, CRM quote entity has a number of special behaviors which makes it difficult to get the migration right. We have received many emails from our clients asking about CRM quote migration, so we have decided to write this blog to share our solutions.

This blog post demonstrates how to migrate CRM quote data with the following perimeters:

1. For the quote that has been revised, we want to make sure that the most recent revision is migrated.
2. Corresponding quote product records are migrated.
3. Migrate the respective quote close activity record if and only if the most recent revision of the Quote is in Close state.

1 Migrate Quote

Before the migration, let’s first have a quick look of the life cycle of a quote record.

A quote is in draft state by default after being created. A quote in draft state is editable, and can also be activated, after which it is no longer editable until it is revised. When the revise action takes place, the state of the quote is changing back to draft and a revised quote record is generated with an incremented revision number.

The quote can only be converted to order (Won) or Closed when it is in Active state. A quote in Won/Closed state can be revised as well. A quote record can be revised multiple times, the revision number would be incremented by one for each revision. The quotenumber field is the link of the revisions which would remain the same among all revisions.

When it comes to quote migration in terms of the revised quote records, you may find the following challenges:

• The revision number cannot be set directly.
• CRM does not allow creating multiple quote records with the same quote number.

Considering the behavior of quote life-cycle and the difficulties of quote migration with revised quote, we choose to use quotenumber as matching criteria when migrate the quote data.

Therefore, the quote migration would be separated into two parts: the first part we get the primary key of the most recent revision records, then we update the records in the next step.

1.1  Migrate the most recent revision record

To migrate the most recent revised record to the destination CRM system, we sort the source data by quote number and revision number (descending). In the destination component, we use Upsert action with quotenumber as matching criteria and set the Batch Size to 1, so the component will create the quote record with the primary key of the most recent revision, then the update action would take over for the rest of the revisions.

Please note that we only need to map the necessary fields and non-updatable fields in this step.

You would be using a FetchXML query such as following to read the quote data from source CRM system:

<fetch mapping='logical'>
    <entity name='quote'>
        <attribute name="createdby" />
        <attribute name="createdon" />
        <attribute name="customerid" />
        <attribute name="importsequencenumber" />
        <attribute name="name" />
        <attribute name="pricelevelid" />
        <attribute name="quotenumber" />
        <order attribute="quotenumber" />
        <order attribute="revisionnumber" descending="true" />
    </entity>
</fetch>

It is then a simple entity field-to-field mapping to the destination component.

After this step, we will have the quote records with the highest revisionnumber in the target system. Other than the revisionnumber field, all other fields of those records are actually stored using the oldest values from the quote record that has the lowest revisionnumber of the same quotenumber; which is the reason that we need another step (next) to update the value in a reversed sequence. This is so that we can get the latest value in the target system. 

1.2  Update the Quote records

Generally we use a similar approach as the previous step, the difference is that in this step the revision number is sorted ascending. And still we use quotenumber as matching criteria, as the most recent quote records have been migrated so that the component would keep updating the migrated records until the most recent revision.

The FetchXML query would be like this in this step to retrieve quote data from the source:

<fetch mapping='logical'>
    <entity name='quote'>
      <all-attributes />
      <order attribute="quotenumber" />
      <order attribute="revisionnumber"/>  
    </entity>
</fetch>

We use a similar destination component to perform update to the quote records using the quotenumber field as the matching criteria.

After this step, we have the latest revisionnumber along with the latest values of all other fields in the target system. We are now ready to process quote details.

2 Migrate Quote Product records

CRM quote product records are stored in quotedetail entity.

Since we already have the most recent quote record migrated in the new CRM system, it would be pretty easy to migrate the line items in the quote. What we need to do is look up the migrated quote records by the primary key (quoteid) in the source CRM system, which can be accomplished by using the SSIS Merge Join component.

The data flow of migrate quote product would be:

3 Update the Quote Status

The last step is to update the status of the records been migrated. Please note that you need to follow the lifecycle of quote status, the quote must be in Active state in order to be set as Closed/Won.

CRM will generate a quote close activity record internally when a quote is closed. When you close a quote record by code, you may notice that the quote close record created by CRM has empty values for subject (Subject), actualend (Closed Date) and description (Description) fields. The common approach is to delete the records that were created automatically by CRM and then recreate them, which is a very complicate and expensive method due to the fact that Delete action in CRM is expensive.

I will introduce a new feature added to our CRM destination component since (v7.2), we added a few virtual quote close fields to quote entity so that when you close a quote along with supplying these virtual fields, our component would send these values to the corresponding fields when closing the quote records. This is done with only one service call.

As you can see, the additional virtual fields has made it extremely easy to close quote records with all the necessary information retained.

In terms of updating the quote status, we only need to retrieve quote records in Active/Won/Closed state from the source CRM system, you would use a FetchXML query such as the following to combine the data in quote and quotedetail entity:

The data flow for updating quote status would be:

As you can see, there are two destination components in the data flow. We use the first one to update the quote state to Active, before we can finally use the second destination component to set to its final state. Note that we use "Ignore Unchanged Fields" option in the second destination component so that we don't actually update the quote if the state is Active (not Won or Closed).

Note that in this step, you may get a "The entity cannot be closed because it is not in the correct state." error if the quote has been closed several times in the source system (only possible if it is done through SDK based tools). If this ever happens, you can add an SSIS sort component before the Derived Column component in this data flow task, and sort the input by quoteclose.createdon field in a descending order. If you do so, you would want to change the destination component's error handling to "Redirect rows to error output" and redirect errors to a log file or something equivalent for analysis purpose (if you ever need to do so). You can technically ignore the above mentioned "The entity cannot be closed because it is not in the correct state." error.

Sample SSIS Package

To help make it easy to understand, we have uploaded a sample SSIS package, which you can try out by downloading from the link below:

• CrmQuoteMigration.zip

The sample package was prepared in a SQL Server 2008 R2 format, and it should automatically upgrade if you are using any newer version of SSIS.

Final Notes

Here are some final notes before we conclude this blog post.

• For each quotenumber, we only migrate the last revision, all the old revisions are not migrated due to some of the difficulties associated as we mentioned in the post.
• The design of the entire SSIS package does use multi-pass to get the last revision into the target system. We could have made the package much simpler if we chose to use a local SQL storage so that we can perform native SQL JOINs to get the highest revisionnumber along with the latest version, but we try to stay away from any additional dependencies. There might be other alternatives that can make this package more efficient, we would like your feedback if you have any suggestion making the package better.
• You need to use a version of v7.2 or later for our SSIS Integration Toolkit for Microsoft Dynamics CRM software in order to run the provided sample migration package.

Have a question?

Should you have any questions about the practice discussed in this blog post, please feel free to contact us, we are more than happy to assist you.