September 17, 2008 at 7:32 am
Thanks guys....I will try this and let u know how it worked....
September 18, 2008 at 6:38 am
Hi Todd,
the idea you suggested worked. instead of @varcount = 0, i had to give @varcount <=0....thats it!!!!
thank you so much...
Regards....
December 18, 2008 at 6:43 pm
I tried your example - thanks for posting it! 🙂 However, my output is in the XML in the email sent and I understood the query results to be included in the email task, but did you mean for them to the xml format itself?
Thanks!
December 19, 2008 at 6:03 am
shari (12/18/2008)
I tried your example - thanks for posting it! 🙂 However, my output is in the XML in the email sent and I understood the query results to be included in the email task, but did you mean for them to the xml format itself?Thanks!
Glad you found it useful.
Yes, that's supposed to be XML in the body of the e-mail. But, if you wanted something else you could add a Script Task and work with the XML to get the desired format. I'm sure there are better ways to go about this but here's one example.
' Script Task
' Add reference to system.xml
Dim s As String = Dts.Variables.Item("varXMLdata").Value.ToString
Dim t as string = ""
Dim r As XmlReader = XmlReader.Create(New IO.StringReader(s))
While r.Read()
If r.HasAttributes Then
t += r.Name & ":"
While r.MoveToNextAttribute()
t += r.Name & "=" & r.Value & " "
End While
r.MoveToElement()
End If
t += vbCrLf
End While
r.Close()
Dts.Variables.Item("varMessage").value = t
May 26, 2009 at 5:42 pm
Todd, thank you for the example. I've got it working, but the final format/layout of the email message body includes the xml tags. Is there a setting in the XML or Send Mail task to clean this up? Or will I need to use a Script Task to accomplish this?
EDIT: Sorry, it helps to read the entire thread... I'll give your script example a try. Thanks again.
October 21, 2010 at 9:18 am
hey Todd,
Your process helped me a lot to send the result set to send mail task.
But I get the result set in xml format in the message body. The script you gave above to parse the xml doesnot work. Please let me know if you have any other suggestions in detail.
Thank you so much. Your posts are wonderful and very helpful/
October 28, 2010 at 1:13 pm
ptheberge (9/17/2008)
I created a SQL Task that does Select count(*) from Table, and thenuse the Result Set tab to populate the package variable rowcount.
Put a 0 in the Result name column, and the varialbe name in the other column. You can then create 2 tasks that flow out from that SQL Tas. The first task would execute only if the rowcount variable is greater than 1 by using a precedence constraint. Change the Evaluation Operation to "Expression" and the Expression value to "@rowcount > 0".
The second step could execute only if the count was equal to 0
Let meknow
Good tip, I solved a similar problem using this method and it worked really well. By the way, I'm sure you mean "if the rowcount variable is greater than 0..." above!
December 30, 2010 at 7:53 am
Hi I am getting error as below:
Error: 0xC002F304 at XML Task, XML Task: An error occurred with the following error message: "Namespace Manager or XsltContext needed. This query has a prefix, variable, or user-defined function.".
Task failed: XML Task
Can anyone help me on this
February 2, 2011 at 9:11 pm
I am trying to send E-Mail by using the Send E-Mail Task in SSIS. First I have successfully configured the Database Mail in SSMS. Below are the following configuration settings
Account Name: SQL Mail Agent
Description: Using Gmail Account
Outgoing mail server (SMTP)
E-mail address: xxx@gmail.com
Display name: SQL Mail
Reply e-mail:
Server name: smtp.gmail.com Port No: 587
Checked the SSL connection
SMTP Authentication
Basic authentication
User name: xxx@gmail.com
Password:******
Confirm Password:******
In second step I have configured SMTP Connections Manager information in BIDS is as follows
Name: SMTP Connection Manager
Description: SMTPConnection
SMTP Server: smtp.gmail.com
Checked windows authentication
Checked SSL
When I am trying to execute the package the following error message shows up in the Execution Result
[Send Mail Task] Error: An error occurred with the following error message: "The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at ".
Can anyone please let me know where I am going wrong?
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply