How to call OneDrive API in SSIS (Upload, Download)

Introduction

Microsoft Graph API is a unified way to access many Microsoft services API including OneDrive API. In our previous blog post we saw how to call Office 365 API Graph API. In this post, you will focus on how to access OneDrive API in SSIS and use REST API Task or connector like SSIS JSON / REST API Source Connector to load Read / Download / Upload data from OneDrive.

OneDrive allows to store files on the cloud and and we have seen growing requirements in downloading/Uploading files from there. This post will provide step by step details of how to Upload/Download files from OneDrive.

 

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it).
  4. Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

Register Microsoft App for OAuth Authentication

In this section, you will learn how to register a custom app in Microsoft Azure portal that will allow access to the OneDrive. So, let’s get started –

  1. Log into Microsoft Azure portal to register a custom app.
  2. Register a new application by clicking New Registration link.
    New App Registration in Azure portal

    New App Registration in Azure portal

  3. Provide the name of the custom app and who can access the app in the organization.
    Register an OneDrive App

    Register an OneDrive App

  4. Go to the App overview and add a Redirect URL.
    Add a Redirect URL

    Add a Redirect URL

  5. Click on “Add a Platform” under Platform Configuration section and then select “Web” under Web applications section to enter a Redirect URL.
    Redirect URL

    Redirect URL

  6. Create a Client Secret key which will be used to Authenticate the custom Azure app.
    Add a Client Secret

    Add a Client Secret

    Secret Key Expiration Period

    Secret Key Expiration Period

    Specify Secret Key

    Specify Secret Key

    Note – Take a note of Client Secret, it will be required while configuring OAuth connection in the SSIS later.
  7. Add API Permissions for OneDrive API.
    OneDrive API Permissions

    OneDrive API Permissions

  8. Select following permissions from the Delegated Permissions section.
    Select Delegated Permissions

    Select Delegated Permissions

  9. Take a note of Client ID, it will be required while configuring OAuth connection in the SSIS later.
    App Client ID

    App Client ID

Get OneDrive File ID

In this section, you will learn how to get the File ID of the CSV file through OneDrive API. So, let’s get started –

  1. Go to the Microsoft Graph Explorer.
  2. Sign in to Graph Explorer.
    SSIS GraphExplorer SignIn

    SSIS GraphExplorer SignIn

  3. Run below OneDrive API to get a list of the files from the OneDrive.
    Microsoft Graph Explorer

    Microsoft Graph Explorer

    NOTE: Take a note of File ID of the CSV file. For this example, we will download invoices.csv file.

Create OAuth Connection in SSIS

In this section, you will learn how to create an OAuth connection in SSIS to an Azure custom app. So, let’s get started –

  1. Create a new ZS-OAuth connection in SSIS.
    ZappySys OAuth Connection in SSIS

    ZappySys OAuth Connection in SSIS

  2. Configure OAuth connection with following settings.
    Client ID Provide Client ID from App Registration steps
    Client Secret Provide Client Secret from App Registration steps
    Authorization URL https://login.microsoftonline.com/organizations/oauth2/v2.0/authorize
    Access Token URL https://login.microsoftonline.com/organizations/oauth2/v2.0/token
    Scope/Permissions User.ReadBasic.All
    Files.Read.All
    offline_access
    OAuth Connection Configuration

    OAuth Connection Configuration

  3. Click on Generate Token will give a login screen which will allow to generate access and refresh token for the app.
  4. Approve the requested permissions bu Accepting it.
    Approve Permissions Requested

    Approve Permissions Requested

  5. Token has been generate successfully.
    Refresh Token Successful

    Refresh Token Successful

  6. Go to the Advanced tab and provide Return URL.
    Add Redirect URL

    Add Redirect URL

  7. Let’s test the connection to make sure it is working fine.
    Connection Successful

    Connection Successful

Download CSV File Through OneDrive REST API in SSIS

In this section, you will learn how to download a CSV file through OneDrive API in SSIS and save it into a CSV file.

  1. Here is the OneDrive API which allows to read content of the file.
  2. Replace <File ID> in above step#1 with the file ID that we have got in the Get OneDrive File ID section. Final URL will look something like below.
  3. Let’s create a SSIS package with Data Flow Task from SSIS Toolbox.""/content</pre
  4. Replace <File ID> in above step#1 with the file ID that we have got in the Get OneDrive File ID section. Final URL will look something like below.
  5. Configure REST API task as follows.
    Binary File Configuration in ZappySys REST API Task

    Binary File Configuration in ZappySys REST API Task

  6. Configure responses setting as below.
    Binary File Configuration in ZappySys REST API Task

    Binary File Configuration in ZappySys REST API Task

    NOTE: Ensure that Treat response as binary checkbox is checked otherwise file will be downloaded but corrupted as zip/mp3 etc. file format are binary.
  7. Check if request is made correctly by clicking Test Request/Response.
    Example of Testing the Request

    Example of Testing the Request

  8. Let’s have a look at the folder where file has been downloaded. Here is the example –
    Downloaded File Example

    Downloaded File Example

Download a Text File Through OneDrive API in SSIS

Here is another technique that can be used to download a CSV/text file through OneDrive API without knowing it’s file ID but file name. Please check this link for more details.

Download Excel Sheets Through OneDrive API in SSIS

JSON Source also supports reading API response which returns 2D arrays. Such as Office Excel Spreadsheet API to read excel data. Check this blog post to learn a similar scenario to parse 2D arrays. There are few other options for parsing 2D arrays which are documented here.

Upload a File Through OneDrive API in SSIS

In this section, you will learn how to upload a file through OneDrive API in SSIS. Uploading a file has few additional steps and has been documented separately. Please check this link for more details.

Delete a File Through OneDrive API in SSIS

In this section, you will learn how to delete a file from OneDrive through OneDrive APIs in SSIS.

  1. Add a REST API task and configure as follows. Make sure to use the File ID of the file which needs to be deleted from OneDrive. Example URL –
    Delete OneDrive File

    Delete OneDrive File

  2. Execute the task and you will have file deleted from OneDrive.

Conclusion

In this article, we have learned how to Upload/Download/Delete data/file from OneDrive API in SSIS. We used SSIS REST API Task to extract data through OneDrive API in SSIS. Download SSIS PowerPack to try many other automation scenarios not discussed in this article.

Posted in REST API, SSIS OAuth Connection, SSIS REST API Task and tagged , , .