Today I got requirement like after my ETL packages execution I need to send one email to team regarding what is status of package execution and how many records got success and fail . To do that I decided to use database mail component in SQL Server. We can configure database mail by two ways
1. Using GUI
Using GUI
By following below
screenshots we need to configure SQL Server Database Mail
DB Mail configuration:
We need to configure the sp_send_dbmail stored prcedure for
respected server , generally this is disabled mode due to security issue.
USE MASTER
GO
SP_CONFIGURE 'show advanced options ' ,1
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE 'Database Mail XPs'
,1
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE 'show advanced options' ,0
RECONFIGURE WITH OVERRIDE
Next we need to send mail by using above created profile and account . We can send mail in different formats , in this case i am sending mail with attachment of .csv file which is out put of one SQL query
EXEC MSDB.DBO.SP_SEND_DBMAIL
@PROFILE_NAME =
'KIRANMAIL',
@RECIPIENTS =
'KIRAN.ALLAM@FORTEST.COM',
@QUERY = 'SELECT * FROM ADVENTUREWORKSDW2012.SYS.TABLES',
@SUBJECT = 'LIST OF TABLES IN ADVENTUREWORKSDW2012',
@QUERY_RESULT_HEADER =
1,
@QUERY_RESULT_WIDTH =
256,
@QUERY_ATTACHMENT_FILENAME ='TEST1.CSV',
@QUERY_RESULT_SEPARATOR =
' ', ----Please consider
this as Tab space
@ATTACH_QUERY_RESULT_AS_FILE =
1,
@QUERY_RESULT_NO_PADDING =1 ;
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
