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!

Comments

Anonymous
June 28, 2014
Hi Aymeric,Your post is very interesting, but i don't find how do you instantiate OrganizationService.Thanks
Anonymous
June 28, 2014
Hi Philippe,You're right, I forgot this important part.Thanks!
Anonymous
July 07, 2014
Aymeric,Thanks for sharing with the CRM in the Field communities!
Anonymous
August 01, 2014
Once we create and update the records how to give the security changes using powershell .
Anonymous
August 02, 2014
Hello Prakash,What did you mean by giving the security changes ?Thanks
Anonymous
October 02, 2014
Very useful. Thank you.
Anonymous
April 16, 2015
I get an error everytime and I can't find a solution for it.I'll get this error:Cannot find an overload for "RetrieveMultiple" and the argument count:"1".At C:tempLab Account.ps1:48 char:9 $results = $service.RetrieveMultiple($query); ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodException + FullyQualifiedErrorId : MethodCountCouldNotFindBest Is there anyone who know about this?
Anonymous
April 16, 2015
Hi Andrea,I have never seen this type of problematic.Which version of PowerShell did you use ?You should install the latest version : www.microsoft.com/.../details.aspxHTH
Anonymous
April 18, 2015
Hi, I found out what the problem was, I tried to run the code as a non administrator... when changed to administrator everything went smooth.
Anonymous
April 23, 2015
Hi Aymeric , Thank you very much for your post. Can you please explain how to read OPTION SET,
Anonymous
April 30, 2015
The comment has been removed
Anonymous
July 19, 2015
Bonjour, Suiper travail, mais j'ai un problème pour créer des produits, je n'arrive pas à alimenter correctement le lookup de l'unite, j'ai le problème de format mais je ne vois pas comment le résoudre. Si vous avez une piste je suis preneur. Merci Chris
Anonymous
July 21, 2015
Bonjour Chris, Est-ce que tu parviens à faire le traitement en C# ? Peux-tu envoyer un extrait de ton script ? Merci
Anonymous
July 21, 2015
Bonjour Aymeric Je ne suis pas très doué en programmation donc je n'ai pas développé de module C#. L’idée du powershell me permettait de m’affranchir de ce problème. Voici mon script pour la création des produist dans la CRM Function CreateProduits { PARAM ( [parameter(Mandatory=$true)]$code, [parameter(Mandatory=$true)]$lib, [parameter(Mandatory=$true)]$unite, [parameter(Mandatory=$true)]$quantite )

Instanciate new subject Entity object

$entite = New-Object -TypeName Microsoft.Xrm.Sdk.Entity -ArgumentList "product"; $ref = New-Object -TypeName Microsoft.Xrm.Sdk.EntityReference -ArgumentList ("uomschedulebase",$unite) $entite["productnumber"] = "$code" $entite["name"] = "$lib" $entite["quantitydecimal"] = 4 $entite["defaultuomscheduleid"] = New-Object -TypeName Microsoft.Xrm.Sdk.EntityReference -ArgumentList ("uomschedulebase",$unite) $entite["defaultuomid"] = New-Object -TypeName Microsoft.Xrm.Sdk.EntityReference -ArgumentList ("uombase",$quantite)

Create an account and retrieve new ID

$id = $service.Create($entite); } Exception lors de l'appel de «Create» avec «1» argument(s): «Le module de formatage a généré une exception en tentant de désérialiser le message : Une erreur s'est produite en tentant de désérialiser le paramètre schemas.microsoft.com/.../Services:entity. Le message InnerException était 'Erreur à la ligne 1 position 1181. L'élément 'schemas.datacontract.org/.../System.Collections.Generic:value& contient des données dont le type est mappé au nom 'System.Management.Automation:PSObject'. Le désérialiseur n'a connaissance d'aucun type mappé à ce nom. Changez l'implémentation de la méthode ResolveName dans DataContractResolver afin de retourner une valeur non Null pour le nom 'PSObject' et l'espace de noms 'System.Management.Automation'.'. Pour plus d'informations, consultez InnerException.» Au caractère C:powercrm.ps1:112 : 2

$id = $service.Create($entite);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : FaultException J’ai vu ce lien pour résoudre le problème mais vu mes compétences en programmation je ne comprends pas comment faire pour résoudre mon problème. colivier.wordpress.com/.../serialization-issue-when-using-crm-2011-and-wcf-generated-proxies Merci de ton aide. Chris

Share via

CRM Data Management With PowerShell

Comments

Instanciate new subject Entity object

Create an account and retrieve new ID

Additional resources