How to connect to Google Drive using Visual Basic or API source

SSIS PowerPack API Connectors
, ,
1

Introduction

Integrating Google Drive with SQL Server Integration Services (SSIS) enables you to automate workflows that involve cloud-based files, such as sheets and documents. Since SSIS does not include a native connector for Google Drive, this article presents two methods for achieving integration: using Visual Basic in a Script Task and utilizing the ZappySys SSIS API Source.

Prerequisites

  • A Google Drive account.
  • Download and install the ZappySys SSIS PowerPack.
  • Optional: Basic knowledge of SSIS Script Task (if using the VB.NET method).

Step 1: Create/Select a Project in the Google API Console

  1. Navigate to the Google API Console.

  2. Click on the Project Dropdown at the top bar and either select an existing project or create a new one by clicking CREATE PROJECT.

    Create Project
    Create Project744×230 20.9 KB

  3. Once the project is set, click ENABLE APIS AND SERVICES.

  4. Enable both Sheets API and Drive API by searching for them and clicking ENABLE.

    Enable Google Drive API
    Enable Google Drive API431×231 13.4 KB

  5. Return to the main screen and click on the OAuth Consent Screen tab. Provide the necessary details and save.

  6. Move to the Credentials tab.

  7. Click CREATE CREDENTIALS in the top bar, choose OAuth Client ID, select Desktop App as the Application Type, and click Create to obtain your Client ID and Secret.

    Create Credentials
    Create Credentials871×334 38.8 KB

Step 2: Connect Using Visual Basic and Google Drive API (Option 1)

If you want to use Zappysys ODBC with Visual Basic, this article can help you in that case.

Install Required Packages

In your SSIS project:

  1. Open Manage NuGet Packages.
  2. Install the following package:
Install-Package Google.Apis.Drive.v3

Script Task Setup

  1. Drag a Script Task into your Control Flow.
  2. Set the script language to Visual Basic.
  3. Add any required SSIS variables to ReadOnlyVariables or ReadWriteVariables.
  4. Open the Script Editor and import the following namespaces:
Imports Google.Apis.Auth.OAuth2
Imports Google.Apis.Drive.v3
Imports Google.Apis.Services
Imports Google.Apis.Util.Store

Authenticate and Connect

Replace the path with the location of your credentials JSON file:

Dim credential As UserCredential
Using stream = New FileStream("C:\path\to\credentials.json", FileMode.Open, FileAccess.Read)
    credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
        GoogleClientSecrets.Load(stream).Secrets,
        {DriveService.Scope.Drive},
        "user", CancellationToken.None,
        New FileDataStore("Drive.Auth.Store", True)
    ).Result
End Using

Dim service As New DriveService(New BaseClientService.Initializer() With {
    .HttpClientInitializer = credential,
    .ApplicationName = "SSIS Google Drive Integration"
})

List Google Sheets

Dim request = service.Files.List()
request.Q = "mimeType='application/vnd.google-apps.spreadsheet'"
request.Fields = "files(id, name)"
Dim result = request.Execute()

For Each file In result.Files
    Console.WriteLine("File: " & file.Name & " - ID: " & file.Id)
Next

Upload a File

Dim metadata As New Google.Apis.Drive.v3.Data.File() With {.Name = "example.txt"}
Dim filepath As String = "C:\path\to\your\file.txt"
Dim upload = service.Files.Create(metadata, File.OpenRead(filepath), "text/plain")
upload.Fields = "id"
upload.Upload()

Handle Errors

Try
    ' Authentication and file operations
Catch ex As Exception
    Dts.Events.FireError(0, "Google Drive Script", ex.Message, "", 0)
End Try

Step 3: Connect Using ZappySys API Source (Option 2)

For an easier and faster alternative, use ZappySys API Source to connect to Google Drive with minimal coding.

Steps

  1. Drag a Data Flow Task to your SSIS package.

  2. Inside the Data Flow, add a ZappySys API Source.

  3. Configure a New API Connection:

    • Select Google Drive Connector
    • Enter Client ID, Client Secret, and required scopes (e.g., https://www.googleapis.com/auth/drive)

  4. Click Generate Token, then Test Connection.

  5. Click OK to save.

    Google Drive connector
    Google Drive connector868×749 49.2 KB

  6. Choose the desired endpoint or Google Drive folder and configure output columns.

  7. Preview the data and press OK to save the configuration.

    ZappySys API Connection Configuration
    ZappySys API Connection Configuration912×651 60.5 KB

  8. Drag and drop any destination component and connect to the API source, then run the packs,ge and you will see the result.

    Final result
    Final result332×260 10.6 KB

Conclusion

There are two reliable methods to integrate Google Drive into your SSIS packages:

  • Use the Visual Basic + Google API method for complete control and customization.
  • Use the ZappySys API Source for a visual, low-code experience that simplifies OAuth, token handling, and endpoint selection.

Visit our official page to explore more SSIS components, automation features, and real-time data integration tools included in ZappySys SSIS PowerPack.

References

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.