How to read Microsoft Dynamics 365 OData API data in SSIS

Posted on by

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

  1. First, you will need to have SSIS installed
  2. Secondly, make sure to have SSDT
  3. Thirdly, You need Register Dynamics CRM App With Azure and get the credential.
  4. 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:

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

  1. First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.
    Drag and Drop SSIS Data Flow Task from SSIS Toolbox

    Drag and Drop SSIS Data Flow Task from SSIS Toolbox

  2. Furthermore, Drag and Drop JSON Source(REST API and File) File Task from the SSIS Toolbox.
    Drag and Drop JSON Source Component

    Drag and Drop JSON Source Component

  3. 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.
    For Contacts:
    For Leads:
    Here, organization-uri is your Microsoft Dynamics 365 site domain name (here we are using our zappy6.api.crm.dynamics.com Dynamics 365 URL).

    Set URL in JSON Source

    Set URL in JSON Source

  4. Check Use Credentials Option and select a new ZS-OAuth Connection from the drop-down.
    Check Use Credentials

    Check Use Credentials

  5. Configure new OAuth Connection, generate the token and test the connection.
    Dynamics CRM API - OAuth Connection

    Dynamics CRM API – OAuth Connection

    Extra Attribute for Token Request

    Extra Attribute for Token Request

    Replace zappy6 with instance id of the Dynamic CRM 365 in your environment.
  6. Now select filter and click on Preview button to get the data.
    Preview Data

    Preview Data

  7. Finally, click OK to save JSON Source settings.
  8. That’s it we are ready to load Dynamics 365 API data to SQL Server.

Load Microsoft Dynamics 365 API data into SQL Server

Now let's look at how to load data into target like SQL Server, Oracle or Flat File. In below example we will see loading data into SQL Server database but steps may remain same for other targets which can be accessed using OLEDB Drivers (e.g. Oracle).
  1. Inside Data Flow, Drag OLEDB Destination from SSIS Toolbox
  2. Connect our Source component to OLEDB Destination
  3. Double click OLEDB Destination to configure it
  4. Select Target Connection or click NEW to create new connection
    Configure SSIS OLEDB Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS

    Configure SSIS OLEDB Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS

  5. Select Target Table or click NEW to create new table based on source columns
  6. Click on Mappings Tab to Auto map columns by name. You can change mappings as you need
    Create NEW Table SSIS OLEDB Destination

    Create NEW Table SSIS OLEDB Destination

  7. Click OK to Save OLEDB Destination Settings
  8. 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
  9. 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:

Posted in SSIS JSON Source (File/REST) and tagged , , , , , , , , .