Trigger DTS package by Email

Is there a way to run my DTS package upon receipt of a certain email?

XP OS
webdorkAsked:
Who is Participating?
 
Chris MangusDatabase AdministratorCommented:
Once you have SQL Mail set up and running you can send a query like 'EXEC runMyPackage' to execute a stored procedure called runMyPackage.  In that SP you use some syntax like:

Declare @SQL nvarchar(400)

Set @SQL = 'DTSRun /S{myServer} /U{username} /P{password} /N{name of my DTS Package}'
Exec master.dbo.xp_cmdshell @SQL, no_output
0
 
Chris MangusDatabase AdministratorCommented:
Are you talking about using SQL Mail directly through SQL Server?
0
 
webdorkAuthor Commented:
idunno...

I want to run a dts package when a particular email arrives. I've heard SQL Mail is a bit iffy, but I'm listening...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Chris MangusDatabase AdministratorCommented:
If you're talking about SQL Mail then sure, you can fire a DTS package that way.  If you're talking about receiving email on your personal email account I think you'd have to write a process that would go through your inbox periodically and then, if it finds a certain email it would use the DTSrun.exe command line utility to run your DTS package.
0
 
webdorkAuthor Commented:
OK how do i go about using SQL mail
0
 
itdrmsCommented:
You can set up a rule in Outlook and upon receipt of your email criteria perform a custom action, start an application, or run a script.
0
 
webdorkAuthor Commented:
I see how to send a trigger with SQL Mail. but i'm not sure how to receive an email in SQL Server
0
 
Chris MangusDatabase AdministratorCommented:
Here are a couple of great links for learning about SQL Mail, how to configure it, and how to use it.

http://sqljunkies.com/Article/0A1F961B-8E50-4E76-8FCE-31A0F800FCC7.scuk
http://www.databasejournal.com/features/mssql/article.php/3293301
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba01/html/SQLServerE-mail.asp

Please note, Custom Actions in Outlook are 3rd party tools that are installed to work with Outlook.  It may be very interesting to explore running a script through a rule.
0
 
itdrmsCommented:
If that aproach will help you, here's an extract from one of my VBS that accesses SQL (not that I'm taking original credit -- but it's in my script and I know it works) -- you can EXEC as listed above by cmangus

  Dim oConn, oComm
  set oConn= createobject("ADODB.Connection")
  Set oComm = createObject("ADODB.COMMAND")

  oConn.Open ("Provider=sqloledb;" & _                        
              "Data Source=servera\servera;" & _
              "Initial Catalog=" + sSQLdb + ";" & _
              "Integrated Security=SSPI")
  oComm.ActiveConnection = oConn
  oComm.CommandText = "EXEC ..."
  oComm.Execute
  oConn.Close
  Set oComm = Nothing
  Set oConn = Nothing
0
 
Chris MangusDatabase AdministratorCommented:
I just took a look at Outlook rules firing scripts.  Very nifty approach itdrms!
0
 
itdrmsCommented:
thank you cmangus, I'm familiar with rules to automatically respond to our users who incorrectly reply to our unmonitored admin email account, and I use a rule to copy my backup on any automated emails from the admin account, so I'm covered if I'm out of the office -- I don't have need to execute a script -- but knew it was an option.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.