SSIS package for moving access team templates with KingswaySoft

by Lucas Alexander

Last year Ben Hosking said there was no way to move access teams between Dynamics CRM organizations, so I created a tool to do that. Last month Tanguy Touzard said I should turn my console application into an XrmToolBox plugin, so I did. Soon after that Daniel Cai said I should make something with the KingswaySoft SSIS Integration Toolkit, so I've now written a version of my access team template mover as an SSIS package.

The logic

KingswaySoft's Dynamics CRM SSIS adapter makes basic CRM retrieves and inserts easy, so I could have written a simple package to do a full copy of access team templates from a source to a target in about 10 minutes, but I wanted to include the same functionality as my XrmToolBox plugin to enable access teams on the relevant entities in the target organization if desired.

Unfortunately the KingswaySoft adapter doesn't make working with Dynamics CRM metadata easy, so I had to use an SSIS transformation script task to check whether access teams are enabled for a particular entity and enable them.

After the transformation script task checks whether access teams are enabled for the entities in the target organization and (optionally) enables them, a conditional split transformation passes teamtemplate records for entities that are enabled for access teams to a Dynamics CRM destination task to create or update the teamtemplate records in the target organization.

Here's what the data flow task looks like with all the components on the canvas:

Let's take a closer look at all of them.

The retrieve task

The data flow starts with a Dynamics CRM source task to retrieve teamtemplate records from the source organization. Although you could use an entity source type, I use FetchXML to offer greater flexibility. For example it would be possible to only sync access teams that have names starting with a particular string value.

The transformation script task

KingswaySoft's Dynamics CRM adapter supports the use of its connection managers in SSIS script tasks, but there are a few extra steps you need to take that are described in this blog post.

Because SSIS processes the data in a row-by-row fashion, I needed to use slightly different logic in the SSIS package to check for whether entities have access teams enabled and then enable them. In the PreExecute method, the package establishes source/target connections and then retrieves metadata for all the entities in the target system. Next it stores the objecttypecode values for each enabled entity in a list of strings. This actually took me a while to get right, because KingswaySoft returns an entity's logical name as the objecttypecode attribute instead of the actual numeric CRM objecttypecode value.

As the transformation script task loops through each row, it does the following:

  1. Check whether the entity is enabled for access teams in the target. If so, set the value of an "enabled" output column to true.
  2. If the entity isn't enabled for access teams in the target organization:
    1. If a package parameter to automatically enable access teams is set to true, update the entity in the target organization to enable access teams and set the "enabled" output column to true. Also add the entity's logical name to the list of enabled entities so the script won't unnecessarily update the target system if it encounters another teamtemplate record for the same entity later.
    2. Otherwise, set the "enabled" output column to false.

The conditional split

The conditional split transformation has two outputs - one for rows with the enabled column set to true and one for rows with the enabled column set to false. My version of the package doesn't actually do anything with the "disabled" output, but you could do something with those rows if you wanted.

The upsert

The package creates or updates teamtemplate records in the target organization with a Dynamics CRM destination upsert task.

All the columns are mapped except for the createdby and modifiedby attributes.

Package parameters

As I mentioned earlier, the package supports optionally enabling entities for access teams in the target system. That's done via a package parameter called "enableTargetAccessTeams." The package also has additional parameters for setting the source and target connection strings and passwords.

Wrapping up

You can get the package from my GitHub repository here. I hope to add more packages to this solution for synchronizing other reference data records like business units, teams and queues in the near future. Stay tuned!