June 5, 2013 at 2:58 am
Hi everyone
I am new to the world of SSIS so apologies if this is a basic question !!
I have been given a task to see if it is feasible to create a SSIS job to check a table and if it has any rows in it then fire an alert (email). If the result set is empty then no alert is needed.
I dont have any further details as of yet just the general info. Im sure this must have been a need for other people at some time.
Any info would be greatly recieved. Thanks
June 5, 2013 at 3:04 am
That's nice & easy.
Here's one way:
1) Create an integer package variable - say NumRows
2) Create an ExecuteSQL task to get the table's rowcount & return the result into NumRows. (You can configure the ExecuteSQL task to do this.)
3) Use precedence constraints to direct the onward logical flow
a) If NumRows > 0 go down the e-mail path
b) If NumRows == 0, do something else (or do nothing at all)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 5, 2013 at 3:31 am
Thanks Phil - that sounds great.
I have just had some more info from one of the developers who is settign this up and feel my original question may need a rethink.
What he is trying to do is not query a table to see if it has rows. He has told me the table will always have rows but they need to see if it has 'relevant data'
To that end he is wrting a Stored Proc to find out if this is the case.
If the stored procedure returns , say a value of X to verify that the table has the required data ( again this is not a test to see if the table has rows but a test for valid data), can we use a SSIS job to then either send the alert or else 'do nothing'
Is this type of test/validation feesible within SSIS ?
Thanks
June 5, 2013 at 3:38 am
No problem - it's pretty much the same as I described in my previous post.
The ExecuteSQL task will run the proc.
The proc will return a result which can be stored in an SSIS package variable.
The value in the package variable can be used to conditionally 'decide' which path the package follows.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 5, 2013 at 3:54 am
Phil thats fantastic. Thanks for you advice - it sounds like something as newbie to SSIS i should be able to handle - at least i know now it can be done.
🙂
June 5, 2013 at 4:19 am
No problem & post back with any specific questions about the implementation. It takes a while to get to grips with the SSIS way of doing things.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 5, 2013 at 4:37 am
OK Phil will do. I've got a week or 2 to implement this so what this space. Thanks
pj:-)
August 28, 2013 at 5:05 am
Hi Phil
Its been a while since the last post as this got put on the backburner....however i am back onto it now and was wondering if you could help again.
I am not that familiar with the steps you talk about.
I have created an SP called ReturnValue.
I have dragged an execute sql task into the Control flow and also an send email task underneath and joined them up with the 'on success' predicate.
If the sp returns 1, then I want the email task to run, if it returns 0 I don' t want the email task to run.
What are the steps i need to edit in the sql task ?
Is it all under the General tab or do i need to go into the Parameter Mapping, Result Set, and Expressions tabs ?
If you need any more info please let me know...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply