Solved

SSIS Email Task

Posted on 2009-04-02
13
632 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now