solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

SQL Power Tools - Agentless Stealth Bottleneck Monitoring of 600+ Wait Types

Server Bottleneck Monitor performs agentless monitoring of SQL wait conditions, SQL I/O, SQL plans, top N SQL, performance counters, disk bottlenecks, blocking, deadlocks, cache, database space, disk drive space and performance baselines. View in 3D graphs the performance of the entire server farm. No intrusive SQL traces are used. Optionally install our Zero Impact Sql Capture Agent at any time. Captures 100% of the SQL activity with the end-user response time. With ZERO impact it pinpoints all poor performing SQL with 100% accuracy. Think of it as a Zero Impact SQL trace facility.

Learn more!

Sending HTML formatted email in SQL Server using the SSIS Script Task

By: Arshad Ali | Read Comments (18) | Related Tips: More > Integration Services Email

Free SQL Server Learning - Making the most out of SQL Server Agent

Problem

In part 1 of this tip series, I discussed using the built-in Send Mail Task which is quite simple to use and can be used in a scenario where you need to send plain text email with less development efforts. In this second tip, I am going to discuss the use of the Script Task to overcome the limitations imposed by the Send Mail Task. In this tip, I will show how you can send HTML formatted emails from SSIS using the Script Task or rather sending emails using the .Net capabilities from your SSIS package.

Solution

I will start my discussion on using the Script Task to send email (both non-HTML and HTML formatted) with an example. First I will create a database table (MailsToBeSent) which will hold the information about the emails which are to be sent by the Script Task and then insert a few records in this table. Next I will create a stored procedure to retrieve the records from the above created tables.

So here is the script for creating these database objects.

--Create a table to store mails to be sent details
CREATE TABLE MailsToBeSent
(
[MailID] INT PRIMARY KEY,
[From] VARCHAR(200),
[TO] VARCHAR(200),
[CC] VARCHAR(200),
[BCC] VARCHAR(200),
[Subject] VARCHAR(200),
[Body] VARCHAR(MAX),
[IsHTMLFormat] BIT,
[Priority] CHAR(1)
)
GO

--Insert a non-HTML mail details to be sent
INSERT INTO MailsToBeSent([MailID], [From], [TO], [CC], [BCC], [Subject], [Body], [IsHTMLFormat], [Priority])
VALUES(1, 'arshad@gmail.com', 'arshad@gmail.com', 'arshad@gmail.com;ali@gmail.com','',
'Sending Non-HTML Mail Using Script Task', 'This Non-HTML mail has been sent using SSIS Script task.', 0, 'L')
GO

--Insert a HTML formatted mail details to be sent
INSERT INTO MailsToBeSent([MailID], [From], [TO], [CC], [BCC], [Subject], [Body], [IsHTMLFormat], [Priority])
VALUES(2, 'arshad@gmail.com', 'arshad@gmail.com', 'arshad@gmail.com;ali@gmail.com','',
'Sending HTML formatted Mail Using Script Task',
'This HTML formatted
mail has been sent using SSIS Script task.',
1, 'H')
GO

--Create a procedure to retrieve all the records
--from MailsToBeSent table to send mails
CREATE PROCEDURE GetMailsToBeSent
AS
BEGIN
SELECT [MailID], [From], [TO], [CC], [BCC], [Subject],
[Body], [IsHTMLFormat], [Priority]
FROM MailsToBeSent
END
GO

EXEC GetMailsToBeSent

Once you are done with the database object creation, let’s move on to create a package with Script Task to send emails.

Create a new project of Integration Services type in the Business Intelligence Development Studio. Drag a Script Task from the toolbox and drop it onto the Control Flow area of the Designer interface, right click on Script Task component and then select Edit, a screen similar to one as given below will come up. This is a screen shot from SSIS 2008. If you are still using SSIS 2005 it will say "Design Script..." instead of "Edit Script...".

ssis script task

On the Script page of “Script Task Editor” click on “Edit Script” button, it will bring up VSTA environment (On SSIS 2005 it’s VSA environment) for writing .Net code for sending emails.

Copy the code from the below tables and paste it in the VSA/VSTA code editor. There are two sets of code one for 2005 and one for 2008, so make sure you use the right version based on your version of Business Intelligence Development Studio, not the version of SQL Server you are connecting to. Because of changes in the scripting environment between SQL Server 2005 and 2008, there would is a slight change in the code that's why I am providing the below separate code to be used on SQL Server 2005 and on SQL Server 2008. If you are running the below code in your Script Task in SSIS 2005 environment, you may need to reference the System.XML.dll. By default it is included and referenced dll in SSIS 2008, so you would not have to worry if you are using it on SSIS 2008.

Here are the objects that are referenced with the System.XML highlighted below.

ssis script task system.xml

Please note you need to change the connection string in the code pointing to the server and database where you have created the above database objects and also change the SMTP server name which will be used to send emails. The two lines of code are as follows:

ConnString = "Data Source=ARALI-LAPTOP;Initial Catalog=Learning;Integrated Security=True;"

mySmtpClient = New SmtpClient("smtpserver")

Also, I have commented out the section that allows you to send emails using authentication to your mail server. So if you want to use a user and password this can be supplied as well.

Script : VB .Net Code for Script Task for SQL Server 2005

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.Net.Mail
Imports System.Data.SqlClient
Imports System.Xml
Public Class ScriptMain
    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        Dim mySmtpClient As SmtpClient
        Dim ConnString As String
        ConnString = "Data Source=ARALI-LAPTOP;Initial Catalog=Learning;Integrated Security=True;"
        Try
            Dim SqlQuery As String = "GetMailsToBeSent"
            Using conn As New SqlConnection(ConnString)
                conn.Open()
                Dim comm As New SqlCommand(SqlQuery, conn)
                comm.CommandType = CommandType.StoredProcedure
                Dim adap As New SqlDataAdapter(comm)
                Dim ds As New DataSet()
                adap.Fill(ds)
                If ds.Tables(0).Rows.Count > 0 Then
                    Dim intCount As Integer
                    Dim intCCCounter As Integer
                    Dim intBCCCounter As Integer
                    For intCount = 0 To ds.Tables(0).Rows.Count - 1
                        'Create an instance of MailMessage class and pass "From EmailID" and "To EmailID" to the constructor
                        '"To" list can accept multiple email address deliminated by comma
                        Using myMessage As New MailMessage(ds.Tables(0).Rows(intCount).Item("From").ToString(), Replace(ds.Tables(0).Rows(intCount).Item("To").ToString(), ";", ","))
                            'CC List
                            'MailMessage.CC property is a collection of type MailAddressCollection so you need to add one email address at a time in this collection as I am doing it in a loop for multiple address
                            'Also you can create a MailAddressCollection first, add all the CC email address to it and finally assign it to MailMessage.CC property
                            Dim CCAddressList As MailAddress
                            If ds.Tables(0).Rows(intCount).Item("Cc").ToString().Length > 0 Then
                                If ds.Tables(0).Rows(intCount).Item("Cc").ToString().IndexOf(";") > 0 OrElse ds.Tables(0).Rows(intCount).Item("Cc").ToString().IndexOf(",") > 0 Then
                                    Dim strEmails As String()
                                    strEmails = Split(Replace(ds.Tables(0).Rows(intCount).Item("Cc").ToString(), ";", ","), ",")
                                    For intCCCounter = 0 To strEmails.Length - 1
                                        CCAddressList = New MailAddress(strEmails(intCCCounter))
                                        myMessage.CC.Add(CCAddressList)
                                    Next
                                Else
                                    CCAddressList = New MailAddress(Replace(ds.Tables(0).Rows(intCount).Item("Cc").ToString(), ";", ","))
                                    myMessage.CC.Add(CCAddressList)
                                End If
                            End If
                            'BCC List
                            'MailMessage.BCC property is a collection of type MailAddressCollection so you need to add one email address at a time in this collection as I am doing it in a loop below for multiple address
                            'Also you can create a MailAddressCollection first, add all the BCC email address to it and finally assign it to MailMessage.BCC property
                            Dim BCCAddressList As MailAddress
                            If ds.Tables(0).Rows(intCount).Item("Bcc").ToString().Length > 0 Then
                                If ds.Tables(0).Rows(intCount).Item("Bcc").ToString().IndexOf(";") > 0 OrElse ds.Tables(0).Rows(intCount).Item("Bcc").ToString().IndexOf(",") > 0 Then
                                    Dim strEmails As String()
                                    strEmails = Split(Replace(ds.Tables(0).Rows(intCount).Item("Bcc").ToString(), ";", ","), ",")
                                    For intBCCCounter = 0 To strEmails.Length - 1
                                        BCCAddressList = New MailAddress(strEmails(intBCCCounter))
                                        myMessage.Bcc.Add(BCCAddressList)
                                    Next
                                Else
                                    BCCAddressList = New MailAddress(Replace(ds.Tables(0).Rows(intCount).Item("Bcc").ToString(), ";", ","))
                                    myMessage.Bcc.Add(BCCAddressList)
                                End If
                            End If
                            myMessage.Subject = ds.Tables(0).Rows(intCount).Item("Subject").ToString()
                            myMessage.Body = ds.Tables(0).Rows(intCount).Item("Body").ToString()
                            If ds.Tables(0).Rows(intCount).Item("IsHTMLFormat").ToString().ToUpper() = "TRUE" Then
                                myMessage.IsBodyHtml = True
                            Else
                                myMessage.IsBodyHtml = False
                            End If
                            If ds.Tables(0).Rows(intCount).Item("Priority").ToString().ToUpper() = "L" Then
                                myMessage.Priority = Mail.MailPriority.Low
                            ElseIf ds.Tables(0).Rows(intCount).Item("Priority").ToString().ToUpper() = "H" Then
                                myMessage.Priority = Mail.MailPriority.High
                            Else
                                myMessage.Priority = Mail.MailPriority.Normal
                            End If
                            'To be used for sending attachements
                            'myMessage.Attachments.Add(New Attachment("c:\example1.txt"))
                            'myMessage.Attachments.Add(New Attachment("c:\example2.txt"))
                            mySmtpClient = New SmtpClient("smtpserver")
                            'You can set the SMTP port number if it is not listening on default port
                            'mySmtpClient.Port = 26
                            'The credentials returned by DefaultNetworkCredentials represents the authentication credentials for the current security context in which the application is running.
                            mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
                            'If you don't want to use windows authentication to connect to your SMTP host and want to specify a different username and password, you can then use NetworkCredential class as shown below
                            'The NetworkCredential class is a base class that supplies credentials in password-based authentication schemes such as basic, digest, NTLM, and Kerberos.
                            'mySmtpClient.Credentials = New NetworkCredential("arshad", "abcd", "corpnet")
                            mySmtpClient.Send(myMessage)
                        End Using
                    Next
                End If
                conn.Close()
            End Using
        Catch E As Exception
            Dts.Events.FireError(-1, "Failure in Script Task while sending mails.", E.Message.ToString(), "", 0)
            Dts.TaskResult = ScriptResults.Failure
        End Try
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

Script : VB .Net Code for Script Task for SQL Server 2008

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.Net.Mail
Imports System.Data.SqlClient
Imports System.Xml
 _
 _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        Dim mySmtpClient As SmtpClient
        Dim ConnString As String
        ConnString = "Data Source=ARALI-LAPTOP;Initial Catalog=Learning;Integrated Security=True;"
        Try
            Dim SqlQuery As String = "GetMailsToBeSent"
            Using conn As New SqlConnection(ConnString)
                conn.Open()
                Dim comm As New SqlCommand(SqlQuery, conn)
                comm.CommandType = CommandType.StoredProcedure
                Dim adap As New SqlDataAdapter(comm)
                Dim ds As New DataSet()
                adap.Fill(ds)
                If ds.Tables(0).Rows.Count > 0 Then
                    Dim intCount As Integer
                    Dim intCCCounter As Integer
                    Dim intBCCCounter As Integer
                    For intCount = 0 To ds.Tables(0).Rows.Count - 1
                        'Create an instance of MailMessage class and pass "From EmailID" and "To EmailID" to the constructor
                        '"To" list can accept multiple email address deliminated by comma
                        Using myMessage As New MailMessage(ds.Tables(0).Rows(intCount).Item("From").ToString(), Replace(ds.Tables(0).Rows(intCount).Item("To").ToString(), ";", ","))
                            'CC List
                            'MailMessage.CC property is a collection of type MailAddressCollection so you need to add one email address at a time in this collection as I am doing it in a loop for multiple address
                            'Also you can create a MailAddressCollection first, add all the CC email address to it and finally assign it to MailMessage.CC property
                            Dim CCAddressList As MailAddress
                            If ds.Tables(0).Rows(intCount).Item("Cc").ToString().Length > 0 Then
                                If ds.Tables(0).Rows(intCount).Item("Cc").ToString().IndexOf(";") > 0 OrElse ds.Tables(0).Rows(intCount).Item("Cc").ToString().IndexOf(",") > 0 Then
                                    Dim strEmails As String()
                                    strEmails = Split(Replace(ds.Tables(0).Rows(intCount).Item("Cc").ToString(), ";", ","), ",")
                                    For intCCCounter = 0 To strEmails.Length - 1
                                        CCAddressList = New MailAddress(strEmails(intCCCounter))
                                        myMessage.CC.Add(CCAddressList)
                                    Next
                                Else
                                    CCAddressList = New MailAddress(Replace(ds.Tables(0).Rows(intCount).Item("Cc").ToString(), ";", ","))
                                    myMessage.CC.Add(CCAddressList)
                                End If
                            End If
                            'BCC List
                            'MailMessage.BCC property is a collection of type MailAddressCollection so you need to add one email address at a time in this collection as I am doing it in a loop below for multiple address
                            'Also you can create a MailAddressCollection first, add all the BCC email address to it and finally assign it to MailMessage.BCC property
                            Dim BCCAddressList As MailAddress
                            If ds.Tables(0).Rows(intCount).Item("Bcc").ToString().Length > 0 Then
                                If ds.Tables(0).Rows(intCount).Item("Bcc").ToString().IndexOf(";") > 0 OrElse ds.Tables(0).Rows(intCount).Item("Bcc").ToString().IndexOf(",") > 0 Then
                                    Dim strEmails As String()
                                    strEmails = Split(Replace(ds.Tables(0).Rows(intCount).Item("Bcc").ToString(), ";", ","), ",")
                                    For intBCCCounter = 0 To strEmails.Length - 1
                                        BCCAddressList = New MailAddress(strEmails(intBCCCounter))
                                        myMessage.Bcc.Add(BCCAddressList)
                                    Next
                                Else
                                    BCCAddressList = New MailAddress(Replace(ds.Tables(0).Rows(intCount).Item("Bcc").ToString(), ";", ","))
                                    myMessage.Bcc.Add(BCCAddressList)
                                End If
                            End If
                            myMessage.Subject = ds.Tables(0).Rows(intCount).Item("Subject").ToString()
                            myMessage.Body = ds.Tables(0).Rows(intCount).Item("Body").ToString()
                            If ds.Tables(0).Rows(intCount).Item("IsHTMLFormat").ToString().ToUpper() = "TRUE" Then
                                myMessage.IsBodyHtml = True
                            Else
                                myMessage.IsBodyHtml = False
                            End If
                            If ds.Tables(0).Rows(intCount).Item("Priority").ToString().ToUpper() = "L" Then
                                myMessage.Priority = Mail.MailPriority.Low
                            ElseIf ds.Tables(0).Rows(intCount).Item("Priority").ToString().ToUpper() = "H" Then
                                myMessage.Priority = Mail.MailPriority.High
                            Else
                                myMessage.Priority = Mail.MailPriority.Normal
                            End If
                            'To be used for sending attachements
                            'myMessage.Attachments.Add(New Attachment("c:\example1.txt"))
                            'myMessage.Attachments.Add(New Attachment("c:\example2.txt"))
                            mySmtpClient = New SmtpClient("smtpserver")
                            'You can set the SMTP port number if it is not listening on default port
                            'mySmtpClient.Port = 26
                            'The credentials returned by DefaultNetworkCredentials represents the authentication credentials for the current security context in which the application is running.
                            mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
                            'If you don't want to use windows authentication to connect to your SMTP host and want to specify a different username and password, you can then use NetworkCredential class as shown below
                            'The NetworkCredential class is a base class that supplies credentials in password-based authentication schemes such as basic, digest, NTLM, and Kerberos.
                            'mySmtpClient.Credentials = New NetworkCredential("arshad", "abcd", "corpnet")
                            mySmtpClient.Send(myMessage)
                        End Using
                    Next
                End If
                conn.Close()
            End Using
        Catch E As Exception
            Dts.Events.FireError(-1, "Failure in Script Task while sending mails.", E.Message.ToString(), "", 0)
            Dts.TaskResult = ScriptResults.Failure
        End Try
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

You should make sure you properly dispose of the instance of MailMessage class especially if you are sending attachments with the email otherwise you will end up having your files locked by Windows OS and you will not be able to delete them. The easiest way to avoid overhead of disposing of the unused objects is to use the USING statement and write your code inside its block, similar to the way it has been done in the above code.

So now for the execution of the above created package, two emails are sent to the intended audience as shown below. As expected the first email (MailMessage.IsBodyHtml = False) is non-HTML email whereas the second email (MailMessage.IsBodyHtml = True) is HTML formatted, look at color in the message body.

ssis send email text format

ssis send email html format

Note

If you are running the above code in your Script Task in SSIS 2005 environment, you may need to make reference to System.Xml.dll. By default it is included and referenced dll in SSIS 2008, so you would not have to worry if you are using it on SSIS 2008.
You need to reference System.Net.Mail namespace in your code which contains MailMessage and SmtpClient classes which are required for sending emails.
There are three differences to note here between SSIS 2005 and SSIS 2008 in terms of the Script Task.
- Now you have two language options to write your code i.e. Visual Basic .Net and C# .Net in SSIS 2008 whereas you had only one language option in SSIS 2005.
- The scripting environment in SSIS 2008 is VSTA and it is VSA in SSIS 2005. It means almost full .Net capabilities in SSIS
- In SSIS 2008, in Script Task Editor Dialog box, the Script page has got preference over other pages and is the first page in list on the left side, it means it saves one more click to reach to your development environment to write your code.

More details about the script environment enhancement in SSIS 2008 can be found in the article at http://www.sql-server-performance.com/articles/biz/SSIS_New_Features_in_SQL_Server_2008_Part5_p1.aspx

Next Steps

Review my tip "Sending email from SQL Server Integration Services (SSIS)" if you want to send plain text mail with less development efforts.
Review http://msdn.microsoft.com/en-us/library/ms142165.aspx for additional information.

Last Update: 5/18/2009

About the author

Become a paid author

Comments and Feedback:

Tuesday, November 03, 2009 - 8:29:39 AM - Rashid

Read The Tip

Hi,

Its a wondurfull writing, i didn't use or applied it yet, but i know somewhere down the road this post really gonna help me as i am not a .Net developer.

Thanks for sharing it.

Rashid

Monday, July 12, 2010 - 12:06:16 PM - ajroylance

Read The Tip

Hi,

I am trying to implemen this code is BIDS2008 but continually get an error:

"A namespace does not directly contain members such as fields or methods"

which I can't figure out why.

Any ideas?

Monday, July 12, 2010 - 1:18:16 PM - arshad0384

Read The Tip

Hi,

Can you please give me the exact name of the namespace, which is giving you error or which is missing?

BTW, you need to reference System.Net.Mail namespace in your code which contains MailMessage and SmtpClient classes which are required for sending emails.

Thanks,

Arshad

Friday, September 17, 2010 - 3:16:55 AM - Ann	Read The Tip
This blog Is very informative , I am really pleased to post my comment on this blog . It helped me with ocean of knowledge so I really belive you will do much better in the future.

Wednesday, December 22, 2010 - 12:23:32 PM - Denise Dreher

Read The Tip

Typo: in 2008 version, line: If ds.Tables(0).Rows.Count > 0 ThenDim intCount As Integer

Should read Then Dim instead of ThenDim.

Wednesday, December 22, 2010 - 12:25:13 PM - Denise Dreher

Read The Tip

Typo: in 2008 version, line: Dim intBCCCounter As IntegerFor intCount = 0 To ds.Tables(0).Rows.Count - 1

Should read Integer For instead of IntegerFor

Wednesday, December 22, 2010 - 12:31:15 PM - Denise Dreher

Read The Tip

Typo: in 2008 version, should the For begin on a new line instead of being on the same line as Dim?

If ds.Tables(0).Rows.Count > 0 Then
     Dim intCount As Integer
     Dim intCCCounter As Integer
     Dim intBCCCounter As Integer
     For intCount = 0 To ds.Tables(0).Rows.Count - 1

Wednesday, December 22, 2010 - 1:11:27 PM - Arshad

Read The Tip

Thanks Denise Dreher for pointing out this,

It seems the HTLML formatter has eaten up some whitespaces.

Thanks once again!

Thursday, December 23, 2010 - 12:33:23 PM - admin	Read The Tip
The code has been updated and should be correct now.

Tuesday, April 19, 2011 - 10:02:52 AM - Mark

Read The Tip

Hi and thank you for the very good example, my first attempt at setting up an SSIS job.

When I run the package from the design studio, I receive an email and it works fine. But when I set up the package as an SQL Job, it executes but I receive no email! I have been searching extensively, and it appears that this only seems to happen on SQL 2008, and it seems to be a permissions issue.

Any advice?

Thanks!
Mark

Tuesday, April 19, 2011 - 10:22:18 AM - Mark

Read The Tip

^^ Nevermind! Figured it out! I've been working on this since Friday. I tried setting up the SSIS service to run as Administrator. It made no difference.

And my code/job has been completing successfully all this time. So I finally got the idea to check the mail server. It was receiving a connection from the SQL server, but the connection was closing before it was completed sending. Even though I am using the .Send method, it is still terminating too soon before flushing the buffer when run as an SSIS job.

So the solution is simply to add:

System.Threading.Thread.Sleep(5000)

After the .Send, and it finally goes through! We are using Mdaemon as our mail server. I think the problem is in the SMTP client though. Doesn't matter, it works now!

Tuesday, July 05, 2011 - 2:46:33 AM - Harry

Read The Tip

Hey Arshad,

Just my 2 cents : we could use a ForEachADOEnumerator with an object variable to loop through the table with message details. This would reduce the code for dataset population and looping table datarows.

Great article!! Thanks!!

Tuesday, November 15, 2011 - 4:36:19 PM - Lena

Read The Tip

Dear Arshad,

Awesome writing. This worked like a charm! (SQL 2005) and does exactly what I need. Thanks a lot for sharing the code!!

Wednesday, June 27, 2012 - 7:45:41 AM - Suresh

Read The Tip

I landed on your post when searching for the option to send HTML formated email using script task in SSIS. THanks for the useful post.

I am doing a development in my local laptop and would like to send failure email when core logic in SSIS fails. Also, since i don't know my office SMTP server, i have tried to use gmail server to send test emails and have below necessary changes in your script posted. However, i am getting error saying "Error on sending email".

Please go thotough my below code changes and let me know if i am wrong anywhere.

-----------------------------------------------------------------------------------------------------------------------------------------------

mySmtpClient = New SmtpClient("smtp.gmail.com")
                            mySmtpClient.Port = 587
                            mySmtpClient.Credentials = New NetworkCredential("myID@gmail.com", "myPwd")
                            mySmtpClient.Send(myMessage)

=========================================================================================

Wednesday, September 05, 2012 - 8:46:38 AM - Richard Schaefer

Read The Tip

Why directly read the database in the Script task? Doesn't it make more sense from an ETL architecture perspective to read the data into a variable using an Execute SQL task and then in the Script task simply iterate over the variable as a set of rows? This eliminates the need for a connection in the Script task and keeps with the overall architecture of having your database connections defined in the Connections tab.

Monday, October 29, 2012 - 9:37:39 AM - Ryan Barrett

Read The Tip

Hi Arshad.

What about using SQL Mail (msdb.dbo.sp_send_dbmail) to send the HTML?

Tuesday, January 22, 2013 - 5:28:11 PM - Phil Katzenberger	Read The Tip
Getting the following error message, 'ScriptMain' is not CLS-compliant because it derives from 'VSTARTScriptObjectModelBase', which is not CLS-compliant. Am I forgetting something? Thanks!

Monday, April 29, 2013 - 3:49:29 PM - Mike Brown

Read The Tip

Getting the following error message, 'ScriptMain' is not CLS-compliant because it derives from 'VSTARTScriptObjectModelBase', which is not CLS-compliant. Am I forgetting something? Thanks!

This is the same as for Phil Katzenberger.

thanks

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen."

NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now

Wish your SQL Servers could run wide open? Learn how the Edgewood SQL Server Consultants can make it happen.

In two mouse clicks view SQL bottlenecks. With ZERO impact pinpoint all poor performing SQL with 100% accuracy.

SQL Server Data Tools - Got questions? Get the answers here!