Solved

SSIS Email Task

Posted on 2009-04-02
13
649 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
Industry Leaders: 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 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

Suggested Solutions

Title # Comments Views Activity
SQL Server Agent Job Error 13 81
Where clause to fliter varchar with Characters 12 59
Creating a View from a CTE 15 49
SQL Server Agent "Access Denied" Error 3 36
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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

739 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