Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Run package when email is received or based on database value? Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 12:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 23, 2013 1:13 PM
Points: 8, Visits: 25
Hi. I've seen a couple of questions/answers here describing how to use a script to check a folder and see if a file exists, then run the SSIS package if the file is there.

Does anyone know how to do the same thing either a) when a specific email is received or b) based on a certain value in a database table?

I am trying to automate a process that can't necessarily be run at the same time each day. It is dependent on other processes which may take more or less time. I receive an email when that process is done. I could arrange for the company to log the completion in a database table. But I don't think they would have the ability to put a file in a folder on our network.

Anyone have any creative suggestions for my dilemma?

Thank you.
Post #1468682
Posted Friday, June 28, 2013 1:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Using Database Mail there is no way for SQL to directly receive email and SSIS does not inherently have the ability to check for email. So without some external tools that isn't a manner readily available.

However you could probably pull off then run of a package based on a value in a table or the insert of a value. I can think of a couple different ways to accomplish this. One of the easiest would be to create a custom error, create a job to run the package, create an alert to watch for the error and then call the job. Then whatever process that determines that it is time to run the job just execute a RAISERROR with the custom error code and boom job runs.

CEWII
Post #1468687
Posted Friday, June 28, 2013 1:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
I've got a CLR i created that does a proof of concept reading of mail from a POP3 server; i'm slapping together an article for SSC on it;

The problem is, it's the carpenter and the nail syndrome; I have a hammer(TSQL), and then every problem looks like a nail....

TSQL might not be the best place to implement this, especially if the email has one or more attachments, and you need to save them so they can be processed by your SSIS package.
(i have not got my CLR to download & Save Attachments yet)

my mail server, for example, might take 10 seconds to a couple of minutes to finally answer a request for getting the list of mail; that kind of waiting doesn't belong in a TSQL process.

an .net app or something that reads the mailbox every x minutes , saves attachments to disk sounds like something that should be done outside of TSQL, even if you can bend a solution to your will.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1468692
Posted Tuesday, July 2, 2013 8:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:48 AM
Points: 386, Visits: 623
depending on the version of SQL you could set the database up with an Exchange email box and have it monitor its emails on a scheduler but this would still be pull notification rather than push notification.

you could set up the exhange account and a copy of Outlook on the server with a rule to trigger the package when the email arrives but this relies on Outlook being open and is likely to be stopped if the server get rebooted.

you could use message broking

All of the above could be a support nightmare

Can you get your package called as a child package from the process that generated the email.


Post #1469544
Posted Tuesday, July 2, 2013 8:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
So short answer is there is no really good way to support what you are wanting to do..

CEWII
Post #1469552
Posted Tuesday, July 2, 2013 8:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:48 AM
Points: 386, Visits: 623
Have you investigated Konesans File Watcher ( you can get it from here)
http://www.sqlis.com/sqlis/post/File-Watcher-Task.aspx

It listens for files arriving in a directory. You could use any process you like to indicate that the package should start and have the file watcher pick it up.

Post #1469559
Posted Tuesday, July 2, 2013 8:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 23, 2013 1:13 PM
Points: 8, Visits: 25
Thanks, all. These are really intriguing ideas but it sounds like there's no good way to do what I want. I think I'll have it periodically check a folder for the existence of a file or something.
Post #1469575
Posted Tuesday, July 2, 2013 8:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:48 AM
Points: 386, Visits: 623
Konesans file watcher allow the package to be permanently running and only triggers processing when the file arrives.

Post #1469576
Posted Wednesday, July 10, 2013 4:24 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:31 AM
Points: 918, Visits: 489
See if below way works for you -

The concept I worked on long ago in one of my assignment was we created a control table which will maintain list of batch jobs, predecessors, scheduled run, last run and whether jobs are currently active or not.. We then created a SSIS package that will keep executing every 5 mins and scan this control table to see which job needs to be pulled in to execute. Any job whose predecessor has run for current date and has scheduled time arrived/already passed, we used to kick off that job.

In your case, you can have them update last execution time into the control table and your SSIS package running every 5 mins will do the job of triggering the next package which qualifies execution criteria. This control table can also give you flexibility if you want to hold any run by just updating flags etc.. you just need to design control table as per your requirement and use conditions in SSIS package.

Here is sample table that we used to do -

Job Name 	Predecessor 	LastRun			ScheduledRun	JobActive 	Frequency
A NULL 07/10/2013 11:00:00 AM 10:45:00 AM Y Daily
B A 07/09/2013 11:35:00 AM 11:30:00 AM Y Daily



Post #1472043
Posted Wednesday, July 10, 2013 1:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:27 PM
Points: 25, Visits: 339
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.
Post #1472318
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse