Loading files directly from Box or Dropbox API

Note: This was written and tested on SQL Server 2016

Download the zip file attached and follow the instructions in README.txt

Contents copied below

You need to create an app in box/dropbox to authenticate with the API.

Give your application a name
Set the redirect_uri to 'https://www.wherescape.com' or your own website address.
All other settings can be left as default
Save your client_id and client_secret.
Save you application.

Next open up the authentication script for your API (right click - edit)
Enter your client_id and client_secret into the variables near the top of the script.
Save the script and run it.
A login window will pop up, login with your box credentials
After you grant access to your API, the script will print your authentication information on the screen

Take the provided information and save it into the variables in the 'create_table_api_authentication.sql' script
For dropbox, you only need an access token.
For box, you need your client id, client secret and your refresh token
Execute the script to create the authentication table and save your credentials in the database.

This gives you an easy way to secure your credentials.
For example, you can deny rights on this table to everyone except the scheduler user or alter the scripts to save and read information from a secure schema

If saving the table in a secure schema, you will need to modify the load script as well.
Simply replace all occurances of 'api_authentication' with '[your_schema].api_authentication'
In the box API script this will need to be changed in two places.
In the dropbox API scripts this will only need to be changed in one place.

For the box API (not dropbox) - anyone who has select rights should also have update rights as refresh tokens are not reusable.
Each time a refresh token is used, a new one is provided. This solution will automatically update the authentication table with the new refresh token.

Create a RED host script object for both the api file load powershell script and the script_run_powershell_load.bat batch file and copy the contents of them into their respective host scripts

Edit the script script_run_powershell_load and change '***ENTER POWERSHELL RED HOST SCRIPT NAME HERE***' to the name of your powershell script as you have named it in RED.

Download your file from your API - this only needs to be done once
Drag the file into RED from your windows connection and parse it as if you were doing a normal file load
Create your table
Set the load type as script based load
Select script_run_powershell_load as the load script

DROPBOX API ONLY
- Set the full path and file name for your file as it is stored in Dropbox

BOX API ONLY
- leave the file path blank and enter a search query into the filename - i.e. a partial or full file name
- any files which match the query string entered will be returned and loaded into the table