SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Run package when email is received or based on database value?


Run package when email is received or based on database value?

Author
Message
sdevanny
sdevanny
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 343
demin99 (7/10/2013)
b) based on a certain value in a database table?

I think this is possible.

1) Using execute SQL task select the value from the data base.
2) Use the value in the precedence constraint expression to check whether it is true or not.
3) Use loop to check the value.


We did something very similar in a past project. Another process needed to run and complete successfully before our process could be run.

The process we were dependent on was expected to be finshed by 20:00 so that is when we would start our process - which would run via SQL Agent every 15 minutes untill 02:00 the next day.

1) Like demin99, we would use an Execute SLQ task to select a value from a table predicated on the day's date and assign the single row resultset to a variable. There were 3 possible values - "Running", "Staged"and "Complete" (though now in hindsight I suppose there were 4 as there might not have been a record at all in the table for that day's date! But as you'll see NULL would not evalutate to true either.)
2) Again like Demin 99, we would use the variable in the precedence constraint between the SQL Task and the Foreach Loop (or whatever the next component needs to be in your package) and evaluate that the value = "Staged". If it did not evaluate true the package would end there. If it evaluated true the package would continue to process the data.
3) After successfully processing the data we would update the value in the table to "Complete".

Hope this helps!
justinhca
justinhca
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 60
This might be possible with this SSIS component from CozyRoc.

http://www.cozyroc.com/ssis/receive-mail-task

This should allow you to detect when a target email is received,
and then you have the full power of SSIS to run another package.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search