Introduction
Please visit this page to see all Pre-Configured ready to use API connectors which you can use in SSIS API Source / SSIS API Destination OR API ODBC Driver (for non-SSIS Apps such as Excel, Power BI, Informatica).
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 the steps listed in this article, you will need to make sure the following prerequisites are met:- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
- (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.
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 –
- Log into Microsoft Azure portal to register a custom app.
- Register a new application by clicking New Registration link.
New App Registration in Azure portal
- Provide the name of the custom app and who can access the app in the organization.
Register an OneDrive App
- Go to the App overview and add a Redirect URL.
Add a Redirect URL
- Click on “Add a Platform” under Platform Configuration section and then select “Web” under Web applications section to enter a Redirect URL.
1https://zappysys.com/oauth
Redirect URL
- Create a Client Secret key which will be used to Authenticate the custom Azure app.
Add a Client Secret
Secret Key Expiration Period
Specify Secret Key
Note – Take a note of Client Secret, it will be required while configuring OAuth connection in the SSIS later. - Add API Permissions for OneDrive API.
OneDrive API Permissions
- Select the following permissions from the Delegated Permissions section.
123User.ReadBasic.AllFiles.Readoffline_access
Select Delegated Permissions
- Take note of Client ID, it will be required while configuring OAuth connection in the SSIS later.
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 –
- Go to the Microsoft Graph Explorer.
- Sign in to Graph Explorer.
SSIS GraphExplorer SignIn
- Run below OneDrive API to get a list of the files from the OneDrive.
1https://graph.microsoft.com/v1.0/me/drive/root/children
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 –
- Create a new ZS-OAuth connection in SSIS.
ZappySys OAuth Connection in SSIS
- 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
- Go to the Advanced tab and provide Return URL.
1https://zappysys.com/oauth
Add Redirect URL
- Click on Generate Token will give a login screen which will allow to generate access and refresh token for the app.
- Approve the requested permissions bu Accepting it.
Approve Permissions Requested
- Token has been generate successfully.
Refresh Token Successful
- Let’s test the connection to make sure it is working fine.
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.
- Here is the OneDrive API which allows to read content of the file.
1https://graph.microsoft.com/v1.0/me/drive/Items/<File ID>/content - 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.
1https://graph.microsoft.com/v1.0/me/drive/Items/01W7L3VCUBS27ILUQKENAK4LQGGTJ2R3TT/content - Let’s create a SSIS package with Data Flow Task from SSIS Toolbox.
- 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.
1https://graph.microsoft.com/v1.0/me/drive/Items/01W7L3VCWMCGPYZLM2IRAYWROSH4XMQT7A/content - Configure REST API task as follows.
Binary File Configuration in ZappySys REST API Task
- Configure the responses setting as below.
Binary File Configuration in ZappySys REST API Task
NOTE: Ensure that the Treat response as the binary checkbox is checked otherwise the file will be downloaded but corrupted as zip/mp3 etc. file format is binary. - Check if the request is made correctly by clicking Test Request/Response.
Example of Testing the Request
- Let’s have a look at the folder where the file has been downloaded. Here is an 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.
|
1 |
https://graph.microsoft.com/v1.0/me/drive/items/01CYZLFJDYxxxxxxx/workbook/worksheets('test')/range(address='A1:B200') |
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.
- 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 –
1https://graph.microsoft.com/v1.0/me/drive/Items/01W7L3VCSEADQ457W54RBI3I47YGFJOD2I
Delete OneDrive File
- 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.
