September 24, 2011 at 8:05 am
I have a question about jobs execution in sqlserver B.I. 2005 platform.
I know that I can schedule a job that exec a sys in the platform, for example a batch execution, but I’m trying to launch a sys based in a event.
The event is an e-mail. When I receive a e-mail in outlook, I would like to take the attached file move it to a directory and exec a sys (SSIS pack) that use this file like input data in a ETL process.
Is that possible using sqlserver B.I. 2005 platform?, do I have to make a previous step?, I also can use MOSS 2007. I suppose that MOSS 2007 can have an agent to receive the e-mail, extract the attached file and call the sys of SQL Server.
I will prefer to make the whole process in sqlserver B.I. 2005 platform.
Any idea or suggestion will be greatly appreciated.
Thanks in advance,
September 26, 2011 at 8:28 am
What you can do is have an event trigger in outlook which, upon receiving an email, will execute a batch file that will in turn execute a DTEXEC command line trigger to launch the SSIS task.
I haven't personally done this before so I can't give you an exact solution on how you can set it up, but the gist of it is:
1. Create an Outlook Macro (Tools -> Macros -> VB Editor or ALT-F11. Make the macro move the file attachment into a folder of your choice
I haven't tested this script but you could try it out, might work for you.
http://www.rondebruin.nl/mail/folder2/saveatt.htm
2. Create an Outlook Macro that will launch a DTEXEC command. Depending on security settings, you might be best off executing a batch file which in turn executes DTEXEC, instead of trying to run DTEXEC directly from a shell in the macro.
3. Create a rule which will get launched when you receive an email. Have the rule launch the macro which moves the file to your specified folder, then launch the DTEXEC macro.
Depending on how frequently this is happening, you might want to consider instead having the rule "queue" up a task, and then, say, once an hour, running the DTEXEC macro against all the files in the folder, instead of having it run each time they come in.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply