Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SSIS "Send mail task" Expand / Collapse
Author
Message
Posted Friday, February 29, 2008 5:16 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:35 PM
Points: 865, Visits: 872
From SSIS I want to run a query, and, if the result set is non-empty, call the Send Mail task with the result set contained in the Body of the email.

Any ideas on how to do this one?

TIA




Post #462732
Posted Friday, February 29, 2008 6:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 8:48 AM
Points: 253, Visits: 1,063

you can use execute sql task in the control flow to determine count of records of your query, and store this value in a variable (countofRecords).

In the precedence constraints that connects between execute sql task and email task, use "expression" as evaluation operation to execute email task. your expression would be @countofRecords > 0 . So email task executes only when record count is equal to or greater than 1.

In the email task, use attachment options to send the result of your query.

Post #462750
Posted Saturday, March 1, 2008 8:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334
This Control Flow will place the query results in the body of the message and deliver only if it contains records.

[Execute SQL Task] ---> [XML Task] -f(x)-> [Send Mail Task]

1) The Execute SQL Task returns the query results as XML.
2) An XML Task calculates the row count of the results.
3) The constraint uses an expression to determine if a message should be sent.
4) The Send Mail Task uses an expression to populate the body of the message.

Here's a list of variables and some of the task properties for this type of solution.

Variables:
varRowCount : Package : String
varResults : Package : String

Execute SQL Task properties:
ResultSet: XML
SQLStatement: SELECT col1,col2 FROM table1 FOR XML AUTO;
Result Name: 0
Variable Name: User::varResults

XML Task properties:
OperationType: XPATH
SourceType: Variable
Source: User::varResults
SaveOperationResult: True
Destination: User::varRowCount
SecondOperand: count(//table1)

Send Mail Task properties:
MessageSource : @[User::varResults]





Post #462824
Posted Saturday, March 1, 2008 7:51 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:42 PM
Points: 386, Visits: 2,345
hey i tried this but i got some errors let me tell u the way i created this ...

Error: Failed to lock variable "user::varresults" for read/write access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

[XML Task] Error: An error occurred with the following error message: "Failed to lock variable "user::varresults" for read/write access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ".

Task XML Task failed

anyway when i create two variable name varresults and varrowcount what should i give in the value column i m new to sql server plz dnt mind ...so what should i give in the value column i jus gave 0 ....once i have created variable then in the exceute sql task

i have given resultset as XML AND THE SOURCE N CONNECTION....then i have written statement ..


select col1,col2 from dbo.abc for xml auto...

and in paramter mapping do i need to add any variables i havent added any variables in that n jus added a variable in result set that is user::varrowcount and result name as 0...

then in xml task ...

operation xpath..sourcetype variable...source user::varresults
save operation true ..destination user::varrowcount
second operand : count(//table1) actualy what is second operand ..i gave the same way do i need to mention something infront of // or plz let me know.....

n in smpt mailtask i jus gave connection ...from mail id n t maild id ..actually what should be the from mailid the company id or anything else and to mail id i gave my gmail acc n from my comapnny mail id ....n how could i jus attache the results in that it says browse but we r not saving the results to any file ...n u said u need to use expressions or constraints where should we use that n whoch way plz let me know....thkz in advance ...


Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman
Post #462859
Posted Sunday, March 2, 2008 12:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334

Error: Failed to lock variable "user::varresults" for read/write access with error 0xC0010001 "The variable cannot be found….

Most likely the variables haven't been defined or aren't scoped appropriately. No values need to be assigned to the variables when they're added to the package, they're assigned when the package is run.

Post the DTSX file you have so far and I might be able to help.
Post #462939
Posted Sunday, March 2, 2008 11:27 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:35 PM
Points: 865, Visits: 872
Todd,

You method worked fine for me! Thanks.

Another question: as you indicated, under ExecuteSQL task properties I needed to set ResultName to "0". (If I don't, I get error messages.) What is the significance of this value to ResultName?


Thanks,
Barkingdog



Post #462991
Posted Monday, March 3, 2008 6:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:42 PM
Points: 386, Visits: 2,345
hi todd

do i need to add the variables in parameter mapping or not .....plz let me know n what is second operand ..........//table1 what is that do i need to mention anything before // or plz explaind abt second opernad


Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman
Post #463087
Posted Monday, March 3, 2008 11:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 1:35 PM
Points: 865, Visits: 872
BTW, I spoke to soon. Here's what I get for my email "Body":

user::myResults


In other words, I get the variable name, not its content. How do I get the content?

TIA,

barkingdog



Post #463297
Posted Monday, March 3, 2008 7:50 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334
Barkingdog,

You have a couple options for getting the variable placed in the body. One, leave the MessageSourceType as Direct Input and add an expression that sets MessageSource to the variable. Two, set the MessageSourceType to Variable and select the variable.

Specifying 0 for the result name is just a requirement when returning that type of result set.

--
kurraraghu,

The SecondOperand is an XPath query that uses the count function. The query doesn't use parameters so mappings aren't needed.

Post #463526
Posted Monday, March 3, 2008 8:35 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:42 PM
Points: 386, Visits: 2,345
so in the second operand do we need to write a query then bcoz he jus specified \\table 1 but what should be the query over could u jus give me an example plz...i m totally new to this so dnt mind explaining the basics...thkz in advance

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman
Post #463543
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse