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
cindypsych
cindypsych
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 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.
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23458 Visits: 5314
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
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70104 Visits: 40923
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2361 Visits: 907
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.
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23458 Visits: 5314
So short answer is there is no really good way to support what you are wanting to do..

CEWII
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2361 Visits: 907
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.
cindypsych
cindypsych
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 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.
aaron.reese
aaron.reese
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2361 Visits: 907
Konesans file watcher allow the package to be permanently running and only triggers processing when the file arrives.
HakunaMatata
HakunaMatata
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1202 Visits: 491
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



Rolleyes
demin99
demin99
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 509
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.
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