Online Data Provider: Microsoft Dynamics 365 Sales CRM

[Version: 2022 - Winter]
[Build: 22.11.29002] 


  • Access to your organization's Microsoft Azure Portal
  • Access to your organization's Microsoft Power Platform
  • A running Microsoft Dynamics 365 Sales. If you don't have one, you can create a trial instance from here:
  • A TARGIT license for the 'Dynamics 365 CRM' Online Data Provider:



Register a Dynamics CRM app on Azure portal

Log in to you Azure portal at

Go to the Azure Directory to create a new App registration:



Choose a name for your app and register it as single tenant or multitenant:



Copy the Application ID and the Tenant ID to e.g. Notepad. Finalize this dialog by adding a secret:



Copy the secret value (not the ID) to Notepad:



Go to API permissions and select Dynamics CRM...

mceclip8.png add the user_impersonation permission:



Configure the application user in Power Platform

Log in to Power Platform admin center:

Go to Environments and select your CRM environment. Once selected, copy the Environment URL to Notepad and then go to Settings:



In Settings, go to Application users:



Search for the application you created in Azure:



Select your (CRM) Business unit and click the pencil to set up Security roles:



Locate the System Administrator role and save the application user:



Load data with TARGIT InMemory ETL - Designer UI

Create or open a project in TARGIT InMemory ETL Studio and add a new data source of the type Microsoft Dynamics 365 Sales - Designer:



From your Notepad, fill in the corresponding fields:

  • Address. Your Environment URL (including https)
  • Tenant ID
  • Client Id is another word for Application ID
  • Client Secret

Once you connect, you will be able to select tables, columns and apply filters to this data source:



With this new data source in the ETL project, you can now create an Import task (and a Save task) to load data from your CRM:


Example SQL for ETL Studio:

SELECT * FROM [] (OPTIONS: depth=3)


In the TARGIT InMemory Query Tool, running the above project might result in a table like this:



Load data with OData query

This type of the data provider will allow you to extract data using the OData URI. SQL queries is not allowed here.

OData URI conventions:


https://[Business unit name]$select=numberofemployees,merged,opendeals&$filter=numberofemployees gt 50
https://[Business unit name]$select=numberofemployees,merged,opendeals
https://[Business unit name]


Example SQL for ETL Studio:

SELECT * FROM [] (OPTIONS: depth=3)


Load data with SQL

Allows to extract data using SQL queries.


SELECT * FROM sys.tables
SELECT * FROM sys.columns WHERE TableName = [accounts]
SELECT address1_composite, donotpostalmail, accountratingcode, numberofemployees, marketingonly, revenue_base FROM accounts
SELECT [numberofemployees], [merged], [opendeals] FROM [accounts] WHERE numberofemployees <> 50


Was this article helpful?
0 out of 0 found this helpful



Please sign in to leave a comment.