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

Question

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

cindypsych

Old Hand

Points: 330
More actions
June 28, 2013 at 12:50 pm

#276786

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.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

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 SSC Guru Points: 323504 More actions · Answer 2

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 SSChampion Points: 13498 More actions · Answer 3

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 SSC Guru Points: 102296 More actions · Answer 4

So short answer is there is no really good way to support what you are wanting to do..

CEWII

aaron.reese SSChampion Points: 13498 More actions · Answer 5

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 Old Hand Points: 330 More actions · Answer 6

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 SSChampion Points: 13498 More actions · Answer 7

Konesans file watcher allow the package to be permanently running and only triggers processing when the file arrives.

HakunaMatata SSCrazy Points: 2610 More actions · Answer 8

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 LastRunScheduledRunJobActive Frequency

ANULL07/10/2013 11:00:00 AM10:45:00 AMYDaily

BA07/09/2013 11:35:00 AM11:30:00 AMYDaily

:rolleyes:

demin99 SSC Eights! Points: 927 More actions · Answer 9

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.

sdevanny Say Hey Kid Points: 698 More actions · Answer 10

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 SSC Rookie Points: 35 More actions · Answer 11

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.