Email Task

EDIS Online User Guide – Email Tasks

EDIS has simple email tasks to import, move, and delete messages, create and delete folders in the inbox, and schedule meetings. Additionally, EDIS has a simple yet powerful task to send emails. The send email task provides the ability to attach a query results set as an Excel file or text file with ease. You simply just provide the query you want to run with the file name you want it called, and EDIS takes care of the rest in a seamless manner. But what if you are sending a large file? No problem! EDIS can compress the query attachment as a zip file all by setting a simple parameter flag.

Setup Email Account (EWS)
This task sets up an email account to be used with Exchange Web Services (EWS). This account type supports all EDIS email tasks.

Remarks
1) In order to connect to Exchange Web Services and use the corresponding EDIS tasks, you must download and install the Exchange web services API on the host EDIS server, which is available at the following link: Exchange Web Services API Download
2) When a new email account is created, it is by default accessible to all users in the EDIS_Role. To lock to a specific ID, group, or set of ID’s, see section Multi-Tenant Support.

Procedure: SSISDB.EDIS.usp_config_ews_acct

Parameters

Name Description Default Value
@svc_id Service ID to be used to associate the EWS account. None; Required
@user_id The user ID used to login to the email account None; Required
@domain Domain used to login to the email account Null
@password Password used to login to the email account None; Required
@email_acct The email address to login to. None; Required
@exchange_version The version of Microsoft Exchange you are running. For Office 365 accounts, specify “2013”. Acceptable values are “2007_sp1”, “2010”, “2010_sp1”, “2010_sp2”, “2013”, and “2013_sp1” None; Required
@office_365_url The EWS URL to direct to if your account is on Office 365. Null

Example: This example creates a new EWS account that is hosted on Office 365

EXEC EDIS.usp_config_email_acct @svc_id = 'SALES_EWS_TEAM_INBOX'

,@user_id= = 'george@contoso.com'

,@password = '$xr%3!!_ab1'

,@email_acct = 'sales_support@contoso.com'

,@exchange_version = '2013'

,@office_365_url = 'https://outlook.office365.com/EWS/Exchange.asmx'

;

 

Setup Email Account (SMTP)
This task sets up an SMTP email account to be used for sending emails.

Remarks
The SMTP account can only be used with the send email task. To import messages, create/delete folders, and move/delete messages, you need to create an Exchange Web Services (EWS) account.
When a new email account is created, it is by default accessible to all users in the EDIS_Role. To lock to a specific ID, group, or set of ID’s, see section Multi-Tenant Support.

Procedure: SSISDB.EDIS.usp_config_email_acct

Parameters

Name Description Default Value
@svc_id Service ID to be used to send emails. None; Required
@smtp_host The SMTP host address for sending emails None; Required
@port Port used for sending emails None; Required
@user_id User ID to use to login for sending emails. For anonymous connections, leave blank Empty String
@pwd Password associated with user ID to use for sending emails. Leave blank for anonymous connections. Empty String
@sender_email_address The email address you want in the From field for sending email messages. None; Required
@use_ssl Flag indicating if connection requires SSL 0 (False)

Example: This example creates a new email account

EXEC EDIS.usp_config_email_acct @svc_id = 'SALES_SUPPORT_EMAIL'

,@smtp_host = = 'smtp.office365.com'

,@use_ssl = 1

,@sender_email_address = 'sales_support@contoso.com'

,@port = 587

,@user_id = 'sales_admin@contoso.com'

,@pwd = '$xYnRTv_b15R3'

;

 

Send Email Task
This task sends an email

Procedure: SSISDB.EDIS.usp_send_email

Parameters

Name Description Default Value
@svc_id Service ID to be used to send emails. None; Required
@to The email address(es) to send the email to. For multiple addresses, use a semi-colon to separate i.e. “brad@contoso.com; jane@contoso.com” Empty String
@cc Copy recipients (CC) email address(es) to send the email to. For multiple addresses, use a semi-colon to separate i.e. “brad@contoso.com; jane@contoso.com” Empty String
@bcc Blind copy recipients (CC) email address(es) to send the email to. For multiple addresses, use a semi-colon to separate i.e. “brad@contoso.com; jane@contoso.com” Empty String
@subject Email Subject None; Required
@body The email message body. You can provide plain text or HTML. The default is that the send email task assumes the body is HTML. To override and use plain text, see parameter @body_format. None; Required
@body_format Flag indicating the format of the email message. Acceptable values are “HTML” and “TEXT”. HTML
@is_high_pri Flag indicating if the email should be sent as a high-priority message 0 (False)
@file_attachment_path Path to specify if you are attaching a local file to the email. For multiple attachments, use a semi-colon to separate i.e. “C:\temp\sales.txt; C:\temp\inventory.csv” Empty String
@qry If you want to attach a query results set as a file, you specify the query SELECT statement here. Empty String
@qry_attachment_path Temporary path to write the query results out to on local storage. The file name you specify will be the file name of the attachment. Acceptable file extension formats are “xlsx”, “xls”, “txt”, and “csv” Empty String
@col_delim If your query attachment is a text file or CSV file, you can use this field to override the default column delimiter. For text files, the default is a tab, for csv files, the default is a comma Empty String
@compress_qry_attachment Flag indicating if you want to compress (zip) the query attachment. This is helpful when you are sending a text file with large amounts of data, as the compression helps to shrink the attachment size. When you specify this parameter as true (1), the file will get compressed and the new attachment name will end with a “.zip” extension i.e. file “C:\temp\sales.txt” would be renamed to “C:\temp\sales.zip” 0 (False)

Example 1: This example sends a simple email.

EXEC EDIS.usp_send_email @to = 'sarah@contoso.com; paul_b@contoso.com'

,@cc = 'greg@contoso.com'

,@subject = 'Weekly Sales Review Data Notification'

,@body = 'Weekly sales review data has been published and is ready for review'

,@svc_id = 'SALES_SUPPORT_EMAIL'

;

 

Example 2: Sending a high-priority email

EXEC EDIS.usp_send_email @to = 'sarah@contoso.com; paul_b@contoso.com'

,@cc = 'greg@contoso.com'

,@subject = 'Weekly Sales Alert'

,@body = 'The weekly sales data batch process encountered an error and needs to be addressed.'

,@is_high_pri = 1

,@svc_id = 'SALES_SUPPORT_EMAIL'

;

 

Example 3: Emailing a query results set attached as an Excel spreadsheet

EXEC EDIS.usp_send_email @to = 'sarah@contoso.com; paul_b@contoso.com'

,@cc = 'greg@contoso.com'

,@subject = 'Weekly Sales Report'

,@body = 'Attached please find the weekly regional sales report.'

,@qry = '

SELECT RGN_ID, SUM(SALES) AS SALES_AMT

FROM SALES_DB.dbo.RGN_SALES_DATA

WHERE FSCL_WK = 7 AND FSCL_YR = 2016

GROUP BY RGN_ID

'

,@qry_attachment_path = 'C:\temp\rgn_sales.xlsx'

,@svc_id = 'SALES_SUPPORT_EMAIL'

;

 

Example 4: Emailing a query results set attached as a compressed zip file

EXEC EDIS.usp_send_email @to = 'sarah@contoso.com; paul_b@contoso.com'

,@cc = 'greg@contoso.com'

,@subject = 'Weekly Sales Report'

,@body = 'Attached please find the weekly regional sales report.'

,@qry = '

SELECT RGN_ID, SUM(SALES) AS SALES_AMT

FROM SALES_DB.dbo.RGN_SALES_DATA

WHERE FSCL_WK = 7 AND FSCL_YR = 2016

GROUP BY RGN_ID

'

,@qry_attachment_path = 'C:\temp\rgn_sales.txt'

,@compress_qry_attachment = 1

,@svc_id = 'SALES_SUPPORT_EMAIL'

;

 

Example 5: Emailing with a local file attached

EXEC EDIS.usp_send_email @to = 'sarah@contoso.com; paul_b@contoso.com'

,@cc = 'greg@contoso.com'

,@subject = 'Weekly Sales Report'

,@body = 'Attached please find the weekly regional sales report.'

,@file_attachment_path = 'C:\temp\sales.xlsx'

,@svc_id = 'SALES_SUPPORT_EMAIL'

;

 

Import Email Messages Task
This task imports emails from a Microsoft Exchange Web Service Account

Procedure: SSISDB.EDIS.usp_email_import_messages

Parameters

Name Description Default Value
@svc_id Service ID to be used to import the email messages. Currently, only Exchange Web Services (EWS) ID’s are supported for this task. None; Required
@folder_path The path of the email folder to import. The root path is “Inbox”. To access sub folders, separate the folder paths with a forward slash i.e. to access a sub folder named “My Tasks” under the inbox, you would use “Inbox/My Tasks” for this parameter value. None; Required
@dest_tbl_nm Name of the destination table to import the email messages to. This table will get dropped and recreated every time. None; Required
@subject_filter This parameter filters the email messages based on the subject and what is supplied. The text supplied for this filter is not case-sensitive. Empty String
@start_dt Filters for messages that only begin at the date supplied. Null
@end_dt Filters for messages that end by the date supplied. Null
@include_attachments Flag indicating if you want to download attachment content. 0 (False)
@attachment_name_filter Filter to only import attachments that have a name corresponding to the supplied value. Null
@body_format Indicates the format for the email body message to import. You can specify text (default) or HTML. Text

Example 1: This example imports email messages from the inbox where the subject contains the word “Lunch Plans”.

EXEC EDIS.usp_email_import_messages @svc_id = 'EWS_team_inbox'

,@folder_path = 'Inbox'

,@dest_tbl_nm = '##messages_staging_raw'

,@subject_filter = 'Lunch Plans'

;

 

Example 2: This example imports email messages in sub folder “Important Tasks” that occurred between 7/1/2017 and 7/5/2017.

EXEC EDIS.usp_email_import_messages @svc_id = 'EWS_team_inbox'

,@folder_path = 'Inbox/Important Tasks'

,@dest_tbl_nm = '##messages_staging_raw'

,@start_dt = '2017-07-01'

,@end_dt = '2017-07-05'

;

 

Move Email Messages Task
This task moves email messages from one folder to another in a Microsoft Exchange Web Service Account

Procedure: SSISDB.EDIS.usp_email_move_messages

Parameters

Name Description Default Value
@svc_id Service ID to be used to move the email messages. Currently, only Exchange Web Services (EWS) ID’s are supported for this task. None; Required
@src_folder_path The path of the email folder to move messages from. The root path is “Inbox”. To access sub folders, separate the folder paths with a forward slash i.e. to access a sub folder named “My Tasks” under the inbox, you would use “Inbox/My Tasks” for this parameter value. None; Required
@tgt_folder_path The path of the email folder to move messages to. The root path is “Inbox”. To access sub folders, separate the folder paths with a forward slash i.e. to access a sub folder named “My Tasks” under the inbox, you would use “Inbox/My Tasks” for this parameter value. None; Required
@msg_id_list The list of exchange email ID’s to move. To build this list, use aggregate function SSISDB.EDIS.ufn_concat_list (Examples below will illustrate). None; Required

Example 1: This example moves messages from the inbox to sub folder “completed tasks/archive” that have the word “Lunch Plans” in the email subject.

-- Step 1: Import the messages so we can get the IDs
EXEC EDIS.usp_email_import_messages @svc_id = 'EWS_team_inbox'

,@folder_path = 'Inbox'

,@dest_tbl_nm = '##inbox_msgs'

;
-- Step 2: Create the message ID List based on the email messages having the word "Lunch Plans" in the subject.
DECLARE @msg_list nvarchar(max);
SELECT @msg_list = SSISDB.edis.ufn_concat_list(email_id, ';')
FROM ##inbox_msgs
WHERE subject = 'Lunch Plans'
-- Step 3: Move the messages
EXEC EDIS.usp_email_move_messages @svc_id = 'EWS_team_inbox'

,@src_folder_path = 'Inbox'

,@tgt_folder_path = 'Inbox/completed tasks/archive'

,@msg_id_list = @msg_list

;

 

Delete Email Messages Task
This task deletes email messages using an EWS account. The messages are sent to the "Deleted Items" folder.

Procedure: SSISDB.EDIS.usp_email_delete_messages

Parameters

Name Description Default Value
@svc_id Service ID to be used to move the email messages. Currently, only Exchange Web Services (EWS) ID's are supported for this task. None; Required
@msg_id_list This is the list of message ID's to delete, separated by a semi-colon None; Required

Example 1: This example deletes messages with "Check-In" in the subject line.

-- Step 1: Create the message ID List based on the email messages having the word "Check-In" in the subject. This part assumes you have already imported the email messages to a table called "##inbox_msgs".
DECLARE @msg_list nvarchar(max);
SELECT @msg_list = SSISDB.edis.ufn_concat_list(email_id, ';')
FROM ##inbox_msgs
WHERE subject = 'Check-In'
-- Step 2: Delete the messages
EXEC EDIS.usp_email_delete_messages @svc_id = 'EWS_team_inbox'

,@msg_id_list= @msg_list

;

 

Create Folder Task
This task creates a new folder using an EWS account.

Procedure: SSISDB.EDIS.usp_email_create_folder

Parameters

Name Description Default Value
@svc_id Service ID to be used to connect to the Exchange account. Currently, only Exchange Web Services (EWS) ID's are supported for this task. None; Required
@folder_nm Name of the folder you want to create None; Required
@parent_folder_path Path of the parent folder to place this new folder under. To target a sub folder, start with Inbox and use forward slashes for the path i.e. if you want to place a folder under sub folder "Daily Reports", which is in the Inbox, you would specify this parameter as "Inbox/Daily Reports" None; Required

Example 1: This example creates a folder called "test" under parent folder "Inbox/Old Messages".

EXEC EDIS.usp_email_create_folder @svc_id = 'EWS_team_inbox'

,@folder_nm = 'test'

,@parent_folder_path = 'Inbox/Old Messages'

;

 

Delete Folder Task
This task deletes a folder using an EWS account. The folder is moved under the "Deleted Items" folder for the account.

Procedure: SSISDB.EDIS.usp_email_delete_folder

Parameters

Name Description Default Value
@svc_id Service ID to be used to connect to the Exchange account. Currently, only Exchange Web Services (EWS) ID's are supported for this task. None; Required
@folder_nm Name of the folder you want to delete None; Required
@parent_folder_path Path of the parent folder that the target folder is located in. To target a sub folder, start with Inbox and use forward slashes for the path i.e. if you want to delete a folder under sub folder "Daily Reports", which is in the Inbox, you would specify this parameter as "Inbox/Daily Reports" None; Required

Example 1: This example deletes a folder called "test" under parent folder "Inbox/Old Messages".

EXEC EDIS.usp_email_delete_folder @svc_id = 'EWS_team_inbox'

,@folder_nm = 'test'

,@parent_folder_path = 'Inbox/Old Messages'

;

 

Save Attachment to File Task
This task saves attachments from email messages to a local directory.
NOTE: email ID's are case-sensitive

Procedure: SSISDB.EDIS.usp_email_save_attachment_to_file

Parameters

Name Description Default Value
@email_id The email ID that contains the attachment content. The email ID column is generated when you run procedure usp_email_import_messsages to download messages to a table. None; Required
@src_tbl Name of the table where the email messages were downloaded to None; Required
@folder_path Path to the local directory where you want to save off the attachments(s) None; Required

Example 1: This example saves the attachments from a specific email ID to local folder "C:\temp\email_imports\".

-- Step 1: Import the messages so we can get the IDs
EXEC EDIS.usp_email_import_messages @svc_id = 'EWS_team_inbox'

,@folder_path = 'Inbox'

,@dest_tbl_nm = '##inbox_msgs'

,@include_attachment_content = 1 -- Important to set this flag so you download attachment content

;
-- Step 2: Save the attachments for the message we want
EXEC EDIS.usp_email_save_attachment_to_file @email_id = 'AAMkADBlZWM2ODJiLTM4NzctNDEwMC05YTk3LTI1MTcwOGI0ZmVlZABGAAAAAAB4gcMojaRvRas4WOwVGCy7BwBEvfdJYIy3TqSKhwgi5AP7AAhhuaIsAAABDkJWSsuxSZPKGoNTd/W7AADp5/Z9AAA='

,@src_tbl = '##inbox_msgs'

,@folder_path = 'C:\temp\email_imports\'

;