Migrating Personal Views and Dashboards with Kingswaysoft SSIS Tools

Migrating personal views and dashboards can be difficult. The primary challenge is that personal views and dashboards can only be queried by their owner. Fortunately, KingswaySoft SSIS tools make this possible with zero code. However, the setup for this is rather unintuitive. The goal of this post is to solve that problem.

Before getting started, I want to thank Sheila Shahpari and Daniel Cai for their work on this functionality.

Setup

The steps below explain how to setup the control and data flow to migrate personal views and dashboards. When the control flow is finished, it will look like this:

1. Setup variables.

First, we need to setup variables to be used by the Foreach Loop Container. To do this, open the variables window using the “SSIS” menu in the Visual Studio main menu bar. Then, create the variables shown below. Note that the scope will be set automatically and does not need to be changed.

2. Create a data flow task to read the users and save them to a recordset.

Add a data flow task for reading the users. This data flow task uses a Dynamics CRM Source component to retrieve the users and then assigns the result to a recordset using a RecordSet Destination. This recordset will be iterated over by the Foreach Loop Container created in the next step. The FetchXml used in the Dynamics CRM source component is included below.

When configuring the recordset destination, select all available input columns in the “Input Columns” tab. Then, open the “Input and Output Properties” tab and make a note of the order of the input columns. You will need this in step 3.

Final Data Flow for step:

FetchXml for Reading users:

 

3. Create a Foreach Loop Container to iterate over the recordset used in step 2.

Add a Foreach Loop Container to the control flow. Configure this task to run after the data flow task added in step 2.

To configure the Foreach Loop, open the settings by double-clicking on the task. Then, in the Collection tab, set the Enumerator to “Foreach ADO Enumerator”, and set the “ADO object source variable” to “User:UsersRecordSet” as shown below. Next, set up the Variable Mappings tab as shown below. Note that the indexes may need to be changed to match the input column order from the RecordSet destination used in step 2. If you don’t know what this is referring to, you may want to re-read step 2.

Collection Tab Setup:

Variable Mappings:

4. Add a data flow task to the ForEach Loop Container to migrate personal views.

Inside of the Foreach container, add a data flow task to migrate personal views. This data flow task uses a Dynamics CRM Source component to read the views, then uses a Dynamics CRM Destination component to create the views. The FetchXml used by the Source Component is below.

Final Data Flow for step:

Query for reading views:

5. Add a data flow task to the ForEach Loop Container to migrate personal dashboards.

Inside of the Foreach Loop container, add a data flow task to migrate personal dashboards. This data flow task uses a Dynamics CRM Source component to read the dashboards, then uses a Dynamics CRM Destination component to create the dashboards. In this step, the source component retrieves all records and properties from the “userform” entity.

Final Data Flow for step:

6. Setup user impersonation

From the control flow, right click on the “Migrate Personal Views” data flow task and click on “Properties”. Then, open the “Expressions” property. Add an expression with the property “[Read Views].[ImpersonateAs]”, and the value set to the expression below. “Read Views” is the name of the Dynamics CRM Source Component used within the data flow task.

Properties:

ImpersonateAs Expression:

Repeat the process above for the “Migrate Personal Dashboards” component. This time the property name used when setting the expression will be “[Read Dashboards from Source].[ImpersonateAs]”.

Completed Control Flow

The completed control flow should look like this.

Conclusion

This setup does not account for differences in systemuser ids between the source and destination systems. But, it should provide a good starting point. These can be accounted for using a merge join or lookup on the systemuser’s fullname or some other joining property.

Since personal views and dashboards can only be read by their owners, we used a Foreach Loop Container to iterate over the users and impersonate each one while retrieving personal views and dashboards. The impersonation was achieved by using the “ImpersonateAs” property on the Dynamics CRM Source component.

I hope this was helpful! Please leave any feedback or issues in the comment section below.

Author: Michael Sollenberger

I am a Dynamics 365 Consultant with over 4 years of experience. I first became interested in programming at age 10 when I started learning BASIC from the book “Beginning Programming for Dummies.” Currently, I am working as a technical consultant for Cloud Nine Solutions. My responsibilities include estimating, designing, and building solutions with Dynamics 365 for Sales and Customer Service. My most recent project was a CRM implementation within an enterprise environment where I was responsible for managing the technical aspects of the project. Previously, I was a developer for Distributed Network Software where I first started with Dynamics CRM 2011 and worked in Dynamics GP.

One thought on “Migrating Personal Views and Dashboards with Kingswaysoft SSIS Tools”

  1. Very helpful article.
    Other difference is that ObjectTypeCodes for custom entities might also be different in source and destination environments. There is also a need to think about the views of disabled users.

Leave a Reply

Your email address will not be published. Required fields are marked *