how to read table rows data using Data.OleDb.OleDbDataReader in SSIS Script task? The package contains only script task, the code seems like the below: Dim objOleDb As New Data.OleDb.OleDbConnection Dim objData As Data.OleDb.OleDbDataReader Dim objCommand As Data.OleDb.OleDbCommand objOleDb.ConnectionString = strOleDb objOleDb.Open() Dts.Events.FireInformation(900, "", Now & " " & "Connected to:" & objOleDb.DataSource, "", 0, True) objCommand = objOleDb.CreateCommand() objCommand.CommandText = objVars("User::OLEDB_Select").Value objData = objCommand.ExecuteReader() Dim myHtmlMessage As MailMessage Dim mySmtpClient As SmtpClient Dim strPageScript As String myHtmlMessage = New MailMessage("sender@address.com", _ "to@address.com", "subject", "body") mySmtpClient = New SmtpClient("smtp.com", 25) objData.Read() For i = 0 To UBound(objData) Select i Case 0 strBCC = objData.Item(i) Case Else strBCC = strBCC & ", " & objData.Item(i) End Select Next objData.Close() mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials mySmtpClient.Send(myHtmlMessage)

why you used Script task for this? you can simply use Execute SQL Task for querying data from any database. As I got from your code, you are querying database data, and send mail to every subscribers, you can fetch data from database with EXECUTE SQL TASK, and fill data in package variables, and then using SEND MAIL TASK or a SCRIPT TASK to send mail to the subscriber address in package variables. tell us what exactly you need to do and I can help you in detail to implement in SSIS way.http://www.rad.pasfu.com

Hi Reza, I would like send an HTML report (therefore the send mail task doesn't fit) to banch of addresses which should derieved from oracle db. I've tried doing it with Script task, but met some problems running over the table rows. see more details below (pay attention to the red lines): Public Sub Main() Dim objVars As Variables Dim objOleDb As New Data.OleDb.OleDbConnection Dim objData As Data.OleDb.OleDbDataReader Dim objCommand As Data.OleDb.OleDbCommand Dim dteStart As Date Dim dteStop As Date Dim strServer As String Dim strDB As String Dim strPass As String Dim strUser As String Dim strLogFile As String Dim strLogFileFull As String Dim blnLogDelete As Boolean Dim strEmailField As String Dim strBCC As String Dim strSenderDisplayName As String Dim intOleDb As Integer Dim strOleDb As String dteStart = Now Try Dts.VariableDispenser.LockForRead("User::ServerName") Dts.VariableDispenser.LockForRead("User::DataBase") Dts.VariableDispenser.LockForRead("User::OLEDB_ServerName") Dts.VariableDispenser.LockForRead("User::OLEDB_Provider") Dts.VariableDispenser.LockForRead("User::OLEDB_Select") Dts.VariableDispenser.LockForRead("User::OLEDB_User") Dts.VariableDispenser.LockForRead("User::OLEDB_Pass") Dts.VariableDispenser.LockForRead("User::Email_Field") strPass = objVars("User::OLEDB_Pass").Value strUser = objVars("User::OLEDB_User").Value strServer = objVars("User::ServerName").Value strDB = objVars("User::DataBase").Value strEmailField = objVars("User::Email_Field").Value strSenderDisplayName = objVars("User::Email_Field").Value strOleDb = "Data Source=" & objVars("User::OLEDB_ServerName").Value & ";User ID=" & strUser & ";Provider=" & objVars("User::OLEDB_Provider").Value & ";Persist Security Info=True" & ";Password=" & strPass & ";" objOleDb.ConnectionString = strOleDb objOleDb.Open() Dts.Events.FireInformation(900, "", Now & " " & "Connected to:" & objOleDb.DataSource, "", 0, True) objCommand = objOleDb.CreateCommand() objCommand.CommandText = objVars("User::OLEDB_Select").Value objData = objCommand.ExecuteReader() Dim myHtmlMessage As MailMessage Dim mySmtpClient As SmtpClient Dim strPageScript As String 'link to SSRS report - using a href: strPageScript = "<table> <tr><td>Hello, this is a test using the Auto-Emailed Report. Please replay after receive. </td></tr> <tr> <td> <a href='http://<ServerName>/sites/BBI/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/BBI/Reports/PRI%20Project/RS_LAK_MAIL.rdl&Source=http%3A%2F%2Fvm%2Dbisps01%2Fsites%2FBBI%2FReports%2FForms%2Fupdated%2Easpx%3FRootFolder%3D%252Fsites%252FBBI%252FReports%252FPRI%2520Project%26FolderCTID%3D0x012000C2185D740B44874981D6B9E86D0F2008%26TreeField%3DFolders%26TreeValue%3DPRI%2520Project&DefaultItemOpen=1' > Please click here to view your report</a></td></tr></table>" myHtmlMessage = New MailMessage("address@bbb.com", _ "address@ccc.com", "AutoEmailedReport Testing", strPageScript) mySmtpClient = New SmtpClient("smtp.com", 25) myHtmlMessage.IsBodyHtml = True objData.Read() 'read 1st table row value 'myHtmlMessage.Bcc.Add(objData.GetString(0)) For i = 0 To UBound(objData) Select Case i Case 0 strBCC = objData.GetValue(i) Case Else strBCC = strBCC & ", " & objData.GetValue(i) End Select Next objData.Close() myHtmlMessage.Bcc.Add(strBCC) mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials mySmtpClient.Send(myHtmlMessage) Dts.TaskResult = ScriptResults.Success Catch ex As Exception dteStop = Now Dts.Events.FireInformation(900, "", Now & " " & "Duration:" & DateDiff(DateInterval.Minute, dteStart, dteStop).ToString, "", 0, True) Dts.Log("MAIN " & ex.Message, 999, Nothing) Dts.TaskResult = ScriptResults.Failure End Try End Sub End Class

do you get any error message? if yes, paste the exact error message here.http://www.rad.pasfu.com

Thanks alot, it is working. what i've missed was doing the following: Do While (objData.Read()) myHtmlMessage.Bcc.Add(objData.GetString(0)) Loop