CRM Data Management With PowerShell

Article
06/28/2014

As you know, it is not supported to manipulate CRM data with SQL, you need to use CRM WebServices with CRM SDK.

You can write console applications that handle data operation but it is not very flexible for quick operations.

PowerShell is a great solution for multiple reasons :

You can edit and run script on windows server
No compilation
Well documented
Can execute .NET code

OK, that was the boring theory introduction, let's go with code examples ! :)

Load SDK assemblies

In order to have access to CRM classes, you need to load CRM SDK assemblies to Powershell script :

 function Add-Crm-Sdk
{
    # Load SDK assemblies
    Add-Type -Path "$PSScriptRoot\Assemblies\Microsoft.Xrm.Sdk.dll";
    Add-Type -Path "$PSScriptRoot\Assemblies\Microsoft.Xrm.Client.dll";
    Add-Type -Path "$PSScriptRoot\Assemblies\Microsoft.Crm.Sdk.Proxy.dll";
}

Build CRM connection and instantiate OrganizationService

Thanks to CrmConnection.Parse method we can easily connect to every deployment type for organization :

 function Get-Crm-Connection
{
    # Configure CRM connection
    $url = "https://organization.domain.com";
    $login = "user@domain.com";
    $pwd = "Pass@word1";
    
    $crmConnection = [Microsoft.Xrm.Client.CrmConnection]::Parse("Url=$url; Username=$login; Password=$pwd");
    return $crmConnection;
}

And then you can instantiate OrganizationService with CrmConnection object :

 $crmConnection = Get-Crm-Connection;

# Instantiate Organization Service
$service = New-Object -TypeName Microsoft.Xrm.Client.Services.OrganizationService -ArgumentList $crmConnection;

Retrieve data from CRM

Here’s an example of RetrieveMultiple with paging :

 function Get-Multiple-Records
{
    PARAM
    (
        [parameter(Mandatory=$true)]$service,
        [parameter(Mandatory=$true)]$query
    )

    $pageNumber = 1;

    $query.PageInfo = New-Object -TypeName Microsoft.Xrm.Sdk.Query.PagingInfo;
    $query.PageInfo.PageNumber = $pageNumber;
    $query.PageInfo.Count = 1000;
    $query.PageInfo.PagingCookie = $null;

    $records = $null;
    while($true)
    {
        $results = $service.RetrieveMultiple($query);
                
        Write-Progress -Activity "Retrieve data from CRM" -Status "Processing record page : $pageNumber" -PercentComplete -1;
        if($results.Entities.Count -gt 0)
        {
            if($records -eq $null)
            {
                $records = $results.Entities;
            }
            else
            {
                $records.AddRange($results.Entities);
            }
        }
        if($results.MoreRecords)
        {
            $pageNumber++;
            $query.PageInfo.PageNumber = $pageNumber;
            $query.PageInfo.PagingCookie = $results.PagingCookie;
        }
        else
        {
            break;
        }
    }
    return $records;
}

This method can be called like this :

 function Get-Accounts
{
    $query = New-Object -TypeName Microsoft.Xrm.Sdk.Query.QueryExpression -ArgumentList "account";
    $query.ColumnSet.AddColumn("name");
    $query.Criteria.AddCondition("accountnumber", [Microsoft.Xrm.Sdk.Query.ConditionOperator]::NotNull);
    $query.AddOrder("name", [Microsoft.Xrm.Sdk.Query.OrderType]::Ascending);

    $records = Get-Multiple-Records $service $query;
    return $records;
}

In this example, we will retrieve all Accounts that have an accountnumber defined and order by name.

Here’s an example of record search :

 function Get-Account
{
     PARAM
    (
        [parameter(Mandatory=$true)]$accountNumber
    )

    $query = New-Object -TypeName Microsoft.Xrm.Sdk.Query.QueryExpression -ArgumentList "account";
    $query.Criteria.AddCondition("accountnumber", [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Equal, $accountNumber);
    $query.ColumnSet.AddColumn("fullname");
    $results = $service.RetrieveMultiple($query);
    $records = $results.Entities;

    if($records.Count -eq 1)
    {
        return $records[0];
    }
    Write-Host -ForegroundColor Red "Record not found : $id"
    return $null;
}

This will return account record with requested accountnumber given in method parameter.

Process results

When you retrieve a collection of record from Get-Multiple-Records method, you can process results with a simple foreach instruction.
In order to display process progression, you can use Write-Progress snippet.

 
$accounts = Get-accounts;
$current = 0;
$total = $accounts.Count;

foreach($account in $accounts)
{
    $accountName = $account.Attributes["name"];
    $current++;
    $percent = ($current/$total)*100;
    
    Write-Progress -Activity "Account data management" -Status "[$current/$total] Processing account '$accountName' ..." -PercentComplete $percent;

     Write-Host "Account : $accountName" -ForegroundColor Yellow;       
}

Create record

The only difference with C# is Powershell syntax for object initialization :

# Instanciate new account Entity object
$account = New-Object -TypeName Microsoft.Xrm.Sdk.Entity -ArgumentList "account";
$account["name"] = "PowerShell is great!";

# Create an account and retrieve new ID
$id = $service.Create($account);

Update record

 
# Instanciate new account Entity object
$accountToUpdate = New-Object -TypeName Microsoft.Xrm.Sdk.Entity -ArgumentList "account";
$accountToUpdate.Id = $id;
$accountToUpdate["name"] = "CRM and PowerShell are greats!"

# Update account
$service.Update($accountToUpdate);

Assign record

The reusable method :

 function Assign-Record
{
    PARAM
    (
        [parameter(Mandatory=$true)]$recordReference,
        [parameter(Mandatory=$true)]$ownerReference
    )

    $assignRequest = New-Object -TypeName Microsoft.Crm.Sdk.Messages.AssignRequest;
    $assignRequest.Assignee = $ownerReference;
    $assignRequest.Target = $recordReference;

    $result = $service.Execute($assignRequest);
}

And the call with our account example :

 Assign-Record $account.ToEntityReference() $me.ToEntityReference();

Delete record

 # Delete account
$service.Delete("account", $id);

OptionSet type

I have serialization problem on record create / update when giving an OptionSetValue to record attribute with the following error :

«The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter
https://schemas.microsoft.com/xrm/2011/Contracts/Services:entity. The InnerException message was 'Error in line 1 position 11940. Element
'https://schemas.datacontract.org/2004/07/System.Collections.Generic:value' contains data from a type that maps to the name 'System.Management.Automation:PSObject'. The deserializer has no
knowledge of any type that maps to this name. Consider changing the implementation of the ResolveName method on your DataContractResolver to return a non-null value for name 'PSObject' and
namespace 'System.Management.Automation'.'. Please see InnerException for more details.»

This error is link to Powershell conversion of object that is not understand by CRM WebService.

You need to explicitely cast the OptionSetValue, like this :

 $optionSetValue = New-Object -TypeName  "Microsoft.Xrm.Sdk.OptionSetValue" -ArgumentList 1;
$account["new_samplecode"]= [Microsoft.Xrm.Sdk.OptionSetValue] $optionSetValue;

Don’t hesitate to share your script snippets !

Enjoy!

CRM Data Management With PowerShell

Additional resources