Welcome To TechBrothersIT: SSIS - How To Email Flat File or Excel File Records in Tabular Format in SSIS Package

Scenario:

We got this requirement in which we have to read the records from Flat File Source or An Excel or from any other source and then we want to send these records in an email in tabular format by using SSIS Package.

Solution:

There are multiple ways to do this. In my post , I am going to write all the records from source/s into global Temp table and then we will use msdb.dbo.sp_send_dbmail stored procedure in Execute SQL task to send an email.

Step 1:

Create the temp table according to the requirement if does not exist by using Execute SQL Task. My Flat File has FName, LName and Address, So I will be creating ##TempClientTable by using below DDL script.

IF OBJECT_ID('tempdb..##TempClientTable') IS NOT NULL
  DROP TABLE ##TempClientTable
CREATE TABLE ##TempClientTable
  (
     FName   VARCHAR(50),
     LName   VARCHAR(50),
     Address VARCHAR(100),
  )

Fig 1: Create Temp table if not Exists

Step 2:

Drag the Data Flow Task and then Bring the Flat File Source or Excel Source according to your requirement. Create your Source Connection by using Flat File Source or Excel Source. In our case I have flat file as source. Then bring OLE DB Destination. By using the code in Step 1. go to SSMS and create the table so it will be available in drop down. Choose the ##TempClientTable in OLE DB Destination.

Fig 2: Flat File Connection Manager

OLE DB Connection Manager inside Data Flow Task from Flat File Source.

Fig 3: OLE DB Destination to use Temp Table

This is how your Data Flow Pane will look like

Fig 4: Data Flow Task to load records from Flat File to Temp Table

As Temp table will not be available at the start of SSIS Package, that will fail the SSIS Package. Go to Data Flow properties and Set DelayValidation=True

Fig 5: Set DelayValidation property on Data Flow Task

One last setting we have to do it , Set the RetainSameConnection=True on our DataBase connection Manager. Right Click on DataBase Connection Manager and go to properties and set property as shown below. This property will make sure that the tasks will be able to see the temp table.

Fig 6: RetainSameConnection Property on OLE DB Connection Manager

Step 3:

The last step is to use Execute SQL task and send email in tabular form for the records which are present in our ##TempClient Table.

Fig 7: Send Email from Execute SQL Task in SSIS Package

I have used below query in SQLStatement. You can change information in this query according to your requirements.

DECLARE @Table NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @Table = CAST(( SELECT [FName] AS 'td','',[LName] AS 'td','',
       [address] AS 'td'
FROM  ##TempClientTable  FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H2>Client Information</H2>
<table border = 1> 
<tr>
<th> FName </th> <th> LName </th> <th> Address </th> <th>'    SET @body = @body + @Table +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourMailProfileName',
@body = @body,@body_format ='HTML',
@recipients = 'youremail@domain.com',
@subject = 'Client Information' ;

Let's run our SSIS Package and check if we get email in required format.

Fig 8: SSIS Package to Send an Email in Tabular Form from Flat File

We received HTML formatted email from our SSIS Package as shown below.

Fig 10: HTML Email from SSIS Package

Welcome To TechBrothersIT

Label

SSIS - How To Email Flat File or Excel File Records in Tabular Format in SSIS Package