Box API Connector

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

Overview

Etlworks Box API connector allows fast, real-time access to Box. The connector works just like any other database connector.

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

When to use Box connector

Use this connector to create Flows that extract data from and load data into Box. 

Prerequisites

Create a Private Box App

 You can follow the procedure below to register an app and obtain the credentials such as the ClientId, ClientSecret, etc.:

1. Log in to your Developer Console from your Box enterprise account. Alternatively, you may sign up for a developer account.
2. Follow official Box documentation steps to create App. 
   • During App creation make sure to generate keypair. After you click on Generate a Public/Private Keypair button to have Box generate a keypair, it will also trigger the download of a JSON configuration file that contains all values necessary to create connection.
   • In order to get Private Key file you need to create new text file (name it something like box.pem) and copy privateKey value from the JSON configuration file into it. Replace all
     \n characters with new line and save file.
3. To authorize your new custom App to access your Box account please follow steps from official Box documentation.

Enable Box API connector for your Etlworks account

Contact support@etlworks.com to enable the connector.

Create a Connection

Step 1. In the Connections window, click +, and type in box.

Step 2. Enter Connection parameters.

Connection parameters

• Client ID: The client ID assigned when you register your application.
• Client Secret: The client secret assigned when you register your application.
• Private Key File: Upload private key that should be used to sign JWT tokens.
• Private Key Password: Optional password for the Private Key File.
• Public Key ID: The ID of your public key in your app settings.
• Subject type: Choose "enterprise" or "user" depending on the Application Access Value you selected in your app settings. The default value of this connection property is "enterprise".
• Subject ID: The enterprise Id if your subject type is set to "enterprise" or your app user Id if your subject type is set to "user".

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

Work with Box

Box data model

The Box models entities in the Box API as Tables, Views, and Stored Procedures. 

Read about Box 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 retrieval of the data 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 back a dataset that contains the collection of tuples that constitute the response.

Read about available stored procedures.  

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

EXEC EmptyTrash AsUserId = 'id'

Extract data from Box

Extracting data from Box is similar to extracting data from the relational database.

Step 1. Create Box Connection which will be used as a source (FROM).

Step 2. Create a destination Connection, for example, a Connection to the relational database, and if needed, a Format (Format is not needed if the destination is a database or well-known API).

Step 3. Create a Flow where the source is a database and the destination is a Connection created in step 2, for example, a relational database.

Step 4. Add a new source-to-destination transformation.

Step 5. Select Box Connection created in step 1 as a source Connection and select the data object you are extracting data from.

Step 6. Select TO Connection, Format (if needed), and object (for example database table) to load data into.

Step 7. Click MAPPING and optionally enter Source Query (you don't need a query if you are extracting data from the Box data object unconditionally).

Step 8. Optionally define the per-field Mapping.

Step 9. Add more transformations if needed.

Load data in Box

Loading data in Box is similar to loading data into a relational database.

Step 1. Create a source Connection and a Format (if needed).

Step 2. Create a destination Box Connection.

Step 3. Create a Flow where the destination is a database.

Step 4. Add new source-to-destination transformation.

Step 5. Select FROM and TO Connections and objects (also a FROM Format if needed).

Step 6. Optionally define the per-field Mapping.

Step 7. Add more transformations if needed.

Browse data in Box

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

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