Solved

How to email results from a query using SSIS

Posted on 2009-07-16
10
803 Views
Last Modified: 2013-11-10
I have the following query in a "Execute SQL Task".

Now, provided the query returns at least one record, I want to e-mail the results to a group of people.
How can I check to see if my query returned any results?

Can someone please point me in the right direction?
Thanks
select person_id, firstname, lastname, People.univ_usr_id

from People 

inner join (SELECT univ_usr_id 

	FROM People 

	WHERE Person_ID NOT IN (SELECT Person_ID from Multiple_Sources WHERE Source in ('D', 'Z')) AND univ_usr_id IS NOT NULL

	GROUP BY univ_usr_id Having COUNT(*) > 1 

) b on People.univ_usr_id = b.univ_usr_id

Open in new window

0
Comment
Question by:IUAATech
  • 5
  • 5
10 Comments
 
LVL 8

Expert Comment

by:MartinCMS
ID: 24870361
Just add this code immediately after your query. This system variable will tell you how many row from the query result.

If @@ROWCOUNT > 0
   Begin
      -----your email code
   End
0
 

Author Comment

by:IUAATech
ID: 24870464
I was thinking of somehow using the precedence constraint between the "Execute SQL Task" and "Send Mail Task". Do you know if I can check for @@ROWCOUNT using precedence constraint?
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 24870636
Don't believe you can do that since system variables are good for immediate result following by the last committed transaction.

I may be able to help you better if you could elaborate a bit more on what you really want to accomplish.
0
 

Author Comment

by:IUAATech
ID: 24870829
Sure.
if you look at my SQL statement above, I have table named "people". In this table, the field univ_usr_id is updated manually a lot of times. This introduces errors where the same univ_usr_id will exist in more than one record when it shouldn't.

So, I want to automate this process of finding univ_usr_ids that exist more than once. I am looking to create a SSIS package that will run everynight. Provided the package finds a univ_usr_id that exists in more than one record, I want to send the information to a group of people here to correct.

Hope I am making sense.
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 24871860
If you're using an exchanger server for email, you should be able to set and send the email out right inside your SQL task by using the @@rowcount criteria.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:IUAATech
ID: 24871874
interesting. I will have to look that up.
0
 

Author Comment

by:IUAATech
ID: 24871932
Can you show me some code to e-mail the results of the query using a Execute SQL task?
0
 
LVL 8

Accepted Solution

by:
MartinCMS earned 500 total points
ID: 24872328
you can use something like what I have here and put it straight in your SQL task....

Declare @Date varchar(10), @Subject_Line varchar(50), @Message_Text varchar(500),
      @Email_To varchar(300), @BC varchar(100),
      @person_id Varchar(10), @firstname Varchar(10),
      @lastname Varchar(10) , @univ_usr_id Varchar(10)  

select       @person_id = person_id,
      @firstname = firstname,
      @lastname = lastname,
      @univ_usr_id = People.univ_usr_id
from People
inner join (SELECT univ_usr_id
      FROM People
      WHERE Person_ID NOT IN (SELECT Person_ID from Multiple_Sources WHERE Source in ('D', 'Z')) AND univ_usr_id IS NOT NULL
      GROUP BY univ_usr_id Having COUNT(*) > 1
) b on People.univ_usr_id = b.univ_usr_id

If @@ROWCOUNT > 0
   Begin

      Select @Date = convert(varchar(10),GetDate(),101)
      Select @Subject_Line = ('WARNING '+@Date)
      Select @Message_Text = ('UserID '+@univ_usr_id+' for '+@firstname+' are dupes.')
      
      select @Email_To =  'test@testmail.com'
      
      Select @BC = 'test@testmail.com'
      
      exec master.dbo.xp_smtp_sendmail
      @TO = @Email_To,
      @FROM = 'administrator@testmail.com',
      @BCC = @BC,
      @Subject = @Subject_Line,
      @Message = @Message_Text,
      @SERVER = '111.111.111.1111' --- server IP

   End
0
 

Author Comment

by:IUAATech
ID: 24872550
THanks.

So, I should select SQLSourceType as "Direct input" and use the code above the "SQLStatement", correct?

When I run the package, I get an error - please see the attached screenshot.

Can you please help me debug this?


SSIS-Error.JPG
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 24904459
Sorry, I have been away for while and just got back in the state.  Anyway, sound like you do not have the xpsmtp80.dll install in your SQL server.  Can you verify if it had been installed or not?

You should be able to find the xpsmtp80.dll in your SQL bin directory if it had been installed.
0

Featured Post

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.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.

760 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

20 Experts available now in Live!

Get 1:1 Help Now