One of the most useful features of Connect email is the ability to query data tables and generate email attachments on the fly. For example, your ETL pipeline might execute business rule validations and send the rejects to an error table. You can use xSQL to generate a reject.csv file attachment in the email creation expression.

In our example, we’ll create the email object e_xsql_attachment with two CSV attachments. One is a list of payee information, and the other is a result set from the xc_payment table:

1 SQL> create email e_xsql_attachment as (“To”=:v_email_to, “Body”=‘Here are the csv files you requested!’, “Subject”=‘End Process: Email Lesson at ‘ || CurDateTime(), “Attachment1”=(select distinct first_name, last_name, employee_id from xactly.xc_participant), “AttachmentName1”=‘participants.csv’, “Attachment2”=(select participant_id, position_name, participant_name, sum(amount), period_name from xactly.xc_payment), “AttachmentName2”=‘payfile.csv’);

Now let’s send that new email:

1 SQL> send email e_xsql_attachment;

Check your inbox for an email message with two attachments: