Contents
Introduction
In our previous article, we talked about how to read from Dynamics CRM using Dynamics CRM Source connector. In this post you will learn How to read Microsoft Dynamics 365 OData API data in SSIS using REST API Call (Drag and drop approach without any coding). We will use SSIS JSON/ REST API Source to extract data from Dynamics 365 API.
Why Dynamics 365 API call?
If user don’t want to provide user name and password and just provide the Client Id and Client Secret only, in this case we can’t make call using Dynamics CRM Source connector. Or if you want to make different Dynamics CRM APIs call using the same Access Token.
Now let’s look at step by step approach to call Dynamics 365 REST API.
Prerequisite
- First, you will need to have SSIS installed
- Secondly, make sure to have SSDT
- Thirdly, You need Register Dynamics CRM App With Azure and get the credential.
- Finally, do not forget to install ZappySys SSIS PowerPack
What is Microsoft Dynamics 365
Dynamics 365 is a product line of enterprise resource planning (ERP) and customer relationship management (CRM) applications announced by Microsoft in July 2016 and on general release November 1, 2016, as a part of the Microsoft Dynamics product line. Dynamics 365 is sold in two editions, the Business Edition for small and medium-sized enterprises (SMEs or SMBs), and the Enterprise Edition for medium to large organizations. The Business Edition includes the Financials application, based on the project code-named ‘Madeira’. The Enterprise Edition comprises Dynamics CRM applications (field service, sales, project service automation, and customer service) plus Microsoft PowerApps, together with Microsoft’s premier ERP product, known as Dynamics 365 for Operations.
First you need Register Dynamics CRM App With Azure Active Directory and get the credential
If you ever need to use Dynamics CRM OAuth authentication method to be able to connect to dynamics CRM using Web API, you are going to need this
Please refer any of the following links for the same:
- Dynamics 365 Online Authenticate with Client Credentials
- Register Dynamics CRM App With Azure
- Register a Dynamics 365 for Customer Engagement app with Azure Active Directory
Read Microsoft Dynamics 365 API data in SSIS using JSON Source
Let’s start with an example. We use SSIS JSON Source Task to make call to Microsoft Dynamics 365 API, we will load Dynamics 365 Leads data to SQL Server.
Please refer to this link for more information : Microsoft Dynamics 365 Web API Reference
Get all Accounts, Contacts and Leads using Dynamics 365 API
- First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
- Furthermore, Drag and Drop JSON Source(REST API and File) File Task from the SSIS Toolbox.
- Seems like, double click on that Component to Edit the Configurations. Select File path or web URL from the Access Mode drop-down.
Enter Web URL like this to Get all Accounts and Chose Data Format as OData. Please refer to this link of Dynamics 365 Rest API for more information.
1https://{{organization-uri}}/api/data/v9.1/Accounts/
1https://{{organization-uri}}/api/data/v9.1/Contacts/
1https://{{organization-uri}}/api/data/v9.1/Leads/ - Check Use Credentials Option and select a new ZS-OAuth Connection from the drop-down.
- Configure new OAuth Connection, generate the token and test the connection.
1https://login.microsoftonline.com/common/oauth2/authorize1https://login.microsoftonline.com/common/oauth2/token - Now select filter and click on Preview button to get the data.
- Finally, click OK to save JSON Source settings.
- That’s it we are ready to load Dynamics 365 API data to SQL Server.
Load Microsoft Dynamics 365 API data into SQL Server
- Inside Data Flow, Drag OLEDB Destination from SSIS Toolbox
- Connect our Source component to OLEDB Destination
- Double click OLEDB Destination to configure it
- Select Target Connection or click NEW to create new connection
- Select Target Table or click NEW to create new table based on source columns
- Click on Mappings Tab to Auto map columns by name. You can change mappings as you need
- Click OK to Save OLEDB Destination Settings
- That's it, You are now ready to run data flow. NOTE: If you wish to debug data flow and see records when you run, add data viewer by right click on blue arrow > Click Enable Data Viewer
- To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task
Conclusion
In this post you have seen how to Read Microsoft Dynamics 365 API data in SSIS. We used JSON Source Component to call Dynamics 365 REST API and load data into SQL server. You can download SSIS PowerPack here to try many other scenarios not discussed in this blog along with 70+ other components.
References
Finally, you can use the following links for more information about the use of Dynamics 365 REST API with our tools:
- JSON Source, you can also find Tutorial Video here.
- Help File: Documentation of SSIS Json Source
- Microsoft Dynamics CRM Web API Reference
- Dynamics 365 Web API EntityType Reference