.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Sending Result Set as Email via Script task

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
Hello, I completely succeeded in creating a package that sends result set as Email. But the issue here is type of the connection I am using, right now using ADO.NET connection. But my lead wants to have OLDB connection in order to maintain uniformity across all packages. Requirement : querying a table having 3 fields, need to send this table content as an Email. Steps Following : Execute SQL task to store the Result set into a System.Object variable, here connection type using ADO.NET , but my lead wants it as OLDB. Script task: Using bellow code to load data from system.Object variable to a string variable Send mail Task : Using string variable ( which was populated in script task) as Message source in Send mail task . So everything working fine, getting proper email with bellow code in script task. But the issue is if I use OLDB connection type in Execute SQL task instead of ADO.NET Connection type, Script task is not working . Bellow script is throwing some error . Can anyone please provide script that works for OLDB connection type To load system.object variable to string variable Error Message in Script task ( if I use OLDB connection in Execute SQL task instead of Ado.NET) “Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'. Instances of types that represent COM components cannot be cast to types that do not repr

View Complete Post

More Related Resource Links

How to generate an excel and send it as email attachment in SSIS 2005 Script Task Component?


I am using a "Script Task" component. I am trying with following three simple steps:

1) Obtain the excel content in HTML

Dim reportInHtml As String = GenerateHTMLReport()

2) Generate an excel with below code:

Dim message As MailMessage

message = New MailMessage(fromAddress, toAddress, subject, String.Empty)

Using memoryStream As MemoryStream = New MemoryStream()

        Dim contentAsBytes As Byte() = Encoding.UTF8.GetBytes(reportInHtml)
        memoryStream.Write(contentAsBytes, 0, contentAsBytes.Length)
        memoryStream.Seek(0, SeekOrigin.Begin)

        Dim contentType As ContentType = New ContentType()
        contentType.MediaType = "application/ms-excel"
        contentType.Name = "Excel_Report.xls"

        Dim excelAttachment

Sending a File Using Email Helper in WebPages/Razor


Note: This is particularly with respect to Email Helper in WebPages/Razor. Please don't post links using System.Net.Mail object.

Following this tutorial: http://www.asp.net/webmatrix/tutorials/9-adding-email-to-your-website

Under "Sending a File Using Email" section what exactly is the code trying to do.

1: The SendFile.cshtml does not have any option to select file.

2: ProcessFile.cshtml is running fine. I am getting the email but no attachement.

I tried changing fileAttachment input to type="file" but not file is being sent to my email. I just get the message send in body.

Question: How exactly this file attachment works using Email Helper in WebPages.

getting a email delivert status when sending email using msdb.dbo.sp_send_dbmail


Hi All,

Can some help me regarding following.

I have set a job in sql server 2008 to send reminder emails for product expiration.
Now I want a report which will specify the list of the emails sent with the email status details like queued ,sent,delivered, failed.

I can fetch the emails that are sent by querying the like following

select * from sysmail_mailitems SM inner join sysmail_profile SP on SM.Profile_Id = SP.Profile_Id
and SP.Name ='ReminderProfile'

The problem is only getting a perfect status of email whether it is delived or failed. How I can get those status ?
Please help.


sending an email in asp.net


My problem is  when i am writing the whole code in asp.net using vb which is

Protected Sub btnsend_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsend.Click
        Dim mailmessage As System.Net.Mail.MailMessage = New System.Net.Mail.MailMessage()
        mailmessage.From = New System.Net.Mail.MailAddress(txtfromaddress.Text.Trim())
        mailmessage.To.Add(New System.Net.Mail.MailAddress(txtToAddress.Text.Trim()))
        mailmessage.Subject = txtSubject.Text.Trim()
        mailmessage.Body = txtbody.Text.Trim()
        Dim smtpClient As System.Net.Mail.SmtpClient = New System.Net.Mail.SmtpClient()

In the last line i m getting smtpException unhandled by user code (Failure sending mail)

In the web.config file i have written the code which is as follows:-

      <smtp from="someaddress@myCTdomain.com">
        <network host

Error While sending Email using Outlook object in asp.net 4.0


Hi ,

            I am trying to send Email using Outlook object but  it gives following error :

Retrieving the COM class factory for component with CLSID {0006F03A-0000-0000-C000-000000000046} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)).

I have added outlook reference "using Microsoft.Office.Interop.Outlook"  version

and i am using below code :

Microsoft.Office.Interop.Outlook.Application oApp = new Microsoft.Office.Interop.Outlook.Application();
            Microsoft.Office.Interop.Outlook._NameSpace oNameSpace = oApp.GetNamespace("MAPI");
            Microsoft.Office.Interop.Outlook.MAPIFolder oOutboxFolder =
            oNameSpace.Logon(null, null, false, false);
            Microsoft.Office.Interop.Outlook._MailItem oMailItem =

smtp is noe sending email instantly,it makes a Queue in my local system

I am using a  smtp to send email from asp.net (C#)code.it sending some email instantly but many of email is making a queue in my Inetpub folder at local system.what could be the issue ? please help me to get rid from it.ThankxArvind

Email task is not accepting the value from TO line

Hi. I'm trying to send an email to the TO: address in the following format "[Fax:blablalba@blablalba]" Send mail task tells me that the input string is in the incorrect format for email. How to skip this and send it out anyway? We have an Exchange server configured, which will send that type of email to the fax gateway. Thanks heaps, Kaarel.

Sending Email by exeucting a stored proc on SQL Server that references CLR

Hi Guys, I want to modify the data type of the @body variable in the stored procedure that sends an email. The stored procedure is as follows:   CREATE PROCEDURE [dbo].[spSendMail4]<br/> @recipients [nvarchar](4000),<br/> @cc [nvarchar](4000),<br/> @subject [nvarchar](4000),<br/> @from [nvarchar](4000), @body [nvarchar](4000), @attachment [nvarchar](4000)<br/> WITH EXECUTE AS CALLER<br/> AS <br/> EXTERNAL NAME [SMTPCLR].[StoredProcedure].[spSendMail]<br/> GO   I want to change the @body variable to data type: text. The reason for this, is because I am sending an HTML email whose content (characters) exceed 8000. Basically the new stored procedure should looks like below:   CREATE PROCEDURE [dbo].[spSendMail4] @recipients [nvarchar](4000), @cc [nvarchar](4000), @subject [nvarchar](4000), @from [nvarchar](4000), @body text, @attachment [nvarchar](4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SMTPCLR].[StoredProcedure].[spSendMail] GO   If attempt to make this change, I get the below error:   CREATE PROCEDURE for "spSendMail" failed because T-SQL and CLR types for parameter "@body" do not match.   Understandably so, because from the article http://msdn.microsoft.com/en-us/library/ms131092%28SQL.100%29.aspx CLR data type (.NET Framework) is not compa

Email Sending Failed

I have email sending option in my ASP.net application(Developed in visual web developer2008 on XP), i am sending the mails with 3 attachements without any problems.I have launched same project in another branch(On windowsXP Machine), from here i am not succeeded to send mails.Following error was througing..My smtp server is smtp.mailserver.com. Server Error in '/' Application.--------------------------------------------------------------------------------No connection could be made because the target machine actively refused it Code Sending mail successfully from my local machineProtected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click        Dim smtp As New System.Net.Mail.SmtpClient()        Dim smail As New System.Net.Mail.MailMessage        lblMessage.Text = ""        If DDReceiver.Text = "" Then            MsgBox("Enter Valid E-Mail Address")        Else            smail.To.Add(New System.Net.Mail.MailAddress(DDReceiver.Text))        End If     

C# , Sending email through exchange server?

I've searched on this and tried several methods using System.Net.Mail but I can't get it to work. I want to send email using the Exchange server they have at work. Anyone could show me how to do this (server requires authentication)   Thanks in advance

Excel sheet is not saving data while sending email

Hey Guys,  I am creating a SSIS package, where I am getting all the data into the excelsheet1, which is not formatted. So i created a formated excelsheet2, where i am giving reference to excelsheet1 cells reference. And I am sending email by attaching excelsheet2.              I am having issue when the data is updated, but my email attachment excel is showing old data. When i open the excelsheet1 it is being updated. I don't know why excelsheet2 is not updated automatically. I am using BIDS 2005 . Can anyone please suggest what will solve my problem. Thanks in Advance Priyamvadha

Task assignment to user without read access in 2007 results in no email to assignee, no warning to a

It appears (from observation and searching) that SP 2007 will not send a task assignment email when the assignee does not have read access for the assigned task and there is no warning when assigning a task to someone who will be unable to read it. I have not been able to find any mentions of attempting to address this quirk by either an item event listener or a workflow that would either prevent the assignment or task the originating user to fix the issue by either changing the assignment or having them added to the appropriate group.  I could see one done badly causing a cascade of tasks. The approaches that I've thought about are: Attempt to block assignment by hooking ItemAdding and ItemUpdating on the task content type.  In ItemUpdating, you could use SPListItem.DoesUserHavePermissions since the SPListItem should be there, but you'd may have check the list since I don't think SPListItem is available at that time.  I could see this causing problems when the assignment is via workflow or something that doesn't like other code interfering with its task creation. Hooking ItemAdded and ItemUpdated to assign a new task to the assigning user to either change the assignment or have the assignee added to the users with access to the site. Define a workflow which could be enabled for a list that would check if any new or modified task is assigned to users with

C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

sending email in asp.net A connection attempt failed because the connected party did not properly re

Hi everyone,   I am sending email , but getting this error on this line smtpClient.Send(mail); "A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond IP adress ...   MailMessage mail = new MailMessage(); mail.To.Add( "test@mywebname.com"); mail.From = new MailAddress(txtEmail.Text); mail.Subject = "Testing Email";   string Body = "Demo Email trial"; mail.Body = Body; mail.IsBodyHtml = true;   SmtpClient smtpClient = new SmtpClient(); smtpClient.Host = "mail.mywebname.com"; smtpClient.Credentials = new System.Net.NetworkCredential("test@mywebname.com", "passwd"); smtpClient.Send(mail);   plz help me

Access the SQL Command Query Value Property from the OLEDB Container within a Script Task

Hello, I'd like to Access the SQL Command Query Value Property from the OLEDB Container within a Script Task so I can audit the queries which are executing. What is the syntax for this or does anyone have a sample on how to do this? Thank you

Receive/Delete Multiple FTP Files based on condition using SCRIPT(VB) TASK

I am trying to receive and delete multiple FTP files from a remote FTP server using Script task Below is the code   Dim FTPConnMgr As ConnectionManager FTPConnMgr = Dts.Connections("FTP Connection Manager") Dim FTPClientConn As FtpClientConnection = New FtpClientConnection(FTPConnMgr.AcquireConnection(Nothing)) Dim FileTimeStampNew As String = "20100913021807" Dim remoteFileNames(0) As String remoteFileNames(0) = Dts.Variables("FtpRemoteDirectory").Value & "*" & FileTimeStampNew & ".*" 'Below hardcoded FileName works good. But the problem is there are lots of file in the FTP folder that I dont want to receive 'remoteFileNames(0) = Dts.Variables("FtpRemoteDirectory").Value & "Company_alpha_Full_20100913021807.xml" Dim localPath As String = Dts.Variables("FtpLocalDirectory").Value FTPClientConn.Connect() FTPClientConn.ReceiveFiles(remoteFileNames, localPath, False, False) FTPClientConn.Close() Dts.TaskResult = ScriptResults.Success End Sub End Class If i specifically mention the RemotefileNames this works fine but, when I say *.* it executes succefully but doesn't receives the file. Please advice how to receive multiple file based on a variable BR, AWM

notification email not sent when updating a task programmatically

I have a task that's in the task list and has no value in the Assigned To field. When I update the field like this: listItem[SPBuiltInFieldId.AssignedTo] = SPContext.Current.Web.CurrentUser.ID; ListItem.Update(); The task is updated (the assigned to has filled in). But no notification has been send to the person in the assigned to. But when I change the assigned to manualy to another person, and then again to the person it was before, the notification is send. So the email works and the notifications are activated, why is there no notification sent when I change the assigned to programmatically?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend