SSIS Components for Gmail

Build 20.0.7656

MailMessages

Create, update, delete, and query Gmail messages.

Note: This schema is defined for all tables. Each table name will be a mailbox folder on the server.

Table Specific Information

Email address fields contained within this table accept the following formats:

  • 'Friendly Name' <address@company.com>
  • address@company.com
The default Gmail table names contain a backslash; some also contain spaces. You can use square brackets to escape the table names for the default Gmail folders. For example:
SELECT * FROM [Gmail/All Mail]

Select

The results of a query to this table conform to the defaults below:
  • The MessageBody and Headers fields are not returned when listing multiple messages.
  • The default number of messages returned is 100. This can be changed by setting either LIMIT or MaxItems. If you wish to return all mail within a mailbox, specify a value of 0 or -1.

List Messages

To list messages within a specific mailbox, specify the mailbox as the table. The emails will then be listed newest to oldest. Alternatively, you can specify a range of message Ids. The available formats for ranges are below:

  • Return only the message with an Id of 10:
    SELECT * FROM [Inbox] WHERE Id = 10 
  • Return all messages from 10 to 20:
    SELECT * FROM [Inbox] WHERE Id BETWEEN 10 AND 20
  • Return messages 1, 3, 5
    SELECT * FROM [Inbox] WHERE Id IN (1,3,5)

Apply Search Criteria

The 本製品 offers several possibilities for searching through email. You can use search criteria from the following sources in the WHERE clause of a SELECT query:

  • Message fields: To search through mail, specify a value for any of the following columns in the WHERE clause of a SELECT query: To, From, BCC, CC, Subject, MessageBody, Flags, Labels, Size, or Date. Complex statements, nested with parentheses, are supported.

    Note that the CONTAINS function requires full words (For example Test, not Tes), just like the search bar from the UI. For example, the query below returns all messages from either test1@email.com or test2@email.com that contain the text 'Test':

    SELECT * FROM MailMessages WHERE CONTAINS(MessageBody, "Test") AND ([From] = test1@email.com OR [From] = test2@email.com) AND Date > '1-1-2012'

  • Attachments: In addition to searching by standard columns, you can also filter by attachment file names and by whether an email has an attachment:
    SELECT * FROM MailMessages WHERE HasAttachment = 'True' AND Attachments = '.txt'
  • IMAP: As an alternative to the above method of creating search criteria, you can use the SearchCriteria pseudo column to search using any valid IMAP search criteria as specified by RFC.

Insert

Insert statement is used to send mail. The Subject, To, and MessageBody fields are required:

INSERT INTO MailMessages (Subject, MessageBody, To) VALUES ('Test Subject','Body Text','address@company.com')
Note that insert doesn't work with OAUTH authentication and the username and password must be specified in this case.

Update

  • To move an email from one mailbox to another, specify the mailbox in the update:
    UPDATE [Inbox] SET Mailbox = 'NewMailboxName' WHERE Id = 'MessageId'
    The Id may consist of a single message number, a range of messages specified by two message numbers separated by ':' (e.g., "1:5"), or individual message numbers separated by ',' (e.g., "1:5,7,10").
  • When moving an email from one mailbox to another, you may not specify any additional updates.
  • To update the flags, specify one or a list of flags in the update:
    UPDATE [Inbox] SET Flags = 'Seen' WHERE Id = 'MessageId'

    The standard values of flags as specified by the IMAP RFCs are : 'Answered' , 'Flagged' , 'Deleted' ,'Seen' and 'Draft'.

    The flags specified in query will override all email's current flags.

Delete

Delete statement is used to permanently delete a mail from its mailbox. The Id fields is required:

Delete FROM [Inbox] WHERE Id = '2'

Additional Notes

  • By default, the max number of messages returned will be 100. This can be changed by setting either LIMIT or MaxItems. If you wish to return all emails within a mailbox, you will need to specify a value of 0 or -1.
  • By default, the number of messages returned per page will be 25. To change this, you can set the ItemsPerPage pseudo column.
  • All message Ids returned are temporary Ids and may change in subsequent requests to the server.

Columns

Name Type ReadOnly References Description
Id [KEY] String True

The identifier of the mail message.

Mailbox String False

The current mailbox that the item is listed in. Updating this value will move the message to a new mailbox.

Subject String False

The subject of the current message.

FullFrom String True

The sender name and email address of the current message.

From String True

The sender email address of the current message.

FullTo String True

A semicolon-separated list of names and email addresses of the recipients.

To String False

A semicolon-separated list of the email addresses of the recipients.

FullCC String True

A semicolon-separated list of names and email addresses of the CCed recipients.

CC String False

A semicolon-separated list of the email addresses of the CCed recipients.

FullBCC String True

A semicolon-separated list of the names and email addresses of the BCCed recipients. This field is only applicable for sent mail.

BCC String False

A semicolon-separated list of the email addresses of the BCCed recipients. This field is only applicable for sent mail.

Date Datetime True

The date and time the current message was sent.

MessageBody String False

The message body. This field can only be set if you specify a single Id in SELECT.

Attachments String False

A semicolon-separated list of the attachment file names (with the path specified if reading from a file) included in the message.

AttachmentData String False

A semicolon-separated list of the base-64-encoded attachment data included in the message. The file name in the Attachments field must also be specified to use this field.

Size Int True

The size in bytes of the current message.

Flags String False

The flags set on the current message.

Labels String False

The labels of the message, separated by spaces. Gmail treats labels as mailboxes.

ThreadId String True

The Gmail thread Id associated with the current message. Several messages may have the same thread Id.

MsgId String True

The Gmail message Id associated with the current message. This is a globally unique Id assigned by Google separate from the IMAP Id/UID.

PartIds String True

The Id of the message part.

PartFilenames String True

The file name of the message part.

PartContentTypes String True

The content type of the message part.

PartSizes String True

The size of the message part.

Headers String True

The message headers for the specified mail message. This field is only available if you specify a single Id in SELECT.

AttachmentHeaders String True

The attachment headers for the specified mail message. This field is only available if you specify a single Id in SELECT.

ContentIds String True

The content id headers for the specified mail message. This field is only available if you specify a single Id in SELECT.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name Type Description
SearchCriteria String

The search criteria to use for the search operation (i.e., SENTSINCE dd-MMM-yyyy). The exact format of the search criteria is specified by the IMAP RFCs. The string consists of one or more search keys and their corresponding values, if any, separated by spaces. For example: 'SINCE 1-Feb-1994 NOT FROM Smith'.

ItemsPerPage String

The maximum number of items to return per page.

デフォルト値は25です。

IsHTML String

This field sets whether the email has an HTML or plain-text encoding.

デフォルト値はTRUEです。

Importance String

The importance of the mail message.

使用できる値は次のとおりです。UNSPECIFIED, LOW, NORMAL, HIGH

デフォルト値はUNSPECIFIEDです。

Priority String

The priority of the mail message.

使用できる値は次のとおりです。UNSPECIFIED, NONURGENT, URGENT, NORMAL

デフォルト値はUNSPECIFIEDです。

Sensitivity String

The sensitivity of the mail message.

使用できる値は次のとおりです。UNSPECIFIED, PERSONAL, PRIVATE, COMPANYCONFIDENTIAL

デフォルト値はUNSPECIFIEDです。

DeliveryNotification String

The email address to send a delivery notification to.

ReadReceipt String

The email address to send a read receipt to.

ShowLabels String

This field sets whether to show labels or not.

デフォルト値はTRUEです。

ShowThreadId String

This field sets whether to show the thread Id or not.

デフォルト値はTRUEです。

ShowMsgId String

This field sets whether to show the unique Gmail Id or not.

デフォルト値はTRUEです。

UIDMode String

If this value is set to true, permanent message Ids are used instead of the default temporary Ids.

デフォルト値はFALSEです。

HasAttachments String

If this value is set to true, only messages with attachments will be listed. If false, all messages will be listed.

DownloadAttachments String

If true, attachments will be downloaded with the messages. Available only when specifying a single message Id or the Include Messages connection property is true. By default organizes attachments in subfolders with the message Id as name.

デフォルト値はFALSEです。

AttachmentFolder String

The folder to download attachments to. If this is set, AttachmentData will not contain data, which will be written to files.

EMLFolder String

The folder where the complete MIME message, which is saved as an EML file, is downloaded to. If multiple messages are selected, subfolders will be created for each message.

Overwrite String

This field sets whether or not to overwrite attachments and EML files.

デフォルト値はFALSEです。

IncludeMessage String

This field sets whether or not to include the message content in the response.

デフォルト値はFALSEです。

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7656