CRM Data Management With PowerShell


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 :

  1. You can edit and run script on windows server
  2. No compilation
  3. Well documented
  4. 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 = "http://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
http://schemas.microsoft.com/xrm/2011/Contracts/Services:entity. The InnerException message was ‘Error in line 1 position 11940. Element
http://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 (14)

  1. Anonymous says:

    Hi Aymeric,

    Your post is very interesting, but i don't find how do you instantiate OrganizationService.

    Thanks

  2. Hi Philippe,

    You're right, I forgot this important part.

    Thanks!

  3. Anonymous says:

    Aymeric,

    Thanks for sharing with the CRM in the Field communities!

  4. Anonymous says:

    Once we create and update the records how to give the security changes using powershell .

  5. Hello Prakash,

    What did you mean by giving the security changes ?

    Thanks

  6. Anonymous says:

    Very useful.  Thank you.  

  7. Anonymous says:

    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?

  8. Hi Andrea,

    I have never seen this type of problematic.

    Which version of PowerShell did you use ?

    You should install the latest version : http://www.microsoft.com/…/details.aspx

    HTH

  9. Anonymous says:

    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.

  10. Anonymous says:

    Hi Aymeric , Thank you very much for your post. Can you please explain how to read OPTION SET,

  11. Hello,

    It depend, if you want to read record optionset value or optionset metadata information.

    If you want to read optionset value from record, you can do this like that :

    $optionsetValue = $account["optionsetfield].Value;

    If you want to retrieve global optionset information from metadata :

    $request = New-Object "Microsoft.Xrm.Sdk.Messages.RetrieveAllOptionSetsRequest";

    $response = $service.Execute($request);

  12. Anonymous says:

    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

  13. Bonjour Chris,

    Est-ce que tu parviens Ă  faire le traitement en C# ?

    Peux-tu envoyer un extrait de ton script ?

    Merci

  14. Anonymous says:

    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