Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhani
Posting Yak Master
132 Posts |
 Posted - 2009-12-15 : 10:36:48
|
| Hello All,Please go through the below.........in my ssis package it loads data from some Database tables(it is a different software) to sql server Table,in the ssis package as a last step i have to send EMAIL when ever a table has data (if there is no rows then i dont need to send any mail) , if there is any rows in that table then i have to send a mailhow can i achive thisi have knowledge on Email task,so please help me logic (and steps that i need to take care of) , especially how to include the table data in a mail please....pleaseThanks in advance mastersasita |
|
|
dhani
Posting Yak Master
132 Posts |
Posted - 2009-12-15 : 11:24:25
|
| Please Kindly suggest me any ideas is it possible at all?Thanks in advance |
 |
|
|
JAG7777777
Starting Member
25 Posts |
Posted - 2009-12-17 : 04:12:08
|
| Hi Dahni,You can do this with a vaiable and precedence constraints.....What you do is this:1 - Create a variable, Int32, and call it 'CheckFlag' or something similar. Set its value to 0. You do this by right-clicking on the design pane and choosing 'Variables'. There is an 'Add Variable' icon. NB - Make sure you set the scope of your variable correctly (package level if you want to see it at multiple tasks).This step provides the means for verifying you have data before emailing later on.2 - Add a SQL task to your flow and set it to a Single Row result set. Then, in the SQL statement, write something that will give you the count of rows in your table - i.e. SELECT Count(*) AS RowCount FROM (your table name here). You'll need to set up a connection manager to your table and use this in the SQL task.3 - In the result set tab of the SQL task, map the result set of your statement (i.e. RowCount) to your variable (i.e. User::CheckFlag)Steps 2 & 3 populate your variable each time the package runs - so you can check each execution for data.4 - Now, on the pipeline(s) connection between the SQL task and your email(s), use a success constraint AND expression (i.e. you want the task to finish AND check the table for data).You do this by right-clicking the green pipeline and choosing 'Edit' - this will take you to the constraint editor. You then choose 'Expression and Constraint' in the 'Evaluation operation' field. Set the 'Value' to 'Success'.Then, in the expression field, write a boolean expression that checks the value of CheckFlag:i.e:@CheckFlag > 0 (this is the pipeline you use when there is data - i.e. you send an email saying "we have data" - it means the task completed successfully and you have a rowcount of 1 or more)@CheckFlag = 0 (this means the SQL task completed successfully, but you have no data)Step 4 checks for success (in terms of task completion) and evaluates the content of your variable using an expression - this means you could check for a specific row count if you wanted (or just the presence of any as this example shows).Hope this helps,Reply if you need any more assistance.JAG7777777 |
|
|
|
|
|
|
|
|
How to Inculde a table data in a mail (as a messag