OneDrive Connector for SSIS
OneDrive Connector can be used to integrate OneDrive and your defined data source, e.g. Microsoft SQL, Oracle, Excel, Power BI, etc. Get, write, delete OneDrive data in a few clicks! You can use this connector to integrate OneDrive data inside SSIS and SQL Server. Let's take a look at the steps below to see how exactly to accomplish that.
Download
Documentation
If you are new to SSIS and ZappySys SSIS PowerPack then check these links to get started:
|
Connect to OneDrive in other apps
|
Video Tutorial - Integrate OneDrive data in SSIS
This video covers following and more so watch carefully. After watching this video follow the steps described in this article.
- How to download / install required driver for OneDrive integration in SSIS
- How to configure connection for OneDrive
- Features about API Source (Authentication / Query Language / Examples / Driver UI)
- Using OneDrive Connection in SSIS
Prerequisites
Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is downloaded and installed (download it). Check Getting started section for more information.
Read data from OneDrive in SSIS (Export data)
In this section we will learn how to configure and use OneDrive Connector in API Source to extract data from OneDrive.
-
Begin with opening Visual Studio and Create a New Project.
Select Integration Service Project and in new project window set the appropriate name and location for project. And click OK.
-
In the new SSIS project screen you will find the following:
- SSIS ToolBox on left side bar
- Solution Explorer and Property Window on right bar
- Control flow, data flow, event Handlers, Package Explorer in tab windows
- Connection Manager Window in the bottom
Note: If you don't see ZappySys SSIS PowerPack Task or Components in SSIS Toolbox, please refer to
this help link.
-
Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox. Double click on the Data Flow Task to see Data Flow designer.
-
From the SSIS toolbox drag and API Source (Predefined Templates) on the data flow designer surface, and double click on it to edit it:
-
Select New Connection to create a new connection:
-
Use a preinstalled OneDrive Connector from Popular Connector List or press Search Online radio button to download OneDrive Connector. Once downloaded simply use it in the configuration:
OneDrive
-
Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.
Use delegated access (User Credentials) whenever you want to let a signed-in user work with their own resources or resources they can access. Whether it's an admin setting up policies for their entire organization or a user deleting an email in their inbox, all scenarios involving user actions should use delegated access.
[API reference]
Steps how to get and use OneDrive credentials
Follow these simple steps below to create Microsoft Entra ID application with delegated access:
WARNING:
To automate your company's processes, make sure you use a system/generic account (e.g. automation@my-company.com
).
When you use a personal account which is tied to a specific employee profile and that employee leaves the company,
the token may become invalid and any automated processes using that token will start to fail.
-
Navigate to the Azure Portal and log in using your credentials.
-
Access Microsoft Entra ID.
-
Register a new application by going to
App registrations
and clicking on New registration button:
-
When configuration window opens, configure these fields:
-
Supported account type
- Use
Accounts in this organizational directory only
, if you need access to data in your organization only.
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
.
- Use
https://zappysys.com/oauth
as the URL.
-
After registering the app, copy the Application (client) ID for later:
-
Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs to use later in the configuration:
-
Now go to SSIS package or ODBC data source and use the copied values in User Credentials authentication configuration:
- In the Authorization URL field paste the OAuth authorization endpoint (v2) URL value you copied in the previous step.
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
-
In the Scope field use the default value or select individual scopes, e.g.:
-
email
-
offline_access
-
openid
-
profile
-
User.Read
-
Files.Read.All
-
Files.ReadWrite.All
-
Press Generate Token button to generate Access and Refresh Tokens.
-
Optional step. Choose Default Site Id from the drop down menu.
-
Click Test Connection to confirm the connection is working.
-
Done! Now you are ready to use the API Connector!
Configuring authentication parameters
OneDrive
User Credentials [OAuth]
https://graph.microsoft.com/v1.0
Application-only access is broader and more powerful than delegated access (User Credentials), so you should only use app-only access where needed. Use it when: 1. The application needs to run in an automated way, without user input (for example, a daily script that checks emails from certain contacts and sends automated responses). 2. The application needs to access resources belonging to multiple different users (for example, a backup or data loss prevention app might need to retrieve messages from many different chat channels, each with different participants). 3. You find yourself tempted to store credentials locally and allow the app to sign in 'as' the user or admin.
[API reference]
Steps how to get and use OneDrive credentials
Follow these simple steps below to create Microsoft Entra ID application with application access permissions. Using following steps, you can grant very granular app permissions to access Full site(s), List(s), List Item(s) and File(s). Choose permission based on your need.
Create OAuth app
-
Navigate to the Azure Portal and log in using your credentials.
-
Access Microsoft Entra ID.
-
Register a new application by going to
App registrations
and clicking on New registration button:
-
When configuration window opens, configure these fields:
-
Supported account type
- e.g. select
Accounts in this organizational directory only
if you need access to data in your organization only.
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
.
- Leave the URL field empty.
-
After registering the app, copy the Application (client) ID for later:
-
Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs:
-
Continue and create Client secret:
-
Then copy the Client secret for later steps:
-
Continue by adding permissions for the app by going to the API permissions section, and clicking on Add a permission:
-
Select Microsoft Graph:
-
Then choose Application permissions option:
-
Continue by adding these Files (OneDrive) permissions (Just search for "Site" and then select desired permissions):
-
Move on by addding these Sites (SharePoint) permissions (You can search for "Site" and then select desired permissions):
-
Finish by clicking Add permissions button:
-
Now it's time to Grant admin consent for your application:
-
Confirm all the permissions are granted:
-
Now go to SSIS package or ODBC data source and use the copied values in Application Credentials authentication configuration:
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
- In the Client Secret field paste the Client secret value you copied in the previous step.
- Optional step. Choose Default Site Id from the drop down menu.
-
Click Test Connection to confirm the connection is working.
-
Done! Let's move on to the next step.
Step-2 (Method#1 - New Approach): Grant permission to app using permission API (UI is not available yet)
In year 2024 microsoft launched
*.Selected
permission scopes and new APIs to grant granular permissions to the resource for app access scenario. Unfortunatly, there is no User interface available yet to control this access so you have to call Admin API or use PowerShell script to control this.
[Click here to learn more].
Follow these simple steps to configure permissions for app we created earlier (steps assume you are the owner of the resource and like to grant some permissions e.g. read, write, full, owner):
- Open PowerShell (Run as Admin user)
- Install Graph API module by running this command
Import-Module Microsoft.Graph.Sites
- Now you can call following PowerShell code to set "read" and "write" permission for the app we created earlier (assuming app id is 89ea5c94-7736-4e25-95ad-3fa95f62b66e).
#More info https://learn.microsoft.com/en-us/graph/permissions-selected-overview?tabs=powershell
#Step-1: Install module if not found
#Install-Module Microsoft.Graph.Sites
#Step-2: set permission for app 89xxx6e and site zappysys.xx.efcdd21xxxxe2
Import-Module Microsoft.Graph.Sites
#Step-3: Login
Connect-MgGraph
#Step-4: Set Parameters for API Call (set Permissions, SiteId and AppId - Replace under #TODO)
You can find SiteId by visiting this URL in browser (assuming you visted SharePoint site and already logged in) https://{your-company}.sharepoint.com/_api/site --OR-- for sub-site use https://{your-company}.sharepoint.com/sites/{your-site}/_api/site. Find Id from the response (e.g. <Id m:type="Edm.Guid">)
$siteId="yourcompany.sharepoint.com,efcddxxxxxxx104d8b5e3,8c9c6xxxxxxxx84e2"
#--OR-- simple id
#$siteId="efcddxxxxxxx104d8b5e3"
$params = @{
roles = @(
#possible options are read, write, fullcontrol, owner (write includes read too)
#TODO: Change here
"write"
)
grantedTo = @{
application = @{
#find this Client Id (Application Id) from the Azure Portal - Application page (https://portal.azure.com/#view/Microsoft_AAD_RegisteredApps/ApplicationsListBlade)
#TODO: Change here
id = "c3e9xxxxxx-xxxxx-xxxxx-xxxx-xxxxxb30c1"
}
}
}
#Step-5: Grant Permission
New-MgSitePermission -SiteId $siteId -BodyParameter $params
Configuring authentication parameters
OneDrive
Application Credentials [OAuth]
https://graph.microsoft.com/v1.0
-
Select the desired endpoint, change/pass the properties values, and click on Preview Data button to make the API call.
API Source - OneDrive
OneDrive Connector can be used to integrate OneDrive and your defined data source, e.g. Microsoft SQL, Oracle, Excel, Power BI, etc. Get, write, delete OneDrive data in a few clicks!
-
That's it! We are done! Just in a few clicks we configured the call to OneDrive using OneDrive Connector.
You can load the source data into your desired destination using the Upsert Destination, which supports SQL Server, PostgreSQL, and Amazon Redshift. We also offer other destinations such as CSV, Excel, Azure Table, Salesforce, and more. You can check out our SSIS PowerPack Tasks and components for more options. (*loaded in Trash Destination)
Write data to OneDrive using SSIS (Import data)
In this section we will learn how to configure and use OneDrive Connector in the API Destination to write data to OneDrive.
Video tutorial
This video covers following and more so watch carefully. After watching this video follow the steps described in this article.
- How to download SSIS PowerPack for OneDrive integration in SSIS
- How to configure connection for OneDrive
- How to write or lookup data to OneDrive
- Features about SSIS API Destination
- Using OneDrive Connector in SSIS
Step-by-step instructions
In upper section we learned how to read data, now in this section we will learn how to configure OneDrive in the API Source to POST data to the OneDrive.
-
Read the data from the source, being any desired source component. In example we will use ZappySys Dummy Data Source component.
-
From the SSIS Toolbox drag and drop API Destination (Predefined Templates) on the Data Flow Designer surface and connect source component with it, and double click to edit it.
-
Select New Connection to create a new connection:
API Destination - OneDrive
OneDrive Connector can be used to integrate OneDrive and your defined data source, e.g. Microsoft SQL, Oracle, Excel, Power BI, etc. Get, write, delete OneDrive data in a few clicks!
-
Use a preinstalled OneDrive Connector from Popular Connector List or press Search Online radio button to download OneDrive Connector. Once downloaded simply use it in the configuration:
OneDrive
-
Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.
Use delegated access (User Credentials) whenever you want to let a signed-in user work with their own resources or resources they can access. Whether it's an admin setting up policies for their entire organization or a user deleting an email in their inbox, all scenarios involving user actions should use delegated access.
[API reference]
Steps how to get and use OneDrive credentials
Follow these simple steps below to create Microsoft Entra ID application with delegated access:
WARNING:
To automate your company's processes, make sure you use a system/generic account (e.g. automation@my-company.com
).
When you use a personal account which is tied to a specific employee profile and that employee leaves the company,
the token may become invalid and any automated processes using that token will start to fail.
-
Navigate to the Azure Portal and log in using your credentials.
-
Access Microsoft Entra ID.
-
Register a new application by going to
App registrations
and clicking on New registration button:
-
When configuration window opens, configure these fields:
-
Supported account type
- Use
Accounts in this organizational directory only
, if you need access to data in your organization only.
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
.
- Use
https://zappysys.com/oauth
as the URL.
-
After registering the app, copy the Application (client) ID for later:
-
Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs to use later in the configuration:
-
Now go to SSIS package or ODBC data source and use the copied values in User Credentials authentication configuration:
- In the Authorization URL field paste the OAuth authorization endpoint (v2) URL value you copied in the previous step.
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
-
In the Scope field use the default value or select individual scopes, e.g.:
-
email
-
offline_access
-
openid
-
profile
-
User.Read
-
Files.Read.All
-
Files.ReadWrite.All
-
Press Generate Token button to generate Access and Refresh Tokens.
-
Optional step. Choose Default Site Id from the drop down menu.
-
Click Test Connection to confirm the connection is working.
-
Done! Now you are ready to use the API Connector!
Configuring authentication parameters
OneDrive
User Credentials [OAuth]
https://graph.microsoft.com/v1.0
Application-only access is broader and more powerful than delegated access (User Credentials), so you should only use app-only access where needed. Use it when: 1. The application needs to run in an automated way, without user input (for example, a daily script that checks emails from certain contacts and sends automated responses). 2. The application needs to access resources belonging to multiple different users (for example, a backup or data loss prevention app might need to retrieve messages from many different chat channels, each with different participants). 3. You find yourself tempted to store credentials locally and allow the app to sign in 'as' the user or admin.
[API reference]
Steps how to get and use OneDrive credentials
Follow these simple steps below to create Microsoft Entra ID application with application access permissions. Using following steps, you can grant very granular app permissions to access Full site(s), List(s), List Item(s) and File(s). Choose permission based on your need.
Create OAuth app
-
Navigate to the Azure Portal and log in using your credentials.
-
Access Microsoft Entra ID.
-
Register a new application by going to
App registrations
and clicking on New registration button:
-
When configuration window opens, configure these fields:
-
Supported account type
- e.g. select
Accounts in this organizational directory only
if you need access to data in your organization only.
-
Redirect URI:
- Set the type to
Public client/native (mobile & desktop)
.
- Leave the URL field empty.
-
After registering the app, copy the Application (client) ID for later:
-
Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs:
-
Continue and create Client secret:
-
Then copy the Client secret for later steps:
-
Continue by adding permissions for the app by going to the API permissions section, and clicking on Add a permission:
-
Select Microsoft Graph:
-
Then choose Application permissions option:
-
Continue by adding these Files (OneDrive) permissions (Just search for "Site" and then select desired permissions):
-
Move on by addding these Sites (SharePoint) permissions (You can search for "Site" and then select desired permissions):
-
Finish by clicking Add permissions button:
-
Now it's time to Grant admin consent for your application:
-
Confirm all the permissions are granted:
-
Now go to SSIS package or ODBC data source and use the copied values in Application Credentials authentication configuration:
- In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
- In the Client ID field paste the Application (client) ID value you copied in the previous step.
- In the Client Secret field paste the Client secret value you copied in the previous step.
- Optional step. Choose Default Site Id from the drop down menu.
-
Click Test Connection to confirm the connection is working.
-
Done! Let's move on to the next step.
Step-2 (Method#1 - New Approach): Grant permission to app using permission API (UI is not available yet)
In year 2024 microsoft launched
*.Selected
permission scopes and new APIs to grant granular permissions to the resource for app access scenario. Unfortunatly, there is no User interface available yet to control this access so you have to call Admin API or use PowerShell script to control this.
[Click here to learn more].
Follow these simple steps to configure permissions for app we created earlier (steps assume you are the owner of the resource and like to grant some permissions e.g. read, write, full, owner):
- Open PowerShell (Run as Admin user)
- Install Graph API module by running this command
Import-Module Microsoft.Graph.Sites
- Now you can call following PowerShell code to set "read" and "write" permission for the app we created earlier (assuming app id is 89ea5c94-7736-4e25-95ad-3fa95f62b66e).
#More info https://learn.microsoft.com/en-us/graph/permissions-selected-overview?tabs=powershell
#Step-1: Install module if not found
#Install-Module Microsoft.Graph.Sites
#Step-2: set permission for app 89xxx6e and site zappysys.xx.efcdd21xxxxe2
Import-Module Microsoft.Graph.Sites
#Step-3: Login
Connect-MgGraph
#Step-4: Set Parameters for API Call (set Permissions, SiteId and AppId - Replace under #TODO)
You can find SiteId by visiting this URL in browser (assuming you visted SharePoint site and already logged in) https://{your-company}.sharepoint.com/_api/site --OR-- for sub-site use https://{your-company}.sharepoint.com/sites/{your-site}/_api/site. Find Id from the response (e.g. <Id m:type="Edm.Guid">)
$siteId="yourcompany.sharepoint.com,efcddxxxxxxx104d8b5e3,8c9c6xxxxxxxx84e2"
#--OR-- simple id
#$siteId="efcddxxxxxxx104d8b5e3"
$params = @{
roles = @(
#possible options are read, write, fullcontrol, owner (write includes read too)
#TODO: Change here
"write"
)
grantedTo = @{
application = @{
#find this Client Id (Application Id) from the Azure Portal - Application page (https://portal.azure.com/#view/Microsoft_AAD_RegisteredApps/ApplicationsListBlade)
#TODO: Change here
id = "c3e9xxxxxx-xxxxx-xxxxx-xxxx-xxxxxb30c1"
}
}
}
#Step-5: Grant Permission
New-MgSitePermission -SiteId $siteId -BodyParameter $params
Configuring authentication parameters
OneDrive
Application Credentials [OAuth]
https://graph.microsoft.com/v1.0
-
Select the desired endpoint, change/pass the properties values, and go to the Mappings tab to map the columns.
API Destination - OneDrive
OneDrive Connector can be used to integrate OneDrive and your defined data source, e.g. Microsoft SQL, Oracle, Excel, Power BI, etc. Get, write, delete OneDrive data in a few clicks!
-
Finally, map the desired columns:
API Destination - OneDrive
OneDrive Connector can be used to integrate OneDrive and your defined data source, e.g. Microsoft SQL, Oracle, Excel, Power BI, etc. Get, write, delete OneDrive data in a few clicks!
-
That's it; we successfully configured the POST API Call. In a few clicks we configured the OneDrive API call using ZappySys OneDrive Connector
Load OneDrive data into SQL Server using Upsert Destination (Insert or Update)
Once you read data from the desired source, now let's see how to Load OneDrive data in SQL Server using Upsert Destination.
Upsert Destination can Merge/Synchronize data from source to target for Microsoft SQL Server, PostgreSql and Redshift. It supports very fast Bulk Upsert (Update or Insert) operation along and Bulk delete.
-
From the SSIS toolbox drag and drop Upsert Destination on the dataflow designer surface
Connect our Source component to Upsert Destination
-
Double click on Upsert Destination component to configure it.
-
Select the desired Microsoft SQL Server/PostgreSql/Redshift Target Connection or click NEW to create new connection. Select Target Table or click NEW to create new table based on source columns.
Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS
-
Set Action to Upsert => (insert if not matching in target else update). Select Target Connection and Target Table. Check on Insert and Update. Click on Map All to Mappings all columns and check on Only Primary Key columns.
-
Click on OK to save Upsert Destination settings UI.
-
That's it. Run the SSIS Package and it will read the data from the OneDrive and load the data in the SQL Server/PostgreSql/Redshift using Upsert Destination.
Deploy and schedule SSIS package
After you are done creating SSIS package, most likely, you want to deploy it to SQL Server Catalog and run it periodically.
Just follow the instructions in How to design, debug, deploy, schedule SSIS Package (In SQL Agent and Catalog) article to see how to do it.
Advanced topics
Actions supported by OneDrive Connector
OneDrive Connector support following actions for REST API integration. If some actions are not listed below then you can easily edit Connector file and enhance out of the box functionality.
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
Myself (only use for User Credentials) |
/me |
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
List Root (Non Recursive)
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Lists Folder Items (Non-Recursive Children)
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
Folder Id or Path (e.g. root: -OR- root:/myfolder: ) - Max 200 items listed |
Specify a Folder Id -OR- Path. Syntax: [{folder-id}] -OR- [root:/{folder-path}:] (Example: root:/myfolder/subfolder: )
|
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Search Type (Default=Recursive) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive - All Files (Blank) - For User Creds Only |
search(q='') |
Non-Recursive / Children - All Files - For Non-Shared Items Only |
children |
Shared With Me (For DriveId 'me') - For User Creds Only |
sharedWithMe |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Path (Default=Root Folder) - Max 200 listed |
|
Order By Field(s) - Only for Recursive SearchType |
Order by Field(s) for output. You can order by single or multiple fields and use filed suffix asc for ascending and desc for descending order. For example to order by lastModifiedDateTime in descending order use lastModifiedDateTime desc. To order by nested field you can supply lastModifiedBy/user/email asc
Option |
Value |
name |
name |
name desc |
name desc |
size |
size |
size desc |
size desc |
createdDateTime |
createdDateTime |
createdDateTime desc |
createdDateTime desc |
lastModifiedDateTime |
lastModifiedDateTime |
lastModifiedDateTime desc |
lastModifiedDateTime desc |
|
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Search Type (Default=Recursive) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive |
search(q='.xls') |
Non-Recursive (Only for Non-Shared Items) |
children |
Shared With Me (For DriveId 'me') |
sharedWithMe |
|
Search Path (Default=Root Folder) |
|
Order By Field(s) - Only for Recursive SearchType |
Order by Field(s) for output. You can order by single or multiple fields and use filed suffix asc for ascending and desc for descending order. For example to order by lastModifiedDateTime in descending order use lastModifiedDateTime desc. To order by nested field you can supply lastModifiedBy/user/email asc
Option |
Value |
name |
name |
name desc |
name desc |
size |
size |
size desc |
size desc |
createdDateTime |
createdDateTime |
createdDateTime desc |
createdDateTime desc |
lastModifiedDateTime |
lastModifiedDateTime |
lastModifiedDateTime desc |
lastModifiedDateTime desc |
|
List Excel Worksheets of a File (i.e. List Tabs)
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
FileId -OR- Path |
Specify a file Id or Path. Syntax: [items/{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/myfile.xlsx: )
|
Read Excel Worksheet (Auto Detect Range)
Use this endpoint to read data from a Sheet without supplying address range (e.g. A1:D100). This endpoint auto detects address range based on used cells in a sheet. [
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
Excel File Id or Path (e.g. root:/somefolder/myfile.xlsx: ) - Max 200 Listed |
Specify a file Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xlsx: )
|
Sheet Id or Name (e.g. {00xx-xxx-xx123} -OR- Sheet1 ) |
Specify a sheet id or name (e.g. {00000000-0001-0000-0000-000000000000} -OR- Sheet1
|
AutoDetectByValue |
Auto detect cell range based on values only and do not consider cell formatting (for empty values) to detect the range.
Option |
Value |
true |
true |
false |
false |
|
Read Excel Worksheet (From Exact Range)
[
Read more...]
Parameter |
Description |
Drive Id / Shared Folder |
Specify a drive
|
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
Excel File Id or Path (e.g. root:/somefolder/myfile.xlsx: ) - Max 200 Listed |
Specify a file Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xlsx: )
|
Sheet Id or Name (e.g. {00xx-xxx-xx123} -OR- Sheet1 ) |
Specify a sheet id or name (e.g. {00000000-0001-0000-0000-000000000000} -OR- Sheet1
|
Range |
|
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
Specify a File Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xyz: )
|
Create / Update CSV File (Generic)
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
Specify a File Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xyz: )
|
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
Specify a File Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xyz: )
|
Create / Update JSON File (Generic)
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
Specify a File Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xyz: )
|
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
File Id or Path (e.g. root:/somefolder/myfile.xyz: ) - Max 1000 Listed |
Specify a File Id or Path. Syntax: [{file-id}] -OR- [root:/{file-path}:] (Example: root:/folder/subfolder/myfile.xyz: )
|
Convert to new format (e.g. pdf or html) |
Use this option to download file and convert to another format (e.g. download excel file as pdf). See https://learn.microsoft.com/en-us/graph/api/driveitem-get-content-format?view=graph-rest-1.0 for more information. Supported Options are Convert to PDF from [csv, doc, docx, odp, ods, odt, pot, potm, potx, pps, ppsx, ppsxm, ppt, pptm, pptx, rtf, xls, xlsx]. Convert to HTML from supported formats are [loop, fluid, wbtx]
Option |
Value |
Do not convert |
|
Convert To PDF |
pdf |
Convert To HTML (source file must be *.loop, *.fluid, *.wbtx) |
html |
|
Upload a file to the specified path [
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Disk File Path (e.g. c:\somefolder\file.xlsx) |
Specify a disk file path
|
Target File Path / Id (e.g. file.xlsx -OR- myfolder/file.xlsx -OR- 0Zxxxx1234) |
Specify Target file path or exising file Id (for Overwrite scenario)
Option |
Value |
Example #1 (Save On Root) |
file.xlsx |
Example #2 (Save Under Folder) |
myfolder/myfile.xlsx |
Example #3 (Save Under Child Folder) |
myfolder/subfolder/myfile.xlsx |
Example #4 (Overwrite Exising File by ID) |
0Zxxxx1234 |
|
Conflict Behavior (What to do if file exists) |
Option |
Value |
fail |
fail |
replace |
replace |
rename |
rename |
|
Use Source File Created/Modified DateTime rather than Uploaded Time |
Set this property if you like to set file creation/modified date as original file. By default its set to uploaded date/time. This may not be desired if file was actually created on different date than uploaded. If you do not set then current date/time used (Upload time).
Option |
Value |
Use Uploaded Time (Blank) |
|
Use Source File Time |
true |
|
Get Item (File or Folder)
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
File/Folder Id or Path (e.g. root:/myfolder: -OR- root:/myfolder/file.xyz: ) - Max 1000 Listed |
Specify a File/Folder Id or Path (e.g. root:/myfolder/file.xyz: )
|
Delete Item (File or Folder)
[
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Drive Id / Shared Folder |
Specify a drive
|
File/Folder Id or Path (e.g. root:/myfolder: -OR- root:/myfolder/file.xyz: ) - Max 1000 Listed |
Specify an item Id you like to delete
|
Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
Move or rename item (File or Folder)
Move or rename an item (File or Folder) to a different location in the same drive or another drive [
Read more...]
Parameter |
Description |
Operation |
Option |
Value |
Move |
Move |
Rename |
Rename |
|
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Source Drive Id |
Specify a drive
|
Source File or Folder Id you like to move or rename |
Specify an item Id you like to move or rename
|
Target Drive Id (Select if different than source DriveId) |
Specify a drive
|
Target Folder Id (e.g. 01R65Qxxxxxxx123 -OR- root) - Max 200 items listed |
Specify a target folder where item should move after move or rename operation. If you are doing inplace rename and not doing move after rename then keep it blank
|
Target Item Name (Optional for Move - e.g. Myfile.pdf or MyFolder) |
Specify target item name you like to give after move or rename operation. If its move operation and you like to keep same item name as source then keep this blank.
|
Source Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Source Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
Target Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
|
Target Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
Prefer Async Operation (Enable if you get error) |
Option |
Value |
None |
|
Async (Needed for Large files, Cross Site) |
respond-async |
|
Copy item (File or Folder)
Copy an item (File or Folder) to a different location in the same drive or another drive (e.g. different User's drive) [
Read more...]
Parameter |
Description |
Default Group or User Id (additional Scopes needed to list - If fails enter manually) |
To list all users and groups from your organizations you need additional scopes. See connection UI - Choose User.Read.All and Group.Read.All Scopes and regenerate token. You can manually type value too if you know Group or User Id. Format is /users/{id} OR /groups/{id}
Option |
Value |
My self (Not Valid for Application Credentials) |
|
For any group |
/groups/ENTER-GROUP-EMAIL-OR-ID |
For any user |
/users/ENTER-USER-EMAIL-OR-ID |
|
Source Drive Id |
Specify a drive
|
Source File or Folder Id you like to copy |
Specify an item Id you like to copy
|
Target DriveId (Optional - Select if different than source DriveId) |
Specify a drive
|
Target Folder Id (e.g. 01R65QTTRxxxxx -OR- root) |
Specify a target folder where item should be copied
|
Target Item Name (Optional) |
Specify target item name you like to give after copy operation. Keep it blank if you like to keep same name as source.
|
Source Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
Recursive *.xlsx Files - For User Creds Only |
search(q='.xls') |
Recursive *.csv Files - For User Creds Only |
search(q='.csv') |
Recursive *.json Files - For User Creds Only |
search(q='.json') |
Recursive Custom Extension Files - For User Creds Only |
search(q='.myext') |
|
Source Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
Target Search Type (For UI Only - Default=Recursive - i.e. Blank) |
If you do not wish to see files from child folders then use Non-Recursive option. Recursive option has certain limitations (e.g. Only indexed files will appear so it takes time to show up in the list). If you do not see some files listed in the output then use Non-Recursive option (must specify Parent Folder Path in SearchPath property).
Option |
Value |
Recursive All Files (Keep Blank) - For User Creds Only |
|
Non-Recursive / Children - All Files |
children |
|
Target Search Folder (For UI Only - Helps to narrow down File Selection DropDown) - Max 200 Listed |
This parameter is only for UI. Select this to narrow down File List Dropdown. Select Search Folder first and then select File List dropdown to see new File list (found under this selected folder).
|
Prefer Async Operation (Enable if you get error) |
Option |
Value |
None |
|
Async (Needed for Large files, Cross Site) |
respond-async |
|
Conflict Behavior (What to do if file exists at target location) |
Default ConflictBehavior is fail if file exists. The conflictBehavior is not supported for OneDrive Consumer. Also it will not fail if TargetItemName same as source file name
Option |
Value |
fail |
fail |
replace |
replace |
rename |
rename |
|
This is generic endpoint. Use this endpoint when some actions are not implemented by connector. Just enter partial URL (Required), Body, Method, Header etc. Most parameters are optional except URL. [
Read more...]
Parameter |
Description |
Url |
API URL goes here. You can enter full URL or Partial URL relative to Base URL. If it is full URL then domain name must be part of ServiceURL or part of TrustedDomains
|
Body |
Request Body content goes here
|
IsMultiPart |
Set this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value)
==== Raw Upload (Content-Type: application/octet-stream) =====
To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip )
==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) =====
To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored.
If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type.
See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored).
file1=@c:\data\Myfile1.txt
file2=@c:\data\Myfile2.json
file2.Content-Type=application/json
SomeField1=aaaaaaa
SomeField1.Content-Type=text/plain
SomeField2=12345
SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab
SomeFieldStartingWithAtSign=\@MyTwitterHandle
|
Filter |
Enter filter to extract array from response. Example: $.rows[*] --OR-- $.customers[*].orders[*]. Check your response document and find out hierarchy you like to extract
Option |
Value |
No filter |
|
Example1 |
$.store.books[*] |
Example2 (Sections Under Books) |
$.store.books[*].sections[*] |
Example3 (Equals) |
$.store.books[?(@author=='sam')] |
Example4 (Equals - Any Section) |
$..[?(@author=='sam')] |
Example5 (Not Equals - Any Section) |
$..[?(@author!='sam')] |
Example6 (Number less than) |
$.store.books[?(@.price<10)]
Example7 (Regular Expression - Contains Pattern)=$.store.books[?(@author=~ /sam|bob/ )] |
Example8 (Regular Expression - Does Not Contain Pattern) |
$.store.books[?(@author=~ /^((?!sam|bob).)*$/ )] |
Example9 (Regular Expression - Exact Pattern Match) |
$.store.books[?(@author=~ /^sam|bob$/ )] |
Example10 (Regular Expression - Starts With) |
$.store.books[?(@author=~ /^sam/ )] |
Example11 (Regular Expression - Ends With) |
$.store.books[?(@author=~ /sam$/ )] |
Example12 (Between) |
$.store.employees[?( @.hiredate>'2015-01-01' && @.hiredate<'2015-01-04' )] |
|
Headers |
Headers for Request. To enter multiple headers use double pipe or new line after each {header-name}:{value} pair
|
Generic Request (Bulk Write)
This is a generic endpoint for bulk write purpose. Use this endpoint when some actions are not implemented by connector. Just enter partial URL (Required), Body, Method, Header etc. Most parameters are optional except URL. [
Read more...]
Parameter |
Description |
Url |
API URL goes here. You can enter full URL or Partial URL relative to Base URL. If it is full URL then domain name must be part of ServiceURL or part of TrustedDomains
|
IsMultiPart |
Set this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value)
==== Raw Upload (Content-Type: application/octet-stream) =====
To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g. @c:\data\myfile.zip )
==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) =====
To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored.
If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type.
See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored).
file1=@c:\data\Myfile1.txt
file2=@c:\data\Myfile2.json
file2.Content-Type=application/json
SomeField1=aaaaaaa
SomeField1.Content-Type=text/plain
SomeField2=12345
SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab
SomeFieldStartingWithAtSign=\@MyTwitterHandle
|
Filter |
Enter filter to extract array from response. Example: $.rows[*] --OR-- $.customers[*].orders[*]. Check your response document and find out hierarchy you like to extract
|
Headers |
Headers for Request. To enter multiple headers use double pipe (||) or new line after each {header-name}:{value} pair
|
Conclusion
In this article we discussed how to connect to OneDrive in SSIS and integrate data without any coding. Click here to Download OneDrive Connector for SSIS and try yourself see how easy it is. If you still have any question(s) then ask here or simply click on live chat icon below and ask our expert (see bottom-right corner of this page).
Download OneDrive Connector for SSIS
Documentation
More integrations
Other application integration scenarios for OneDrive
Other connectors for SSIS
Download OneDrive Connector for SSIS
Documentation
Common Searches:
How to connect OneDrive in SSIS?
How to get OneDrive data in SSIS?
How to read OneDrive data in SSIS?
How to load OneDrive data in SSIS?
How to import OneDrive data in SSIS?
How to pull OneDrive data in SSIS?
How to push data to OneDrive in SSIS?
How to write data to OneDrive in SSIS?
How to POST data to OneDrive in SSIS?
Call OneDrive API in SSIS
Consume OneDrive API in SSIS
OneDrive SSIS Automate
OneDrive SSIS Integration
Integration OneDrive in SSIS
Consume real-time OneDrive data in SSIS
Consume real-time OneDrive API data in SSIS
OneDrive ODBC Driver | ODBC Driver for OneDrive | ODBC OneDrive Driver | SSIS OneDrive Source | SSIS OneDrive Destination
Connect OneDrive in SSIS
Load OneDrive in SSIS
Load OneDrive data in SSIS
Read OneDrive data in SSIS
OneDrive API Call in SSIS