Calling Gmail REST API using SSIS – Read Emails

Introduction to get Gmail with REST API

In this example, we will show how to read emails by calling Gmail REST API using SSIS. To illustrate, we will use ZappySys SSIS PowerPack, which includes several tasks to import/export data from multiples sources to multiple destinations like flat files, Azure, AWS, databases, Office files and more.


  1. First, you will need ZappySys SSIS PowerPack installed.
  2. Secondly, you will need SSDT installed.

Getting started

How to get information about Gmail with REST API

  1. First, you will need to create a developer application for Gmail. The following tutorial shows how to create an application for Google Drive, but the steps are similar:
  2. Secondly, go to SSDT and create a new SQL Server Integration Project.
  3. Subsequently, drag and drop a Data Flow task and double-click it:
    Using Data flow in SSIS

    Drag and drop Data Flow

  4. Also, in the connection manager create a new ZS-OAuth connection. Select Google as the OAuth provider. In scopes, add the following:
    In addition, press generate token and add your credentials if necessary:

    OAuth credentials

    OAuth credentials to connect to Gmail


  5. To illustrate, the following example shows how to get the Gmail messages from an account. Check the option use credentials and select the OAuth connection just created. To get your current email messages, you can use the following URL:

    The filter used is the following:

    The result displayed shows the ids of the messages. You can see the id of the message in Gmail in the URL when you click a message:

    Get Gmail messages with REST API

    JSON Source Rest API


  6. If you want to check to information of a specific message you can use the following URL.

    Where 1632106341364d6f is the message id. The filter used will be the following:

    Data extracted from Gmail

    Gmail message information

    The query shows the snippet (body of the message), the history id, size estimated and more.

  7. After this, we can use the following filter:

    This will show the sender, the receiver, subject, references, message ID and more information:

    To from IP information

    Information about Gmail

How to get label information in Gmail with REST API

  1. First, you can use the following URL to get the Gmail labels:
  2. Also, the information displayed is the following:
    Gmail labels

    Labels in Gmail

  3. Finally, the labels are the same than in Gmail:
    Gmail labels used

    Gmails labels, categories

How to get threads information in Gmail with REST API

  1. Finally, you can also get the thread information using the following URL:
    The filter used will be the following:
  2. The information displayed will be the following:
    Threads in Gmail

    Get Threads from Gmail

  3. If you press the Preview button, you will see the following information:
    Gmail information with REST

    Thread information using Gmail

How to export the data from Gmail to a CSV file

  1. First, to export the data, we will use the ZS Trash Destination:
    Export data to a file

    Export data from Gmail to a CSV file

  2. Finally, in the Trash Destination specify the path to store the data from Gmail:
    Gmail information stored to get Gmail with REST API

    CSV destination to store Gmail info


To conclude, we can say that ZappySys SSIS PowerPack is a powerful tool where you can get information from Gmail. This information can be exported to Excel, SQL Server, XML, JSON or any other format using SSIS.  Download FREE Trial of SSIS PowerPack to find out more use cases like this.


For more information about Gmail, you can refer to these links:

Posted in REST API Integration, SSIS JSON Source (File/REST), SSIS PowerPack and tagged , , .