How can I handle emailed job control files for SQL Server?

Question

How can I detect control files from any mailbox, scan that file, decrypt it and queue it as a job using job manager in SQL Server?

Answer 1

You will have to use .net script for this. There is no specific SSIS task for reading incoming emails and taking data/config file/jobs from the email.

Alternately you can write a small macro or vb.net script and attach it to Outlook which will strip the file from email and dump it in some folder or directly add it to SQL job schedule. May be you can use a SQL job frequently looking in the specific folder for files to process. This part can also be done using SSIS. But some level of coding is necessary.

Then if you have web service access to your mail box you can use a web service source to access the file and use a Execute SQL task to add the job. This way you may be able to avoid .net coding.

There may be many more ways. These three I got after I started typing an answer. So explore a little bit more.