Send Mail Task in SSIS 2008

How to send a notification/mail to specific group of peoples through SSIS packages? Thanks to Send Mail Task. This can be achieved easily using send mail task.
By using the Send Mail task, a package can send messages if tasks in the package workflow succeed or fail, or send messages in response to an event that the package raises at run time. For example, the task can notify a database administrator about the success or failure of the Backup Database task.

You can configure the Send Mail task in the following ways:

• Write the message text for the e-mail.
• Write a subject line for the e-mail message.
• Set the priority level of the message. The task supports three priority levels: normal, low, and high.
• Specify the recipients on the To, Cc, and Bcc lines. If the task specifies multiple recipients, they are separated by semicolons.

I'll explain this by an example:

STEP 1:

Create a new SSIS Package and rename it with SendMailTask.dtsx.

STEP 2:

Add two package variable MessageSource and SMTPServer of type string to this package as shown below:

STEP 3:

Add a new SMTP Connection manager and configure its properties. Enter SMTP Connection manager in Name and Connection for sending mails in Description boxes. Now enter your SMTPServerName in SMTP server box as shown below:

Set SmtpServer property of above connection manager with variable User::SMTPServer by clicking on Expression in properties of SMTP Connection Manager. This is highlighted in below snap shot:

STEP 4:
Drag and drop Script Task to set your message. Double click on Script Task to open Script Task Editor, Select User::MessageSource as ReadWriteVariables and click on Edit Script... button to set the message. Write below code in main function. (This is for VB.NET, you can choose C# or VB.NET)

Public Sub Main()
   'Set MessageSource variable
   Dts.Variables("User::MessageSource").Value = "Hi," & vbCrLf & _
   "This is a test mail to check Send Mail Task in SSIS!" & vbCrLf & vbCrLf & _
   "Thanks and Regards," & vbCrLf & "_____________" & vbCrLf & "Hari"
   Dts.TaskResult = ScriptResults.Success
End Sub
 
However, the message text can be a string that you provide, a connection to a file that contains the text, or the name of a variable (in our case) that contains the text.
 
STEP 5:
Drag and drop Send Mail Task and double click on it to open Send Mail Task Editor. Select Mail from Left side and set mail properties as shown below:

 
Thats all. We are done with Send Mail Task setup, this package will look like below snapshot. Just right click on package in Solution Explorer and Execute. Receiver will get an email as soon as package executes successfully. However, mail delivery depends on the response of SMTP Server as well.
 

 
 
 
 
 
 
 
 
 
Note:

1. The To, Cc, and Bcc lines are limited to 256 characters.
2. The SMTP connection manager supports only anonymous authentication and Windows Authentication.

Find Largest Size Tables in a Database

Below is the stored procedure to return largest tables of a database.

IF OBJECT_ID('sp_LargestTables' ,'P') IS NOT NULL
DROP PROC sp_LargestTables
GO
/***************************************************************
CREATE BY : Hari Sharma
PURPOSE : To get a list of tables according to their size.
***************************************************************/
CREATE PROC sp_LargestTables
(
   @n int = NULL,
   @IsSystemAllowed bit = 0
)
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @LOW int
   SELECT @LOW = LOW
   FROM [master].[dbo].[spt_values] (NOLOCK)
   WHERE [number] = 1 AND [type] = 'E'

   IF @n > 0 SET ROWCOUNT @n
   SELECT TableName,[Row Count],[Size (KB)] FROM
   (
      SELECT QUOTENAME(USER_NAME(o.uid)) + '.' +
                     QUOTENAME(OBJECT_NAME(i.id))
         AS TableName
         ,SUM(i.rowcnt) [Row Count]
         ,CONVERT(numeric(15,2),
            (((CONVERT(numeric(15,2),SUM(i.reserved)) * @LOW) / 1024.0))) AS [Size (KB)]
      FROM sysindexes i (NOLOCK)
      INNER JOIN sysobjects o (NOLOCK)
         ON i.id = o.id AND
         ((@IsSystemAllowed = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
      WHERE indid IN (0, 1, 255)
      GROUP BY
         QUOTENAME(USER_NAME(o.uid)) + '.' +
         QUOTENAME(OBJECT_NAME(i.id))
   ) AS Z
   ORDER BY [Size (KB)] DESC

   SET ROWCOUNT 0
END
GO


How to use:

1. If you want all the user tables in the database with largest db size then:
EXEC sp_LargestTables [No Need to pass parameters]

2. If you want only 3 tables in the database with largest db size then:
EXEC sp_LargestTables 3

3. If you want only 20 tables in the database with largest db size including system tables then:
EXEC sp_LargestTables 20,1

Function to Split Multi-valued Parameters

How to split a comma seperated value?
Many times we need to write T-SQL statements to split a comma seperated value, however string is not necessarily to be comma seperated, it can be seperated by any delimiter e.g. comma (,), @, &, ; etc.

How to use Multi-valued Parameters of SSRS report in a Stored procedures?
One more question comes around, how to use a multi valued parameter of SSRS report in a Stored Procedure to filter report data? I am sure you can't use a multi valued parameter directly in T-SQL code without spliting multiple values, if you do so without spliting values, SPROC will throw an error.

To find the answer of above questions, you create a user defined function using below T-SQL code:

/**********************************************
CREATED BY HARI
PURPOSE : To split any multivalued string
seperated by any delimiter into multiple rows
***********************************************/
CREATE FUNCTION [dbo].[SplitMultivaluedString]
(
   @DelimittedString [varchar](max),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'')
                + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,
             CharIndex(@Delimiter,@sTemp)-1)
     
      SET @sTemp = RIGHT(@sTemp,
        LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END
GO

/* How to use this function:
-----------------------------------------
SELECT * FROM [dbo].[SplitMultivaluedString] ('1,2,3,4', ',')
SELECT * FROM [dbo].[SplitMultivaluedString] ('1;2;3;4', ';')
SELECT * FROM [dbo].[SplitMultivaluedString] ('Hari#Thomas','#')
*/