{"id":7004,"date":"2019-05-25T10:28:13","date_gmt":"2019-05-25T10:28:13","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7004"},"modified":"2023-08-27T09:12:46","modified_gmt":"2023-08-27T09:12:46","slug":"read-microsoft-dynamics-365-odata-api-data-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-microsoft-dynamics-365-odata-api-data-ssis\/","title":{"rendered":"How to read Microsoft Dynamics 365 OData API data in SSIS (CRM Web API)"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>In our previous article, we talked about\u00a0how to\u00a0read from Dynamics CRM<\/a>\u00a0using easy to use\u00a0SSIS Dynamics CRM Source connector<\/a>. In this post, you will learn how to use SSIS JSON Source (for REST API)<\/a>\u00a0instead of CRM Source. Using SSIS JSON Source you can read virtually any REST API or JSON file data including Dynamics CRM \/ Dataverse. For this article, our goal is to learn How to read Microsoft Dynamics 365 data using Web API (OData API) in SSIS.<\/strong>\u00a0We will use drag and drop approach without any coding.<\/p>\n

 <\/p>\n

 <\/p>\n

Why use Web API (OAuth) for Dynamics 365 CRM?<\/strong><\/h2>\n

Microsoft has announced depreciation of older CRM API (SOAP API with WS-Trust Authentication) which means they are no longer going to support the older style of authentication so no option but to switch to newer authentication \/ API.<\/p>\n

In the older approach (SOAP API with WS-Trust Authentication) you must pass your User Name and Password to authenticate, also if MFA \/ 2FA (Multi-Factored Auth) is turned on by organization policy you cannot use your user id \/ password in API call. In new authentication (i.e. OAuth) you have to use Client Id and Client Secret only.<\/p>\n

Now let’s look at the step by step approach to call Dynamics 365 REST API.<\/p>\n

Step-1 : Register Dynamics CRM \/ Dataverse App obtain App Id \/ Client Secret<\/h2>\n

The very first step to access Microsoft Dynamics CRM 365 API is to obtain Application Id (also referred as Client Id)\u00a0 and Client Secret. We will use this information later in this article (on OAuth connection UI).<\/p>\n

    \n
  1. Register Dynamics CRM \/ Dataverse App With Azure<\/a>\u00a0and get the credential (App Id, Client Secret).<\/li>\n<\/ol>\n

    <\/h2>\n

    Step-2 :\u00a0Read Microsoft Dynamics 365 API data in SSIS using JSON Source<\/h2>\n

    Let’s start with an example. We use SSIS JSON Source Task to make calls to Microsoft Dynamics 365 API, we will load Dynamics 365 Leads data to SQL Server.<\/p>\n

    Please refer to this link for more information:\u00a0Microsoft Dynamics 365 Web API Reference<\/a><\/p>\n

    Find out API Url for your environment (organization url)<\/h3>\n

    Once we are done with the creation of the Azure AD app to obtain App Id \/Secret out next step is to find out the base API URL for your organization \/ environment.<\/p>\n

    If you have multiple environments (i.e. Prod, Sandbox) then each environment might have its own unique URL)<\/p>\n

      \n
    1. To find your API Service URL, Click on Gear Icon<\/strong><\/li>\n
    2. Choose Advanced Settings<\/strong><\/li>\n
    3. Click down arrow on Settings<\/strong><\/li>\n
    4. Click Customization<\/strong><\/li>\n
    5. Click Developer Resource<\/strong> Link<\/li>\n
    6. On this page now you can see various URLs. For examples used in this article, we need to get is Service Root URL<\/strong> for Web API<\/strong><\/li>\n<\/ol>\n
      \"dynamics-crm-find-organization-url-soap-web-api\"<\/a>

      How to Find Organization Id \/ Web API URL \/ SOAP API Service URL (Unique Environment Instance URL)<\/p><\/div>\n

       <\/p>\n

      Create SSIS Data Flow to read data from Dynamics CRM \/ Dataverse<\/h3>\n
        \n
      1. \n First of All, Drag and drop Data Flow Task from SSIS Toolbox and double click it to edit.<\/p>\n

      2. \n Furthermore, Drag and Drop JSON Source(REST API and File) File Task from the SSIS Toolbox.<\/p>\n

        \"Drag<\/a>

        Drag and Drop JSON Source Component<\/p><\/div>\n <\/li>\n<\/ol>\n

        <\/h3>\n

        Create OAuth Connection for Dynamics CRM \/ Dataverse<\/h3>\n

        Now lets look at how to create and configure OAuth connection to call Web API for Dynamics CRM \/ Dataverse<\/p>\n

          \n
        1. \n Check Use Credentials Option and select a\u00a0new ZS-OAuth Connection from the drop-down.<\/p>\n

        2. \n Once OAuth Connection UI opens up. Configure like below on General Tab.
          \n Change OAuth Version to OAuth2<\/strong>
          \n Change Grant Type to Client Credentials<\/strong>
          \n Enter Client Id<\/strong> (Azure AD App Id) (Obtained in the Previous Section)
          \n Enter Client Secret<\/strong> (Obtained in the Previous Section)
          \n Enter Token URL<\/strong> (see below)<\/p>\n
        3. \n Now go to Advanced Tab and configure like belowEnter Extra Attribute for \/auth<\/strong> as\u00a0\u00a0resource=https:\/\/<your-instance-id>.api.<your-crm-region>.dynamics.com<\/span><\/p>\n

        4. Click OK to Save connection.<\/li>\n<\/ol>\n

          Get all Accounts, Contacts, and Leads using Dynamics 365 API<\/h3>\n

          Now let’s configure API URL from where you like to read data.<\/p>\n

          For example, if you just use the root URL then it will list all possible entries<\/p>\n

          Root URL: https:\/\/{{organization-uri}}\/api\/data\/v9.1\/<\/span><\/p>\n

            \n
          1. Double click on the Component to Edit the Configurations.<\/li>\n
          2. Change Data Format to\u00a0OData<\/strong>.<\/li>\n
          3. \n In the Web URL Field, you can enter any valid URL for Web API. Please refer to this link of Dynamics 365 Rest API<\/a> for more information.Example Data URLs<\/strong><\/p>\n
            https:\/\/{{organization-uri}}\/api\/data\/v9.1\/\r\nhttps:\/\/{{organization-uri}}\/api\/data\/v9.1\/accounts\r\nhttps:\/\/{{organization-uri}}\/api\/data\/v9.1\/contacts\r\nhttps:\/\/{{organization-uri}}\/api\/data\/v9.1\/leads<\/pre>\n
                    Here, replace\u00a0{{organization-uri}} <\/strong>in URL to your Microsoft Dynamics 365 site domain name (Example\u00a0zappy6.api.crm.dynamics.com<\/span><\/strong>\u00a0Dynamics 365 URL).<\/strong><\/p>\n
                    
            \"Set<\/a>
            Set URL in JSON Source<\/p><\/div>\n    <\/li>\n<\/ol>\n
            Preview \/ Query Dynamics 365 Data<\/h3>\n
              \n
            1. Now select Array filter as $.value[*]<\/span>\u00a0 (Click Select Filter<\/strong> Button – Choose value node)<\/li>\n
            2. \n        Click on the Preview button to get the data.<\/p>\n
                      
            3. Finally, click OK to save JSON Source settings.<\/li>\n
            4. That\u2019s it we are ready to load Dynamics 365 API data to SQL Server.<\/li>\n<\/ol>\n
              Load Microsoft Dynamics 365 API data into SQL Server<\/h3>\n
              ZappySys SSIS PowerPack makes it easy to load data from various sources such as REST, SOAP, JSON, XML, CSV or from other source into SQL Server, or PostgreSQL, or Amazon Redshift, or other  targets. The Upsert Destination<\/strong> component allows you to automatically insert new records and update existing ones based on key columns. Below are the detailed steps to configure it.<\/p>\r\n
              Step 1: Add Upsert Destination to Data Flow<\/h3>\r\n
                \r\n
              1. Drag and drop the Upsert Destination<\/strong> component from the SSIS Toolbox.<\/li>\r\n
              2. Connect your source component (e.g., JSON \/ REST \/ Other Source) to the Upsert Destination.<\/li>\r\n<\/ol>\r\n
                \r\n\r\n\"\"<\/a>\r\n
                SSIS - Data Flow - Drang and Drop Upsert Destination Component<\/p>\r\n<\/div>\r\n
                Step 2: Configure Target Connection<\/h3>\r\n
                  \r\n
                1. Double-click the Upsert Destination<\/strong> component to open the configuration window.<\/li>\r\n
                2. Under Connection<\/strong>, select an existing target connection or click NEW<\/strong> to create a new connection.\r\n