Upload Files to Dropbox

Introduction

Boomerang Notification Framework supports several different file and ftp services. This page explains and gives examples of how to use Boomerang Notification Framework and Dropbox’s file sharing services to integrate and automate workflows.

Dropbox is a cloud-based file sharing service that lets users bring photos, docs, and videos anywhere and share them easily.

Each instance of Boomerang may be associated with one Dropbox account. To use the Dropbox Out service in Boomerang you will have to authorize your Boomerang instance to upload and download files of a particular Dropbox account. You will find configuration instructions here.

Why use the Boomerang Dropbox Connector?

Do not concern yourself with learning the Dropbox API, Boomerang is the turnkey solution to Dropbox which takes the interfacing complexity out of the equation.

With Boomerang’s Dropbox connector you can with a few simple SQL statements integrate business processes and data with the Dropbox popular file sharing service.

Uploading a File to Dropbox

To schedule a file upload to your Dropbox repository using Boomerang Notification Framework is an easy task and can be accomplished with four simple SQL statements.

1. Create Event (EVENT_MASTER)
2. Create Job (OUT_FILE)
3. Select Content to Upload (EVENT_CONTENT)
4. Release Event (EVENT_MASTER)

The below SQL statement will upload a file “Letter.docx” found in “\\localhost\” to the folder “Photos” in the Dropbox account associated with the Boomerang. [important]Note Dropbox syntax: [dropbox:foldername] entered in column OUT_FILE.Path[/important]

---- Upload Files to Dropbox --------------------
declare @gKey uniqueidentifier, @jKey uniqueidentifier
 
set @gKey = NEWID()
set @jKey = NEWID()
 
insert EVENT_MASTER(gKey, Str1) values (@gKey, 'Upload Files to Dropbox')
insert OUT_FILE (gKey, jKey, [Path], Mode) values (@gKey, @jKey, 'dropbox:Sales', 1) -- Mode 1 = create new or replace existing file
insert EVENT_CONTENT (gKey, jKey, Src_Type, Path, Name) values (@gKey, @jKey, 1, '\\localhost\Letter.docx', 'Letter')
 
update EVENT_MASTER set Status = 0 where gKey = @gKey
 
---- Upload Files to Dropbox --------------------

Result:

Upload a file to the root directory

The following example will upload a file to the Dropbox root directory:

insert OUT_FILE (gKey, jKey, [Path], Mode) values (@gKey, @jKey, 'dropbox:', 1)

Upload a file and create a new directory

The following example will create a new directory and upload our word document to it:

insert OUT_FILE (gKey, jKey, [Path], Mode) values (@gKey, @jKey, 'dropbox:My New Folder Name', 1)

Result:

[important]Please note that the Windows Service Account used by Boomerang needs to have access rights to the folder and files used. In the above example: \\localhost\Letter.docx[/important]

Upload a SQL Server Reporting Services Report

Boomerang Notification Framework supports all rendering options and functions in SQL Server Reporting Services and may be used to add content to your Dropbox account.

If the SSRS report requires parameters the name and value of these should be inserted into CONTENT_PARAMETERS.

To add an SSRS report named “Sales_Trend” in reporting directory “Boomerang_Demo” replace the above EVENT_CONTENT line the following SQL statement:

---- SSRS report --------------------
insert EVENT_CONTENT (gKey, jKey, Src_Type, Path, Format, Name) values (@gKey, @jKey, 2, 'Boomerang_Demo/Sales_Trend', 'PDF', 'Sales_Trend')
---- SSRS report --------------------

Result:

Upload Image or Document from SQL Database

To add a Microsoft Word, Adobe PDF or any other type of file that is supported by Dropbox and that resides in a SQL database add the following SQL statement.

The below code will use an image from the AdventureWorks database and ProductPhoto table.

-- Image Stream -------------------
insert EVENT_CONTENT (gKey, jKey, Src_Type, Stream, Name) select @gKey, @jKey, 0, LargePhoto, 'bike.gif' from AdventureWorks.Production.ProductPhoto where ProductPhotoID = 73
-- Image Stream -------------------

Result:

Upload Multiple Documents

To upload multiple documents and images to a Dropbox simply insert multiple records into EVENT_CONTENT. The following statement will include a SSRS report, a MS Word file and GIF images from a SQL database.

---- Upload Files to Dropbox --------------------
declare @gKey uniqueidentifier, @jKey uniqueidentifier
 
set @gKey = NEWID()
set @jKey = NEWID()
 
insert EVENT_MASTER(gKey, Str1) values (@gKey, 'Upload Files to Dropbox')
insert OUT_FILE (gKey, jKey, [Path], Mode) values (@gKey, @jKey, 'Sales', 1) -- Mode 1 = create new or replace existing file
insert EVENT_CONTENT (gKey, jKey, Src_Type, Path, Name) values (@gKey, @jKey, 1, '\\localhost\Letter.docx', 'Letter')
insert EVENT_CONTENT (gKey, jKey, Src_Type, Path, Format, Name) values (@gKey, @jKey, 2, 'Boomerang_Demo/Sales_Trend', 'PDF', 'Sales_Trend')
insert EVENT_CONTENT (gKey, jKey, Src_Type, Stream, Name) select @gKey, @jKey, 0, LargePhoto, 'bike.gif' from AdventureWorks.Production.ProductPhoto where ProductPhotoID = 73
 
update EVENT_MASTER set Status = 0 where gKey = @gKey
---- Upload Files to Dropbox --------------------

Email In – Dropbox Upload (File Out)

All other services in the Boomerang Notification Framework may be combined with the File Out service. For example the Email In services can be used together with File Out.

The below email is sent to an email address that is being handled by Boomerang. Two PDF documents have been attached and the subject is set to “dropbox”

After an email has been received and parsed by Boomerang the stored procedure sp_On_Email_In is triggered and may be used for additional processing of the incoming emails.

In the following example any file attachments (regardless of size, name or origin) will be uploaded to the Dropbox folder “Email Uploads”

--- Email In - Dropbox Upload (File Out) ----------------------
ALTER procedure [dbo].[sp_On_Email_In]
(
@rKey uniqueidentifier
)
as 
begin
set nocount on 
 
declare @EmailFrom varchar(255), @gKey uniqueidentifier, @jKey uniqueidentifier
 
---- Check if email attachments should be uploaded to dropbox ----------------
select @EmailFrom = From_Email from IN_EMAIL where rKey = @rKey and Subject like '%dropbox%'
if @@ROWCOUNT = 1 
 
set xact_abort on
begin
	----- get email attachments and insert into temp table ---------------------------------
	select iec.Data as EmailAttachments, iec.Name as [FileName], ie.From_Email as EmailFrom
	into #tmp_email_in
	from IN_EMAIL ie 
	inner join IN_EMAIL_CONTENT iec on iec.rKey = ie.rKey 
	where ie.rKey = @rKey
 
	---- set key's for outgoing event -----------------------------------
	set @gKey = NEWID()
	set @jKey = NEWID()	 
 
	------ Upload email attachments to Dropbox account -------------------------------------
	insert EVENT_MASTER(gKey, Str1, Str2) values (@gKey, 'Upload Files to Dropbox from Email In Service', @EmailFrom)
	insert OUT_FILE (gKey, jKey, [Path], Mode) values (@gKey, @jKey, 'dropbox:Email Uploads', 1)
	insert EVENT_CONTENT (gKey, jKey, Src_Type, Stream, Name) select  @gKey, @jKey, 0, EmailAttachments, [FileName] from #tmp_email_in
	update EVENT_MASTER set Status = 0 where gKey = @gKey
 
end
 
return 0
 
end
 
--- Email In - Dropbox Upload (File Out) ----------------------

Result:

Dropbox API Methods/Functions included in Boomerang

The following methods are built into the File Out service of Boomerang. You may not directly access these methods since all communications with Dropbox is handled by Boomerang.

Scenario Information

This application scenario is based on the following technologies: Boomerang 1.7.0, SQL 2008 (64 bit), SSRS and Dropbox.

About Boomerang Notification Framework

Boomerang is a collection of notification services for creating applications that generate and send, as well as receiving notifications. Using the Boomerang notification framework, you can quickly create applications to generate and send notifications to customers, suppliers, partners and employees.

Boomerang is a bolt-on solution for MS-SQL Server. The application interface of Boomerang is entirely based on SQL Server (MS) meaning that each notification service has a corresponding table object that represents the service in question. For example to send and email out you would insert a record into the OUT_EMAIL table. To send a fax you would insert a record into the OUT_FAX table.

Download Sample

Integration with Dropbox code sample