How to email results from a query using SSIS

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

IUAATechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MartinCMSCommented:
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
IUAATechAuthor Commented:
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
MartinCMSCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

IUAATechAuthor Commented:
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
MartinCMSCommented:
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
IUAATechAuthor Commented:
interesting. I will have to look that up.
0
IUAATechAuthor Commented:
Can you show me some code to e-mail the results of the query using a Execute SQL task?
0
MartinCMSCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IUAATechAuthor Commented:
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
MartinCMSCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.