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 8.0.2.1 for .Net framework 4.5.2
    • Microsoft.CrmSdk.Extensions version 7.1.0.1 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

CRMDynamics_SSIS_GAC_1

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

CRMDynamics_SSIS_GAC_2

Activate the Windows Identity Fundation feature onto your Windows :

  • Launch « Turn Windows features on or off »

CRMDynamics_SSIS_WIF_1

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>.crm4.dynamics.com; 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)
 {
 listentity.AddRange(results.Entities);
 }
 
 // Check for more records, if it returns true.
 if (results.MoreRecords)
 {
 // Increment the page number to retrieve the next page.
 pagequery.PageInfo.PageNumber++;
 // Set the paging cookie to the paging cookie returned from current results.
 pagequery.PageInfo.PagingCookie = results.PagingCookie;
 }
 else
 {
 // If no more records are in the result nodes, exit the loop.
 break;
 }

 }
 return listentity;
 }
}

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

Laisser un commentaire

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

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. 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 )

Photo Google+

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

Connexion à %s