SSIS tutorial: How to download files from an SFTP server and Upload them to Google Drive using SSIS

[daniel]

Introduction

In this tutorial, we show how to automate file transfers from an SFTP server to Google Drive using SQL Server Integration Services (SSIS). This is a common scenario when receiving data from external partners via SFTP and needing to archive or distribute it using Google Drive.

We will use the ZappySys SSIS PowerPack, which includes components such as the SFTP Task, Advanced File System Task, and API Source, to streamline this process without writing code.

Prerequisites

1. Download and install the ZappySys SSIS PowerPack
2. A Google Drive account
3. A working SFTP server with credentials

Steps

Step 1: Create or 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.
   

   

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.
   

   

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.
   

   
   Desktop App, and create it to get your Client ID and Client Secret

Step 2: Download the files from the SFTP server

1. Add a ZappySys SFTP Task in the SSIS Control Flow

2. Configure the connection with your SFTP server

3. Choose the action Download FTP server file(s) to local directory

4. Enter the source path from the SFTP server. You can use a wildcard to get more than one file (e.g., /JSONs/file*.json).

5. Enter the destination folder on your local machine (e.g., D:\Zappysys\download file test).

6. Set the overwrite option to OverwriteAll

7. Check Create folder if missing if needed
   

   

Step 3: Get the file path and name into variables

1. Create two string variables: User::FilePath for the file path, and User::FileName for the file name. Also, create an object variable User::FileList to hold the list of files.

2. Drag and drop the Advanced File System Task into the Control Flow and connect it to the SFTP task.

3. Open the Advanced File System Task and select Get File List as ADO.net DataTable as the action type.

4. Set the source path to the folder where the files were downloaded, for example:
   D:\Zappysys\download file test\*.*

5. Assign the result to the object variable User::FileList.

6. Click OK to save the task configuration.
   

   

7. Drag a Foreach Loop Container and connect it to the Advanced File System Task.

8. Double-click the Foreach Loop Container and go to the Collection tab.

9. Set Enumerator to Foreach ADO Enumerator.

10. Set the source variable to User::FileList.
    

    

11. Go to the Variable Mappings tab.

12. Map Index 0 to User::FilePath.

13. Map Index 1 to User::FileName.

14. Click OK to save the loop configuration.
    

    

Step 4: Upload the file to Google Drive

1. Inside the Foreach Loop Container, drag a Data Flow Task.

2. Inside the Data Flow, add a ZS API Source component.

3. Create a new connection and select the Google Drive connector from the list.

4. In the connection manager, enter your Client ID, Client Secret, and the required scopes (e.g., https://www.googleapis.com/auth/drive).

5. Click Generate Token, then click Test Connection to validate.

6. Click OK to save the connection.
   

   

7. In the API Source component, choose the endpoint Upload a file.

8. Enter the variable User::FilePath for Local Filepath and User::FileName for Target Filename. Use the Raw Edit option to insert the variable expressions. Ensure the variables are properly URL-encoded.

9. Select the destination folder. You can enter the folder ID or use root to place the file in the leading Google Drive directory.

10. Click Preview to verify the request setup. Ensure that the variables have valid values during preview.

11. Click OK to save the API Source configuration.
    

    

12. Add a destination component (e.g., Trash Destination) and connect it to the API Source to complete the data flow.

13. Run the package. You should see the uploaded file appear in your Google Drive.
    

    

Conclusion

With the help of ZappySys SFTP Task, Advanced File System Task, Foreach loop, and API Source, you can build a low-code SSIS solution that transfers files from an SFTP server directly into Google Drive. This method helps automate data pipelines, improves reliability, and eliminates the need for manual intervention.

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

References

• API connector - Google Drive
• API connector - Google Drive documentation
• SFTP Task (Secure FTP, FTPS)
• SFTP Task (Secure FTP, FTPS) documentation
• Advanced SSIS File System Task
• Advanced SSIS File System Task documentation
• Blog articles
• SSIS PowerPack

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.