Using the Email Source Component

The Email Source Component is used to receive emails from a server and output them to columns.

General Page

Configure general email settings.

SSIS email source

Connection Manager

The Email Source Component requires an Email connection. The Connection Manager drop-down will show a list of all Email connection managers that are available in your current SSIS package.

Folder Path:

Folder path determines which folder on the server emails will be retrieved from.  This option cannot be used with POP3 server because they do not support folders.  Imap and Exchange Web Services connection managers will default to 'Inbox'.

Include Subfolders:

Determines if subfolders below the root folder should be searched for email.

Only Process Unread Messages

This option will determine if only messages that have not previously been read will be processed.

Read Processed IDs from Variable:

This option is only available with a POP3 server.  Because POP3 messages do not support 'Read' flags message IDs that the component has already processed must loaded before processing.

Mark Unread Messages as Read

This option will determine if messages should be marked as read after they are processed.

Write Processed IDs to Variable:

This option is only available with a POP3 server.  Because POP3 messages do not support 'Read' flags message IDs that the component has processed must saved in a variable.

Batch Size (since v2.0):

This Batch Size option is available for Exchange Web Services protocal connections to allow you to specify how many records you want to retrieve each time.

Search Parameters:

Search parameters can be entered that allow messages retrieved from the server to be filtered.  Each parameter has a Field, Filter Type and Value.

The field can be any column that has been selected on the columns page.

Filter type options depend on the field that has been selected.  String fields support the 'Contains' and 'Does Not Contain' filter type.  Numeric fields support the 'Greater Than', 'Less Than' and 'Equal To' filter types.

Columns Page

On the columns page you will see a list of all the output columns that are available and their types.

SSIS email source columns page

On the top left of the grid, there is a checkbox, which can be used to toggle the selection of all available fields. This is a productive way to check or uncheck all available fields. 

The Columns Page grid consists of:
  • Include Field Checkbox - A checkbox that determines if the field will be available as an output column.
  • Field Name   - Column that will be retrieved from Flat File.
  • Data Type - The data type of this field. 
Filter
The output columns that are visible can be filtered by entering text in the Filter text box.
Hide Unselected Fields
When the Hide Unselected Fields checkbox is checked unselected output columns will be hidden.
Hide Used Fields
When the Hide Selected Fields checkbox is checked selected columns will be hidden.
Add Button
Clicking on the Add button opens the 'Add Email Header' dialog and allows columns to be added that will map to headers in the email.
Delete Button
The delete button will be enabled if a header column is selected.  Clicking this button will delete the column.

The following email columns are available:

  • Body - The body of the email.
  • BodyHTML - The bodyHTML of the email.
  • Cc - A list of email addresses that were Cced on the email.
  • DateSent - The date the email was sent.
  • From - The email address that the email was sent from.
  • MessageID - The unique ID of the email message.  This corresponds to the MessageID column in secondary output.
  • Priority - The priority of the Email.
  • Size - The size of the email in bytes.
  • Subject - The subject of the email.
  • To - A list of the email address that were in the To field of this email.

Add Email Headers Dialog

If the Add button is clicked the Add Email Headers dialog will open.  This will allow any email header to be mapped to a column.  If the header appears multiple times in an email message all the values will be included in the output column as a JSON list.

SSIS email source add header

Column Name - A column name is automatically generated based on the header name that is entered.

Header Name - The name of the email header.

Data Type - Only the string data type is supported for email headers.

Length - The length of the header column.  Entering a length of 0 will create an ntext column with unlimited length.

Outputs

The Email component supports 2 outputs.  

Primary Output - Primary output will consist of the columns available on the columns page.  Secondary output will include any attachments that correspond to messages retrieved for primary output.  

Second Output - Secondary output is used to output any attachments that are associated with the messages in primary input.  It has 3 columns:

  • MessageID - This corresponds to the MessageID of the message the attachments are associated with.  
  • FileName - This column contains the name of the attachment file.
  • AttachmentContent - This column contains the content of the attachment file as a DT_IMAGE.