Logging Sent Emails with Advanced ETL Processor

Build the sent-email logging workflow in seven steps

1. Send the message. Configure the Send Email action with the SMTP or Microsoft Graph connection, recipient, subject, body, and attachments.
2. Expose the output variables. Enable Message ID, Raw Message, To, and Subject on the Variables tab.
3. Create a filename-safe identifier. Use a Set Variables action to remove characters from #MessageID# that should not appear in a Windows filename.
4. Create a dated archive folder. The tutorial uses C:\DATA\<CurrentDate> so one directory does not become a museum of every email ever sent.
5. Save the raw message. Use Save String to write #RawMessage# to an .eml file named with the cleaned identifier.
6. Insert the audit row. Use the SQL Script action with an existing database connection and an embedded statement.
7. Verify both outputs. Open the saved .eml file and query the database row before scheduling the package.

Store the message beside its business identifiers

The workflow inserts seven values into EMAIL_LOG. SQL Server generates the row ID and email date. The variable mapping connects the archived message to the business record and leaves room for reply correlation.

Keep the variable names and database column names distinct. The mapping is explicit, which is less exciting than guessing and considerably easier to support.

Create the SQL Server email log table

The workflow uses this table definition. It creates an identity primary key and applies the SQL Server date when EMAIL_DATE is omitted from an insert.

CREATE TABLE [dbo].[EMAIL_LOG](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EMAIL_TYPE] [varchar](150) NULL,
	[EMAIL_DATE] [date] NULL,
	[ORG_CODE] [varchar](50) NULL,
	[NATIONAL_CODE] [varchar](50) NULL,
	[INVOICE_NUMBER] [varchar](50) NULL,
	[FILE_NAME] [varchar](250) NULL,
	[MESSAGE_ID] [varchar](250) NULL,
	[IN_REPLY_TO] [varchar](250) NULL,
 CONSTRAINT [PK_EMAIL_LOG] PRIMARY KEY CLUSTERED
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EMAIL_LOG] ADD CONSTRAINT [DF_EMAIL_LOG_EMAIL_DATE] DEFAULT (getdate()) FOR [EMAIL_DATE]
GO

The date type records the calendar date, not a time of day. Change the design before production if the audit requirement needs a precise send time.

Insert the runtime values with the SQL Script action

The workflow uses this statement:

INSERT INTO [EMAIL_LOG]
           ([EMAIL_TYPE]
           ,[ORG_CODE]
           ,[NATIONAL_CODE]
           ,[INVOICE_NUMBER]
           ,[FILE_NAME]
           ,[MESSAGE_ID]
           ,[IN_REPLY_TO])
     VALUES
           ('#EMAIL_TYPE#'
           ,'#ORG_FROM#'
           ,'#ORG_TO#'
           ,'#INVOICE_NUMBER#'
           ,'#MESSAGE_FILE_NAME#'
           ,'#MessageID#'
           ,'#IN-REPLY-TO#')

Set Replace Variables to True. With it disabled, SQL Server receives the literal placeholder text instead of the runtime values.

The values are quoted in the supplied statement. Do not pass untrusted values into direct SQL substitution unchanged. Escape apostrophes and other database-sensitive characters before this action, then test the resulting statement with representative business data.

The audit row becomes the reply-tracking key

A reply normally carries the original Message-ID in its In-Reply-To header. Store the sent value now, then the next workflow can compare it with incoming messages. This is more dependable than matching subjects, which users and email software are free to edit.

Read the reply-tracking workflow guide for the next step. For the email standard behind these fields, see RFC 5322 section 3.6.4 and Microsoft’s Graph message resource.