How to upload files on Google Drive using SSIS

Introduction

In today’s data-driven world, the ability to seamlessly integrate and transfer files between different platforms is crucial for efficient business operations. If you’re a user of SQL Server Integration Services (SSIS) and looking to integrate Google Drive into your data workflow, you’re in luck. ZappySys SSIS PowerPack offers a powerful Google Drive Connector that makes uploading files to Google Drive a breeze. In this guide, we’ll walk you through the process of uploading files to Google Drive using SSIS and the ZappySys SSIS PowerPack Google Drive Connector.

Upload files to Google Drive with SSIS:

In this section, we will learn how to configure and use Google Drive Connector in the API Source to upload files on Google Drive.

1. Begin with opening Visual Studio and Creating a New Project.
2. Select Integration Service Project and in the new project window set the appropriate name and location for the project. Click OK.
3. In the new SSIS project screen you will find the following:
   1. SSIS ToolBox on the left sidebar
   2. Solution Explorer and Property Window on the right bar
   3. Control flow, data flow, event Handlers, Package Explorer in tab windows
   4. Connection Manager Window at the bottom
   

   SSIS Toolbox – ZappySys Tasks

   NOTE: If you don’t see ZappySys SSIS PowerPack Task or Components in the SSIS Toolbox, please refer to this help link.
4. Now, Drag and Drop the SSIS Data Flow Task from the SSIS Toolbox. Double-click on the Data Flow Task to see the Data Flow Designer.
   
5. 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:
   
6. Select New Connection to create a new connection:
   

Configuring the Google Drive Connector

1. Use a preinstalled Google Drive Connector from the Popular Connector List or press the Search Online radio button to download Google Drive Connector. Once downloaded simply use it in the configuration:
   
2. 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.
3. After configuring all the required properties in the API Connection Manager, please click on the “Test Connection” button to confirm that the connection is established successfully.
   

   Method1: Connect to Google Drive using User Account [OAuth]

   You may press a link Steps to Configure which will help set certain parameters.

   

   SSIS API Source – Generate Token – Google Drive with User Account

   Method2: Connect to Google Drive using Service Account [OAuth]

   You may press a link Steps to Configure which will help set certain parameters.

   

   SSIS API Source – Generate Token – Google Drive with Service Account

How to upload a local file to Google Drive

In the API Source Component, we’ve chosen the API Connection Manager. Next, we need to configure the remaining properties of the component to upload a local file to Google Drive.

1. Choose the Upload a file endpoint from the “Select Table/Endpoint” dropdown menu.
2. To access detailed API documentation for uploading files, you can click on the following link: Help for: Upload File.
3. Please specify the desired filename for the uploaded file by setting the FileName parameter.
4. Specify the desired local file path by setting the DiskFilePath parameter.
5. If you wish to upload the local file to a specific folder in Google Drive, please choose the folder ID from the dropdown menu labeled ParentFolderId. Otherwise, you can skip this parameter as it’s optional.

Please refer to the sample screenshot below.

SSIS API Source – Google Drive – Upload Single File

Once you have configured all the mentioned properties above, please click on the “Preview Data” button to initiate the API call. With just a few clicks, we have successfully configured the call to Google Drive using the ZappySys Google Drive Connector for uploading a local file to Google Drive.

How to upload multiple files to Google Drive

In the API Source Component, we’ve selected the API Connection Manager. Next, we need to configure the remaining properties of the component to upload multiple files to Google Drive. Additionally, we should utilize a Foreach Loop Container to iterate through each file one by one in a loop.

1. Create 3 SSIS variables as described below.
   

   Variable Name	Datatype	Value
   varFileLists	Object	System.Object
   varFileName	String	fake.extension
   varFullFilePath	String	C:/fake/fake.extension

2. Go to Control Flow, From the SSIS toolbox drag and drop ZS Advanced File System Task on the control flow designer surface, and double click on it to edit it:
   
3. Select the “Get file list as ADO.NET Data Table” action from the Action drop-down menu. Set the file path in the Path textbox. Choose the variable (Object type) where you want to store the file list, and then click the OK button to save the settings.
   

   SSIS Advanced File System Task – Get file list as ADO.net Data Table

4. Now, drag and drop the “Foreach Loop Container” from the SSIS Toolbox and connect it with the “ZS Advanced File System Task.” Double-click on the “Foreach Loop Container” to edit its properties.
   

   SSIS Foreach Loop Container – Drag & Drop

5. In the Foreach Loop Editor’s “Collection” tab, choose the Enumerator as “Foreach ADO Enumerator.” In the “ADO object source variable” field, select your SSIS object-typed variable. Select the “Rows in the first table” radio option.
   

   SSIS Foreach Loop Editor – Collection – Select Variable & Enumeration mode

6. In the Foreach Loop Editor’s “Variable Mappings” tab, select the variables that you want to map to the values from the collection, and then click the “OK” button to save your selections.
   

   SSIS Foreach Loop Editor – Variable Mappings

7. Inside the Foreach Loop Container, drag and drop the Data Flow Task, and then rename it according to your preference.
   

   SSIS Data Flow Task – Drag & Drop inside Foreach Loop Container

8. Double-click on the Data Flow Task to see the 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:
   
9. Double-click on the API Source component to configure it for uploading multiple files to Google Drive.
   1. Choose the Upload a file endpoint from the “Select Table/Endpoint” dropdown menu.
   2. To access detailed API documentation for uploading files, you can click on the following link: Help for: Upload File.
   3. Please specify the desired SSIS Variable [varFileName] which contains file name for the uploaded file by setting the FileName parameter.
   4. To specify the desired local SSIS variable [varFullFilePath], which holds the file path, set the DiskFilePath parameter.
      When configuring the SSIS variable directly in the Raw Edit, JSON may become invalid due to the requirement of slashes within the local file path. To address this, we use a JSONENC Placeholder Function for proper escaping.
      <<{{User::varUserFullFilePath}},FUN_JSONENC>>
   5. If you wish to upload the local file to a specific folder in Google Drive, please choose the folder ID from the dropdown menu labeled ParentFolderId. Otherwise, you can skip this parameter as it’s optional.

   Please refer to the sample screenshot below.

   

   SSIS API Source – Google Drive – Upload Multiple files configuration

   To configure dynamic filenames and file paths, you can set variables. Click on “Raw Edit,” and in the “Edit Parameters” popup, you can use SSIS variables, as shown in the image below.

   Once you have configured all the mentioned properties above, please click on the “Preview Data” button to initiate the API call. With just a few clicks, we have successfully configured the call to Google Drive using the ZappySys Google Drive Connector for uploading multiple files to Google Drive.

Conclusion

In this blog, we learned how to connect with Google Drive using SSIS API Connection Manager and upload single or multiple files to Google Drive using a combination of SSIS components, including the SSIS API Source component, SSIS ZS Advanced File System Task, Foreach Loop Container, and SSIS variables.