Executing a SSIS package on a remote server

Recently, I was faced with the problem of executing a SSIS package on remote server.
No doubt it is a simple problem but for one reason or another I faced a heck of a issues in this, so after finally able to execute the package I decided to blog my solution.

Coming back to the problem, the MSDN page for the same (http://msdn.microsoft.com/en-us/library/ms403355.aspx), shows two ways to do the same.
First is via the SQL Server agent, and the second is via the Web Service. If your application is not only interested in just executing the package but in also knowing about the execution status of that package (which I guess almost all applications would do), then package execution using the SQL server agent won't be that handy.

The next way stated in the MSDN is to execute a package on remote server is via the web service.
Well, I tried giving appropriate permissions to my web service by making the impersonation property true in the web.config file, but still the I was not able to successfully execute the package. Tried searching everywhere for a solution to this problem in vain. Finally, I figured out that the web service was not able to successfully execute the package because SSIS creates multiple threads that run under the process’s user account and as such impersonation gets ignored.

Another option one can use to execute a package on a remote computer is using the PSExec tools (on Windows Versions without UAC) but this solution is also not very handy when your application is also interested in getting the various warning messages generated during the execution of the package.

Finally I tried to execute the package via the WCF services and was very easily able to execute the package. Executing the package via the WCF service is by far one of the simplest ways to do the same. You can very easily make a standalone host and can use it to execute the package on the remote machine. Writing the standalone host and it's client application is a damn easy task and there are lot's of tutorials for the same on the net.

So if you are considering executing a package on a remote machine, I would say go with the WCF service way. It's a damn easy way and also gives you the flexibility to extend your application in whatever way you want. In addition, this does not requires one to code a lot of lines and thus do mistakes :).

Enjoy the Life!!!

Good One

A jock and a geek applying for the same job.
The boss said, "Boys, you need to take a test before you can get this job."
So they took the test and the next day they came back to see who the boss chose. "Well," he said, "Both of you got the same score except I'm going to choose the geek."
The jock complained, "Don't you think that's prejudice or something?"
"Well," the boss said, "Let me tell you what happened. Both of your papers were right all the way through until the last question came up, and the geek answered 'I don't know,' and then when I looked at your paper, you answered, 'Me either'.