Solved

SSIS Email Task

Posted on 2009-04-02
13
641 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

786 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