I need to remotely execute an SSIS package located in the SSIS Package store from a C# .net application.
Application is located on a separate server, integration services cannot be installed on the app server.
I have the functionality working on my dev machine just using the file system method because I installed integration services to get it running. Now I need to flip it to call the package remotely.
Right now I'm using the following:
pkg = app.LoadFromDtsServer("\loadfile", "DBServer", Nothing)
But I get the following error:
"Connecting to the Integration Services service on the computer "DBServer" failed with the following error: "Access is denied." By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service."
Certainly the user running this application in production will not be granted such rights on the database server. How can I make this run as a sql user? Is this the right way to go about this? There are other options, I know. The package does not absolutely have to be stored in the package store, but I believe this is best practice.
I should also mention that the application passes in a file name via a package variable, so any solution needs the ability to do that as well. I'm not sure how to do this if I create a job to run the package.
If you are passing parameters from the .NET application to the SSIS package, check the following link for options:
How to execute an SSIS package from .NET?
If no parameters are being passed, then an easier approach would be to add the SSIS package to a job and then call the job from the .NET application. The following link provides a code example:
http://www.roelvanlisdonk.nl/?p=1736