Download Gmail Attachment in SSIS

Introduction

Download Gmail Attachment in SSIS is always a little bit difficult compared to work with a simple text or numeric columns. If you are not an expert in SSIS, doing that the first time may be Challenging. That is why we created this article for you. Doing this is a common request that we frequently receive and that is why we are bringing this article for you.

Gmail is the most popular E-mail service in the world and it can be useful to automate and get valuable information from it. We will connect to Gmail with REST API. For that, if you are not an experienced user in REST API, do not worry. This article is for you. We will provide simple steps for the connection.

Requirements

In order to do it, we will need the following components:

1. SSDT for SSIS must be installed on your computer
2. ZappySys SSIS Powerpack should be installed. This Powerpack Plugin contains the connectors to Gmail using REST API.

Getting Started

Creating the connection

1. First of all, in SSDT, create a new SSIS project
2. In order to create a connection in SSDT, go to the connection manager, and select a new connection.
   

   SSIS New connection

3. Select the ZS OAuth connection. This is a special connection included with the ZS
   SSIS PowerPack installer that we will use to connect to Gmail.
   

   ZappySys OAuth Connection in SSIS

4. In the ZS OAuth connection, select the Google API OAuth Provider and specify the following scope permissions:
   

   SSIS Gmail connection

   https://mail.google.com/ https://www.googleapis.com/auth/gmail.modify https://www.googleapis.com/auth/gmail.readonly https://www.googleapis.com/auth/gmail.addons.current.message.readonly https://www.googleapis.com/auth/gmail.addons.current.message.action

   1 2 3 4 5

   https://mail.google.com/ https://www.googleapis.com/auth/gmail.modify https://www.googleapis.com/auth/gmail.readonly https://www.googleapis.com/auth/gmail.addons.current.message.readonly https://www.googleapis.com/auth/gmail.addons.current.message.action

Creating the package to download Gmail attachment in SSIS

1. 1. First of All, Drag and drop the Data Flow Task from SSIS Toolbox and double click it
      to edit.

1. Secondly, in the Data Flow, we will use the JSON Source to connect to Gmail and get the attachments. JSON Source is a ZappySys component used now only to connect to JSON files, but also to connect to REST API Servers that support JSON. In this case, Gmail REST API can send information in JSON Format. Use the connection created before.
2. Basically, we need to specify the URL of the Gmail attachment. In Gmail, the email attachment is inside the message. Then you need to specify the message-id and the Gmail attachment id like this:
   

   JSON Properties

   https://www.googleapis.com/gmail/v1/users/me/messages/17690e59f9fd76/attachments/ANGjdJ_sEBAH-dvAQQpHUHcsDozPvKs-5O3cr-8E-dCFATCmaml6TyI0t9ixr7sSBtwM6_7ulZHCHmwkwy84eq_3czuJ1nYZ1HI6IxlkI87aLfjnvmmzms9ogfbynLuqRrr3s_Ng2xm8DTYT9q8zlzdCZ0Mwf9Y4M7qx0w8K4O2ZFtDgq4U7BUFdZNA62p-aq_A2loAdyqZ80DewP498lyD1iH_qnnOYP-STI5eWTA

   1

   https://www.googleapis.com/gmail/v1/users/me/messages/17690e59f9fd76/attachments/ANGjdJ_sEBAH-dvAQQpHUHcsDozPvKs-5O3cr-8E-dCFATCmaml6TyI0t9ixr7sSBtwM6_7ulZHCHmwkwy84eq_3czuJ1nYZ1HI6IxlkI87aLfjnvmmzms9ogfbynLuqRrr3s_Ng2xm8DTYT9q8zlzdCZ0Mwf9Y4M7qx0w8K4O2ZFtDgq4U7BUFdZNA62p-aq_A2loAdyqZ80DewP498lyD1iH_qnnOYP-STI5eWTA

    
3. Now in the go-to columns Tab and set the data  DataType as DT_IMAGE  and increase others’ columns DT_WSTR length with 1000 and 1500. We set contentBytes to DT_IMAGE  datatype its like varbinary(MAX)
   

   JSON Source Columns Tab

4. Next, we will use the derived column, to set the path to store the attachment in a local folder. The derived column will be used to set the local path for the attachment. Make sure to specify a valid path and double backslash for the paths because the backslash is a special character. The derived column is a special transformation component used to create new columns using
   SSIS expressions.
   

   SSIS Path for attachment in Gmail

5. Also, we will require the Export column to download into a file. This component is specially used to get images or binary files into the file system.
   

   Export column SSIS Gmail Attachment

6. Finally, we will use the Trash destination to get the attachment and finish the flow. This one is also a ZappySys component very common.
7. Your package should be something like this:
   

   Gmail attachment tasks in SSIS

8. The next step to test it is to run it.
9. If everything is fine, we will be able to see the file in your system.

Conclusion

In conclusion, we learned how to Download Gmail Attachment in SSIS. Basically, you need to use the ZappySys JSON Source to connect to Gmail using REST API, then you need to use the derived column component to specify the path to store the attachment. The next step is to use the Export column component to export the attachment. Finally, the ZS Trash destination is a destination for the attachment. If you liked this article, do not hesitate to continue testing and using ZappySys PowerPack for SSIS.