February 29, 2008 at 5:16 pm
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
February 29, 2008 at 6:32 pm
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.
March 1, 2008 at 8:46 am
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]
March 1, 2008 at 7:51 pm
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
March 2, 2008 at 12:30 pm
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.
March 2, 2008 at 11:27 pm
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
March 3, 2008 at 6:30 am
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
March 3, 2008 at 11:00 am
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
March 3, 2008 at 7:50 pm
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.
March 3, 2008 at 8:35 pm
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
June 30, 2008 at 10:18 am
The second operant field is giving me issues too.
How does the XML Task determine the number of rows returned and what is referenced in the procedent constraint in the Expression box to reference the count of rows?
July 1, 2008 at 6:30 pm
ptheberge (6/30/2008)
The second operant field is giving me issues too.How does the XML Task determine the number of rows returned and what is referenced in the procedent constraint in the Expression box to reference the count of rows?
Since the operation was specified as XPATH in that task the second operand is the XPath query. That query returns the count: [font="Courier New"]count(//table1)[/font].
The result of that query is placed in varRowCount, which is then used in the constraint to check if it's greater than zero. Since varRowCount is a string the check looks like this: [font="Courier New"]@varRowCount > "0"[/font]
If you post your .dtsx package as an attachment I'll have a look.
September 16, 2008 at 2:57 pm
I have the query result going to the excel file and I'm sending an email with an attachment. But if the query does not have any result, I do not want to send a blank excel file (which happens right now that a blank file goes with the email). What should I do in this case?
Also if the query has no rows, I want to send another email notifying the same. Can anyone please guide me on this one?
Thanks in anticipation.
Regards....
September 16, 2008 at 4:17 pm
Based on your description is this what the package currently looks like?
Control Flow
[Data Flow Task] ---> [Send Mail Task]
Data Flow
[OLE DB Source] ---> [Excel Destination]
I haven't tested this but try adding a Row Count task in the the data flow between the source and destination. Use a package scoped variable for the Row Count. Then, in the control flow task use an expression to determine if it will send the mail; @varCount > 0, @varCount = 0.
For example:
Control Flow
-f(x)-> [Send Mail Task]
[Data Flow Task] --|
-f(x)->[Send Mail Task]
Data Flow
[OLE DB Source] ---> [Row Count] ---> [Excel Destination]
September 17, 2008 at 6:28 am
I created a SQL Task that does Select count(*) from Table, and then
use 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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply