The project I’m currently working on involves modifying an existing MSCRM organization, both in terms of structure (i.e. changing the entities, attributes, forms and views) and data (by setting sensible defaults for new attributes, converting existing data to new OptionSet values or applying configuration changes).

Scripting as the key to continuous delivery

I’m performing this migration not on the production version, but instead on an (anonymized) copy of the MSCRM organization on my development machine, so that I can develop the changes without interfering with the actual end-users. About half of these changes can be defined and deployed using a regular MSCRM solution file, so where possible, I use that.

For the other half, I’ve decided to implement all required modifications as Powershell scripts (including a script that imports and publishes the solution), so that at any point I can take a production-like copy and simply run all scripts to migrate the organization to the latest version.
These same scripts are also executed as part of the TFS Release deployments, so that it literally takes zero effort to deploy the latest version to our User Acceptance Test and/or production environments.

I’ve already blogged about how Powershell is an excellent choice when it comes to using the MSCRM webservice to import data from .csv files. Today, I want to show you how the same approach can be used to perform data migration and applying configuration changes, and how having a set of custom Cmdlets available can make your life considerably easier.
I can’t cover every Cmdlet that I’ve written, but I’ve tried to make a representative selection so you have both the essentials, and an idea of how to write your own. Also, I won’t cover adding these Cmdlets to a Powershell module (this post is long enough as it is), but I recommend that you do if you’re going to use them more often.

I’ll provide Cmdlets for:
– Connecting to the MSCRM Organization webservice
– Converting retrieved entities to PSObjects (which are easier to use in Powershell)
– Working with OptionSets
– Importing and publishing a solution .zip file
– Deleting an attribute from an entity (as a code fragment)

I’m using an on-premise installation of MSCRM 2016 in combination with Powershell 5.

Setting up the basics

In order to do anything useful, you’ll need the webservice proxy classes that MS CRM provides as part of its SDK, or you can get them from NuGet. Copy the Microsoft.Crm.Sdk.Proxy.dll and Microsoft.Xrm.Sdk.dll assemblies to your current working folder, and load them into your Powershell session, along with System.ServiceModel for the authorization classes:

[void][System.Reflection.Assembly]::LoadFile("$pwdmicrosoft.xrm.sdk.dll")
[void][System.Reflection.Assembly]::LoadFile("$pwdmicrosoft.crm.sdk.proxy.dll")
[void][System.Reflection.Assembly]::LoadWithPartialName("system.servicemodel")

For the Cmdlets, I find it convenient if I can rely on sensible default values so I don’t have to specify them with each call. Lets define a couple of global settings now:

$Global:CrmDefaultOrganizationName = "MyOrganization"
$Global:CrmDefaultOrganizationUrl = "http://localhost:5555/$Global:CrmDefaultOrganizationName/XRMServices/2011/Organization.svc"
$Global:CrmDefaultCredentials =  $null     # If set, should be of type PSCredential
$Global:CrmWebserviceProxy = $null

Getting the MSCRM webservice proxy

Ok, the most important thing is to define an easy way to get the MSCRM webservice proxy, since all our other Cmdlets will rely on it:

function Get-CrmWebServiceProxy {
  [CmdletBinding()]
  param(
    [parameter(Mandatory=$false, 
               HelpMessage = "The CRM webservice url, e.g. http://localhost:5555/MyOrganization/XRMServices/2011/Organization.svc. Optional, defaults to the globally configured url.")]
    [string]$OrganizationUrl = $Global:CrmDefaultOrganizationUrl,

    [parameter(Mandatory=$false, 
               HelpMessage = "The credentials to connect with. Optional, defaults to the currently logged on user.")]
    [PSCredential]$Credentials = $Global:CrmDefaultCredentials
  )
  process
  {
    # Create a WCF ClientCredentials to use for authentication. By default the currently 
    # logged in user is used, but if $Credentials are available, configure it to use these.
    $clientCrendentials = new-object System.ServiceModel.Description.ClientCredentials
    if($Credentials)
    {
      $clientCrendentials.Windows.ClientCredential = $Credentials.GetNetworkCredential()
    }
        
    # Create the proxy...
    Write-Verbose "Creating a Crm webservice proxy to ""$OrganizationUrl"" as ""$($Credentials.UserName)""..."
    $proxy = new-object Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy($OrganizationUrl, $null, $clientCrendentials, $null)
    $proxy.Timeout = new-object System.Timespan(0, 10, 0)

    # Make the proxy available as a global variable so that the other Cmdlets will use it as 
    # its default.
    $Global:CrmWebserviceProxy = $proxy

    return $proxy
  }
}

This is enough to do basically anything with MSCRM. For example, to query for data:

$proxy = Get-CrmWebServiceProxy
$query = New-Object Microsoft.Xrm.Sdk.Query.QueryExpression("contact");
$query.ColumnSet = New-Object Microsoft.Xrm.Sdk.Query.ColumnSet(@('contactid', 'fullname', 'emailaddress1', 'gendercode'));
$response = $proxy.RetrieveMultiple($query)

$response.Entities | Select-Object -First 3

# This produces a result like:
#
# LogicalName     : contact
# Id              : 2848d55e-f0ec-e511-9e20-00155d010155
# Attributes      : {[contactid, 2848d55e-f0ec-e511-9e20-00155d010155], [fullname, Petersen, Jan], [emailaddress1, jan.petersen@dummy.com]}
# EntityState     : 
# ...

Getting Entity objects in an easy-to-use format

However, as you can see each returned entity contains an Attribute dictionary with the values we’re actually interested in, but in the current format it’s a bit hard to read and use – it can’t be sent to Format-Table for example (in a meaningful way anway). When working with the results, I’d rather have a PSObject for each entity with all the entity’s attributes added to it as properties:

function Convert-EntityToPSObject
{
  [CmdletBinding()]
  param(
    [parameter(Mandatory=$true, Position = 1, ValueFromPipeline = $true, 
               HelpMessage = "The Entity to convert. Required")]
    [Microsoft.Xrm.Sdk.Entity]$Entity
  )
  process{
    # The entity's Attributes property implements IEnumerable, but not IDictionary; use Linq's ToDictionary() to convert it to
    # a Dictionary[string, object]
    [System.Func[System.Collections.Generic.KeyValuePair[string, object], string]]$keySelector = { param($attr) $attr.Key }
    [System.Func[System.Collections.Generic.KeyValuePair[string, object], object]]$valueSelector = { param($attr) $attr.Value }
    [System.Collections.Generic.Dictionary[string, object]] $dict = [System.Linq.Enumerable]::ToDictionary($Entity.Attributes, $keySelector, $valueSelector)

    # Now add the values that every entity should have
    $dict["Id"] = $Entity.Id
    $dict["LogicalName"] = $Entity.LogicalName
    $dict["EntityState"] = $Entity.EntityState

    # Create a PSObject based on this dictionary, with a property for each entry of the dictionary.
    $obj = New-Object PSObject -Property $dict
    return $obj
  }
}

Because of the ValueFromPipeline, we can pipe Entities through this Cmdlet so that we get a representation that is easier to work with and does work with Format-Table:

$contacts = $response.Entities | Convert-EntityToPSObject
$contacts | Select-Object -First 3 | Format-Table -AutoSize

# Produces:
# 
# contactid            fullname         emailaddress1           gendercode   ...
# ---------            --------         -------------           ----------   ...
# 25aa7d01-ede9-e5...  Petersen, Jan    jan.petersen@dummy.com  Microsoft.Xrm.Sdk.OptionSetValue
# 440071fa-29ec-e5...  Jansen, Helga    Helga.Jansen@dummy.com  Microsoft.Xrm.Sdk.OptionSetValue
# 46c9fd70-2bec-e5...  De Vries, Henk   Henk.DeVries@dummy.com  Microsoft.Xrm.Sdk.OptionSetValue

Working with OptionSets

As you can see, gendercode is of type Microsoft.Xrm.Sdk.OptionSetValue, which only contains the numerical identifier (1 or 2). We could hard-code some symbolic constants for these numerical values in Powershell, but it is better to retrieve the corresponding labels directly from MSCRM:

function Get-OptionSet()
{
  [CmdletBinding()]
  param(
    [parameter(Mandatory=$true, ParameterSetName="EntitySpecific", Position = 1, 
               HelpMessage = "The logical Entity name, e.g. ""contact"".")]
    [string]$EntityName,

    [parameter(Mandatory=$true, ParameterSetName="EntitySpecific", Position = 2, 
               HelpMessage = "The attribute name on the Entity, of type OptionSet.")]
    [string]$AttributeName,

    [parameter(Mandatory=$true, ParameterSetName="GlobalOptionSet", Position = 1, 
               HelpMessage = "The global OptionSet name.")]
    [string]$OptionSetName,

    [parameter(Mandatory=$false, 
               HelpMessage = "If specified, the numerical values are used as keys rather than the string values.")]
    [switch]$FromNumericalValueToString,

    [parameter(Mandatory=$false, 
               HelpMessage = "If specified, the numerical values are wrapped in Microsoft.Xrm.Sdk.OptionSetValues.")]
    [switch]$AsOptionSetValues,

    [parameter(Mandatory=$false, 
               HelpMessage = "The CRM webservice proxy to use. Optional.")]
    [Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy]$Proxy = $Global:CrmWebserviceProxy
  )

  # If no Proxy is available, create one now using the $Global:CrmDefaultOrganizationUrl and 
  # $Global:CrmDefaultCredentials.
  if($Proxy -eq $null)
  {
    $Proxy = Get-CrmWebServiceProxy
  }

  if($EntityName -and $AttributeName)
  {
    # Request the attribute metadata
    $metadataRequest = New-Object Microsoft.Xrm.Sdk.Messages.RetrieveAttributeRequest
    $metadataRequest.EntityLogicalName = $EntityName
    $metadataRequest.LogicalName = $AttributeName
    $metadataRequest.RetrieveAsIfPublished = $true
    $metadataResponse = $Proxy.Execute($metadataRequest)

    # If this was indeed a picklist, $metadataResponse.AttributeMetadata is of type 
    # PicklistAttributeMetadata which contains all available options. 
    $options = $metadataResponse.AttributeMetadata.OptionSet.Options
  } 
  else
  {
    # Request the OptionSet metadata
    $metadataRequest = New-Object Microsoft.Xrm.Sdk.Messages.RetrieveOptionSetRequest
    $metadataRequest.Name = $OptionSetName
    $metadataRequest.RetrieveAsIfPublished = $true
    $metadataResponse = $Proxy.Execute($metadataRequest)

    $options = $metadataResponse.OptionSetMetadata.Options
  }

  # Both ways produce a collection of Microsoft.Xrm.Sdk.Metadata.OptionMetadata, convert it to 
  # a dictionary.
  $dict = @{ };
  $options | ForEach-Object {
    $label = $_.Label.UserLocalizedLabel.Label
    $numericalValue = $_.Value

    # If -AsOptionSetValues is specified, wrap each numericalValue in an OptionSetValue so it 
    # can more easily be used as part of an Entity's Attribute data.
    if($AsOptionSetValues) {
        $numericalValue = New-Object Microsoft.Xrm.Sdk.OptionSetValue($numericalValue)
    }

    if($FromNumericalValueToString) {
        $dict.Add($numericalValue, $label)
    } else {
        $dict.Add($label, $numericalValue)
    }
  }

  return $dict
}

This Cmdlet works with both global and entity-specific OptionSets, and returns a regular Powershell hashtable/dictionary that maps each label to its numerical value. Via the extra switches it allows you to specify if you want the mapping to be reversed and/or if you want the OptionSetValue objects or just the numerical values.

Get-OptionSet -EntityName "contact" -AttributeName "gendercode"

# Produces:
#
# Name     Value
# ----     -----
# Female   2
# Male     1

Importing and publishing solutions

Another thing that will come in handy when scripting a migration is the ability to upload a MSCRM solution in the form of a .zip file, and to publish the results. This too can be accomplished using the MSCRM webservice:

function Import-Solution {
  [CmdletBinding()]
  param(
    [parameter(Mandatory=$true, Position = 1, HelpMessage = "The solution .zip file to import")]
    [string]$Path,

    [parameter(HelpMessage = "If specified, all customizations are published afterwards.")]
    [switch]$Publish,

    [parameter(Mandatory=$false, HelpMessage = "The CRM webservice proxy to use. Optional.")]
    [Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy]$Proxy = $Global:CrmWebServiceProxy
  )
  process{
    # If no Proxy is available, create one now using the $Global:CrmDefaultOrganizationUrl 
    # and $Global:CrmDefaultCredentials.
    if($Proxy -eq $null)
    {
      $Proxy = Get-CrmWebServiceProxy
    }

    if([System.IO.Path]::IsPathRooted($Path) -eq $false)
    {
      # Path is not absolute, make it relative to $pwd, otherwise we will end up looking in
      # C:WindowsSystem32.
      $Path = [System.IO.Path]::Combine($pwd, $Path)
    }

    Write-Output "Importing solution from ""$Path""..."
    $request = new-object Microsoft.Crm.Sdk.Messages.ImportSolutionRequest
    $request.ConvertToManaged = $false
    $request.PublishWorkflows = $true   # Activate all workflows and SdkMessageProcessingSteps
    $request.CustomizationFile = [System.IO.File]::ReadAllBytes($Path)
    $response = $Proxy.Execute($request);

    if($Publish -eq $true)
    {
      Write-Output "Publishing all customizations..."
      $request = new-object Microsoft.Crm.Sdk.Messages.PublishAllXmlRequest
      $response = $Proxy.Execute($request);
    }
  }
}

Tying it all together: migrating data

Using these tools we have created for ourselves, we can now perform data migrations. For example, suppose we’ve defined a MSCRM solution that contains a new global OptionSet called new_drinkpreferences, and defines a new attribute of this type called new_drinkpreference on the contact entity.

If we wanted to import this solution to MSCRM, publish it, and then initialize this field for all contacts in a ‘smart’ way we can now do so:

# Import the solution and publish it.
Import-Solution "MySolution.zip" -Publish

# Get all contacts
$proxy = Get-CrmWebServiceProxy
$query = New-Object Microsoft.Xrm.Sdk.Query.QueryExpression("contact");
$query.ColumnSet = New-Object Microsoft.Xrm.Sdk.Query.ColumnSet(@('contactid', 'fullname', 'gendercode', 'new_drinkpreference'));
$response = $proxy.RetrieveMultiple($query)

# Get the available options for gendercode and new_drinkpreferences
$genderCodeMap = Get-OptionSet -EntityName "contact" -AttributeName "gendercode" -AsOptionSetValues
$drinkPreferenceMap = Get-OptionSet -OptionSetName "new_drinkpreferences" -AsOptionSetValues

foreach($entity in $response.Entities)
{
  # Men will probably like beer, whereas women will prefer wine.
  if($entity.Attributes['gendercode'] -eq $genderCodeMap['Male']) {
    $drinkPreferenceOption = $drinkPreferenceMap['Beer']
  } else {
    $drinkPreferenceOption = $drinkPreferenceMap['Wine']
  }

  # Note the explicit cast, this is needed when assigning to a Dictionary of Objects, 
  # otherwise Powershell won't "unwrap" the $drinkPreferenceOption but add it as a PSObject
  #  - which is a type that the MSCRM webservice doesn't understand.
  $entity.Attributes['new_drinkpreference'] = [Microsoft.Xrm.Sdk.OptionSetValue]$drinkPreferenceOption

  # Perform the update
  $proxy.Update($entity)
}

Or, if we no longer want this new attribute, we can also delete it (which is something that can’t be done by importing a solution, since it only works additive):

$request = new-object Microsoft.Xrm.Sdk.Messages.DeleteAttributeRequest
$request.EntityLogicalName = "contact";
$request.LogicalName = "new_drinkpreference";
$response = $proxy.Execute($request);

And again, this is just a fraction of what’s possible with the MSCRM webservice – the sky’s the limit. 🙂