Consumer OneDrive connector

• Starter
• Business
• Enterprise
• On-Premise
• Add-on

Overview

Etlworks Consumer OneDriver connector allows fast, real-time access to files stored in the consumer OneDrive account. The connector works just like any other database connector.

To access files in OneDrive for Business accounts, use OneDrive for Business connector.

Etlworks partnered with CData to provide access to the OneDrive using industry-standard JDBC protocol. Read about CData OneDrive JDBC connector.

When to use the Consumer OneDrive connector

Use this connector to create Flows that download files from and upload files into OneDrive for consumer accouns.

Prerequisites

Enable the OneDrive connector for your Etlworks account. Contact support@etlworks.com to enable the connector.

Create a Connection

You can create a Connection in just two steps, and they are the following:

Step 1. In the Connections window, click +, type in onedrive, and select OneDrive (premium).

Step 2. Enter the Connection parameters:

• Permissions: select the permissions that will be used to access the OneDrive account. 
• OAuth Token: sign in with Microsoft.

Use Other parameters to specify the Connection string options. Read about available Connection string options.

Work with OneDrive

OneDrive Data Model

The connector models the OneDrive API as relational tables. Any changes to the remote data are immediately reflected in your queries; the table definitions are dynamically retrieved. When you connect, the driver connects to OneDrive and gets the list of tables and the metadata for the tables by calling the appropriate Web services.

Read about OneDrive data model.

Stored procedures

Stored procedures are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the data retrieval is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return a dataset that contains the collection of tuples that constitute the response.

Read about available stored procedures in the data model.  

To call stored procure from the SQL Flow or Before/After SQL, use EXEC sp_name params=value. Example:

EXEC DownloadFile ResourcePath='path',LocalFile='file' 

SQL Compliance

Read about SQL Compliance.

Download file in the OneDrive

Here's are the steps on how you can download a file in the OneDrive:

Step 1. Create OneDrive Connection.

Step 2. Create a new SQL Flow.

Step 3. Select a Connection created in step 1.

Step 4. Select the Parameters tab and enter the following SQL:

EXEC DownloadFile ResourcePath='path',LocalFile='file' 

Where ResourcePath is a location of the file in the OneDrive and LocalFile is the location of the file in the local (server) storage, for example {app.data}/test.json.

Upload file to OneDrive

Here's are the steps on how you can upload a file in the OneDrive:

Step 1. Create OneDrive Connection.

Step 2. Create a new SQL Flow.

Step 3. Select a Connection created in step 1.

Step 4. Select the Parameters tab and enter the following SQL:

EXEC UploadFile FileName='filename', LocalFile='/path/filename'

Where FileName is a location of the file in OneDrive and LocalFile is a local file in the server storage to upload {app.data}/test/json.

Browse folders and files in OneDrive

You must have OneDrive Connection to browse objects and run SQL queries.

Use the Etlworks Explorer to browse data and metadata in OneDrive as well as execute DML and SELECT queries against the OneDrive Connection.