Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSIS Email Task

Posted on 2009-04-02
13
Medium Priority
?
659 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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