October 24, 2005 at 8:15 am
Has anyone ever set up a DTS package so that it starts upon receipt of an email. I have a series of DTS packages that need to run only after a process at remote server completes. Unfortunately, the completion time for this remote process varies by an hour or so each day. I would like to start my DTS packages as soon as the remote server completes its processes and sends me an e-mail. Any thoughts or suggestions are appreciated.
October 24, 2005 at 10:15 am
I have faced a similar kind of situation like this. I resolved it like I will sent out a file to a particular folder and one windows process will be running, which will check for that process for every 15 mins. Once the file is there in that folder, it means that the remote process has completed its task then the windows task will invoke a vb exe, which will run the DTS job. Hope this might help you..
with smiles
santhosh
October 24, 2005 at 1:56 pm
Thanks for the reply; however, the only notification I will or can have is the email. Unfortunately, the remote server is owned by anothe company and they are not cooperative. I am stuck with what I have.
October 24, 2005 at 3:23 pm
There are extended stored procedures that allow processing of in-bound emails messages BUT you must be using Exchange and the Exchange Server must be in the same domain as the SQL Server.
From SQL Server Books OnLine:
How to use SQL Mail (Transact-SQL)
SQL Mail uses several extended stored procedures that are necessary for mail enabling. These extended stored procedures are included in a dynamic-link library, SQLMAP70.DLL, which is installed with Microsoft® SQL Server™ 2000.
To process e-mail messages manually
In SQL Query Analyzer, start a SQL Server Mail client session by executing xp_startmail.
To find the ID of the next unread message in the mail box, execute xp_findnextmsg.
To read a message or attachment, execute xp_readmail (using a specific message ID), and use the output variable in a SELECT statement to display the message in the result pane.
To delete a message, execute xp_deletemail (using a specific message ID).
To send a message or a query result set to specified recipients, execute xp_sendmail (with the query in the message body).
Stop the SQL Server Mail client session by executing xp_stopmail.
SQL = Scarcely Qualifies as a Language
October 25, 2005 at 10:10 am
Checking for email arrival via xp_readmail is definitely an option, a scheduled job that checks every 5 minutes or so for the email to have arrived would work just fine as long as you have SQL Mail up and running on your server.
Another alternative might be an Exchange 2000/2003 event sink - plenty of examples of how to process mail on arrival (e.g. mail from X, do this). Solution wouldn't be 100% SQL server but could do the trick (e.g. on mail arrival, connect to the sql server, execute sql statment (e.g. xp_cmdshell dtsrun...).
Joe
October 27, 2005 at 11:31 pm
Another option could be to use Outlook as your mail client (receiving the email). Outlook is completely programmable. You could use Outlook to send a file/notice to a specified directory to complete a solution proposed by Santhosh Kumar, above. Downside is that this has a couple points of failure.
Mike
January 18, 2007 at 1:17 pm
I want to do the same exact where based on a file in a folder I want to trigger the DTS package. Could you please tell me how it can be done. Thanks.
My email: m_chintamani@hotmail.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply