Working with CRM Dynamics 2016 and SSIS – Step 1

Last year, when Microsoft had announced SQL Server 2016 they talked about Power Query integration in SSIS. From my point of view this was a great announcement for many reasons :

  • Agile BI enhancements : when IT have to re industrialize personal BI solutions created by end users
  •  IT enhancements : adding multiple native to connection to many sources that you have to script in SSIS

And that would have been a great scenario, when you have to put BI over a CRM (Dynamics for example). Unfortunately, this announcement has been deprecated before he born…. So how could you import CRM Dynamics data into your data warehouse with SSIS ?

The simplest way is working with script component, but if you have to import many objects, the best way is to create a Ssis toolkit library for CRM Dynamics to reuse and to manage your code easily. So let’s start with our dll….

First of all, just simply create a new « Class Library » project with VS 2015, let’s call it CrmToolkit and add it a class called CrmToolkit4Ssis.

To interact with CRM Dynamics you have to

  • add some NuGet Packages :
    • Microsoft.CrmSdk.CoreAssemblies version for .Net framework 4.5.2
    • Microsoft.CrmSdk.Extensions version for .Net framework 4.5.2
    • Microsoft.CrmSdk.Deployment version 7.1.0 for .Net framework 4.5.2
    • Microsoft.IdentityModel version 6.1.7600.16394 for .Net framework 4.5.2
  • registrer in your global ssembly cache (GAC) some dlls. To do that, run as administrator the app called Developer Command Prompt for VS2015


and use the command gacutil /I to register the those you need (Microsoft.Xrm.Sdk.dll, Microsoft.Xrm.Client.dll, Microsoft.Crm.Sdk.Proxy)


Activate the Windows Identity Fundation feature onto your Windows :

  • Launch « Turn Windows features on or off »


Ok, now let’s code something to retrieve a CRM Entity by getting back to our custom class library, my class prototype is very basic and contains :

  • A CrmConnection property to connect to my Dynamics instance
  • A simple constructor with a string argument to initialize the connection
  • A function to fully retrieve an entity by her name
public class CrmToolkit4Ssis
 CrmConnection Connection { get; set; }
 Int EntityPagerFetchSize { get; set; }
 public CrmToolkit4Ssis(string connectionString){}
 public List<Entity> FullRetrieveByEntityName(string entityName){}

To connect to your CRM Dynamics instance, your connection string just be like that :

  • Url = https://<your tenant name>; Username=<username>; Password=<password>; »

The class constructor is trivial :

public CrmToolkit4Ssis(string connectionString)
 this.Connection = CrmConnection.Parse(connectionString);
 EntityPagerFetchSize = 4000;

To fully retrieve an entity, it’s not so complicated :

  • Create CommunicationService service object to connect to your CRM instance.
  • Build a QueryExpression which define the entity name to retrieve and the pager size
  • Execute the QueryExpression by using the function RetrieveMultiple
  • And loop over the resultset till it has unread records

Notice that you can build more complicated queries by defining the fields you want to retrieve and/or adding predicates filters (for incremental loads or other needs…)

public List<Entity> FullRetrieveByEntityName(string entityName)
 using (OrganizationService service = new OrganizationService(Connection))
 //retrieve the list entities
 List<Entity> listentity = new List<Entity>();

 // Define the fetch attributes by setting the number of records per page to retrieve.
 int fetchCount = this.EntityPagerFetchSize;

 // Initialize the page number.
 int pageNumber = 1;

 // Assign the pageinfo properties to the query expression.
 QueryExpression pagequery = new QueryExpression();
 pagequery.PageInfo = new PagingInfo();
 pagequery.PageInfo.Count = fetchCount;
 pagequery.PageInfo.PageNumber = pageNumber;
 pagequery.TopCount = null;
 pagequery.EntityName = entityName;

 // The current paging cookie. When retrieving the first page, 
 // pagingCookie should be null.
 pagequery.PageInfo.PagingCookie = null;
 while (true)
 // Retrieve the page.
 EntityCollection results = service.RetrieveMultiple((pagequery);
 if (results.Entities != null)
 // Check for more records, if it returns true.
 if (results.MoreRecords)
 // Increment the page number to retrieve the next page.
 // Set the paging cookie to the paging cookie returned from current results.
 pagequery.PageInfo.PagingCookie = results.PagingCookie;
 // If no more records are in the result nodes, exit the loop.

 return listentity;

Ok, just build the dll and you’re good to implement SSIS packages (in a future post)…

Votre commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:


Vous commentez à l’aide de votre compte Déconnexion /  Changer )

Photo Google

Vous commentez à l’aide de votre compte Google. Déconnexion /  Changer )

Image Twitter

Vous commentez à l’aide de votre compte Twitter. Déconnexion /  Changer )

Photo Facebook

Vous commentez à l’aide de votre compte Facebook. Déconnexion /  Changer )

Connexion à %s