SSIS OneDrive Connector
If you are new with SSIS and ZappySys SSIS PowerPack then check below links to get started.
|
See also
|
Video Tutorial - Read 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 SSIS PowerPack for
OneDrive integration in SSIS - How to configure connection for
OneDrive - How to Read data from
OneDrive - Features about SSIS API Source
- Using
OneDrive Connector in SSIS
Step-By-Step: How to read from OneDrive in SSIS (Export data)
In this section we will learn how to configure and use OneDrive Connector in the API Source to extract data from the OneDrive.
-
Prerequisite: you must have downloaded and installed SSIS PowerPack. Check Getting started section for more information.
-
Begin with opening Visual Studio and creating new SSIS package Project.
-
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 drop 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.
Steps to get OneDrive Credentials
Firstly, login into Azure Portal and there create an OAuth application:
- Go to Azure Portal and login there.
- Then go to Azure Active Directory.
- On the left side click menu item App registrations
- Then proceed with clicking New registration.
- Enter a name for your application.
- Select one of Accounts in this organizational directory only as supported account type to access data in your company.
- In Redirect URI section leave option selected at Web.
- In nearby textbox enter https://login.microsoftonline.com/common/oauth2/nativeclient as Redirect URI or any other valid redirect URL, e.g. https://zappysys.com/oauth
- Use this Redirect URI in Redirect URL grid row.
- Copy Application (client) ID and paste it into API Connection Manager configuration grid in Client ID row.
- Then click on Endpoints link and copy first URL to Authorization URL and second one to Token URL in configuration grid. Usually they look similar to these:
- https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/authorize
- https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/token
- https://login.microsoftonline.com/common/oauth2/v2.0/authorize
- https://login.microsoftonline.com/common/oauth2/v2.0/token
- Close "Endpoints" and create a Client Secret by clicking Certificates & secrets.
- Proceed by clicking New client secret and setting expiration period. Copy the client secret and paste it into configuration grid in Client Secret row.
- Use these scopes in Scopes field: offline_access Files.Read Files.Read.All Files.ReadWrite Files.ReadWrite.All openid profile User.Read email
- Click Generate Token to generate tokens.
- That's it!
User Credentials [OAuth]https://graph.microsoft.com/v1.0Required Parameters Authorization URL Fill in the parameter... Token URL Fill in the parameter... Client ID Fill in the parameter... Scope Fill in the parameter... Optional Parameters Client Secret Fill in the parameter... Refresh Token File Path Fill in the parameter... ReturnUrl Fill in the parameter... -
Select the desired endpoint, change/pass the properties values, and click on Preview Data button to make the API call.
API Source - OneDriveOneDrive 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. In a few clicks we configured the call to OneDrive API using ZappySys OneDrive Connector
Video Tutorial - Write or lookup data to OneDrive using SSIS
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: How to write to OneDrive in SSIS (Import data)
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.
-
Prerequisite: you need to have SSIS ZappySys PowerPack downloaded and installed. Check Getting started section for more information.
-
Firstly, open Visual Studio and create a new SSIS Package Project.
-
Now, drag and drop SSIS Data Flow Task from SSIS Toolbox.
-
Double click on the Data Flow Task to see Data Flow Designer view.
-
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 - OneDriveOneDrive 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.
Steps to get OneDrive Credentials
Firstly, login into Azure Portal and there create an OAuth application:
- Go to Azure Portal and login there.
- Then go to Azure Active Directory.
- On the left side click menu item App registrations
- Then proceed with clicking New registration.
- Enter a name for your application.
- Select one of Accounts in this organizational directory only as supported account type to access data in your company.
- In Redirect URI section leave option selected at Web.
- In nearby textbox enter https://login.microsoftonline.com/common/oauth2/nativeclient as Redirect URI or any other valid redirect URL, e.g. https://zappysys.com/oauth
- Use this Redirect URI in Redirect URL grid row.
- Copy Application (client) ID and paste it into API Connection Manager configuration grid in Client ID row.
- Then click on Endpoints link and copy first URL to Authorization URL and second one to Token URL in configuration grid. Usually they look similar to these:
- https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/authorize
- https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/token
- https://login.microsoftonline.com/common/oauth2/v2.0/authorize
- https://login.microsoftonline.com/common/oauth2/v2.0/token
- Close "Endpoints" and create a Client Secret by clicking Certificates & secrets.
- Proceed by clicking New client secret and setting expiration period. Copy the client secret and paste it into configuration grid in Client Secret row.
- Use these scopes in Scopes field: offline_access Files.Read Files.Read.All Files.ReadWrite Files.ReadWrite.All openid profile User.Read email
- Click Generate Token to generate tokens.
- That's it!
User Credentials [OAuth]https://graph.microsoft.com/v1.0Required Parameters Authorization URL Fill in the parameter... Token URL Fill in the parameter... Client ID Fill in the parameter... Scope Fill in the parameter... Optional Parameters Client Secret Fill in the parameter... Refresh Token File Path Fill in the parameter... ReturnUrl Fill in the parameter... -
Select the desired endpoint, change/pass the properties values, and go to the Mappings tab to map the columns.
API Destination - OneDriveOneDrive 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 the desired columns:
API Destination - OneDriveOneDrive 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
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).Other App Integration scenarios for OneDrive
Other API Connectors for SSIS
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 realtime 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