Solved

SSIS Email Task

Posted on 2009-04-02
13
654 Views
Last Modified: 2013-11-10
I am picking an Excel file (Report) from a shared folder and sending an email using Send mail task from SSIS and this is working fine. My Question is Before sending this Email I have to count the rows in a table (I have to count the rows in a table for the latest data) and if I have those rows in the table I have to send an Email with the Report and If I dont have those rows in the table I have to Send a Msg in an Email stating that the Report could not be delivered. Please Help me Quick, this is very urgent to me.
To Say this simply, I have to Check this.
 
Select Count(*) from StudentEnroll Where Convert(Varchar, AppRecDate, 101) > getdate()-2
 
If the Above query result is  > 5 I have to email the report
and if the query result is < 5 I have to send an email with this message ("The Report Could not be sent because of a data issue")
Can you please help me with this, this is very urgent to me.. I have posted my Question in EE and I have pasted the link above.
Thanks in advance...
0
Comment
Question by:KuldeepReddy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24048781
Hi Friend,
Simple put the SQL query in a SQL task and ste the return result (count rows) to a SSIS Variable...
Then link the SQL task to a SendMail task and use the expression and constraint (cehck images)

The expreesion could be @@CountRows>0
SSIS-Contraint1.JPG
SSIS-Contraint2.JPG
0
 

Author Comment

by:KuldeepReddy
ID: 24050512
Hi Pedro, I have Checked the Answer, Can you please give me Some more detail for designing the package. From the Images I have seen 3 Execute Sql Tasks, Do I need to Add these 3 and 2 Email Tasks or 1 Exec Sql Task and 2 Email Tasks.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24050666
attach here the database script to create the table and insert some data and I do it for you!
Cheers!
Pedro
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:KuldeepReddy
ID: 24051008
Thanks Pedro, Here is my Requirement To tell you in simple terms.
I need to Execute the Below Given SQL Statement
Select Count(*) from StudentEnroll Where Convert(Varchar, AppRecDate, 101) > getdate()-2
 
If the result of the Above Sql Statement is Greater than(>) 5 then I have to send an email of a report thats on my shared drive
 
And If the result of the Above Sql Statement is Less than(<) 5 Then I have to send another email saying that the report cannot be sent. In this case I should not send the Report I should only send the error message.
 
Simply Evaluating the Above Sql and if the result is greater than(>) 5 then it should be treated as Success and the Report has to be sent.
And if the Above Sql  result (Count of rows) is Less than(<) 5 then it should be treated as Failure and the Failure msg has to be sent with out the report.
 
Please let me know if you dont Understand this.
 
Thanks in Advance..
0
 

Author Comment

by:KuldeepReddy
ID: 24051098
USE [OHE_CRM_MKTG]
GO

CREATE TABLE [dbo].[StudentEnroll](
      [EnrollID] [int] NOT NULL,
      [StudentID] [int] NULL,
      [StuNum] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [AppRecDate] [datetime] NULL,
      [StartDate] [datetime] NULL,
      [ADAID] [int] NULL,
      [SchoolID] [int] NULL,
      [ProgramID] [int] NULL,
      [DateAdded] [datetime] NULL,
      [DateLstMod] [datetime] NULL,
      [SchoolStatusID] [int] NULL,
      [OriginalAmRepId] [int] NULL,
      [OriginalExpStartDate] [datetime] NULL,
      [ExpStartDate] [datetime] NULL)
0
 

Author Comment

by:KuldeepReddy
ID: 24051105
Hi Pedro I have attached the DB Script For that Table, And If you want I can Send you some sample Data too.
0
 

Author Comment

by:KuldeepReddy
ID: 24051116
I have to Count the Distinct StudentID in that Query
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24051391
send me some data
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 500 total points
ID: 24051967
Check the attached package.
Add it to a SSIS Solution!
Helped?
regards,
Pedro

www.pedrocgd.blogspot.com
SSIS-Interface.JPG
Package-EE98-dtsx.txt
0
 

Author Comment

by:KuldeepReddy
ID: 24051991
Hi Pedro I have sent Some Sample data in an Email to you
0
 

Author Comment

by:KuldeepReddy
ID: 24052241
Hi Pedro, My Actual requirement is Based on the Conditions I need to Send an email, As I see in the Package If nrRows>5 then I need to send an email and If nrRows < 5 I need to send a msg in the email. So Should I add the send mail task
to the Script task. Or should I add the Send mail task in the place of Script mail task.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24052309
Ok.. your problem is the send mail process? You never used it?
0
 

Author Comment

by:KuldeepReddy
ID: 24052331
Yes In fact I never used SSIS, I am just new to SSIS and Learning, Thanks
0

Featured Post

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question