0

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.

EL_Beau
  • 1
  • 1
  • 3
  • I had success at doing this by creating a job that calls the package, and starting the job with a sql command from .net. – Tab Alleman Jun 10 '15 at 19:41
  • Would you mind sharing if you were passing a variable using this method? If so, how did you go about doing that? – EL_Beau Jun 11 '15 at 18:41
  • Certainly. I created a "JobHistory" table that contained a column for every variable that needed to be passed to the package. The .net app would run a sql command that inserted a row in that table, and then it would kick off the job. The first step of the package was to read the variables from the most recent row in the table. The last step was to update the table and set a "JobResult" column to "success" or some other message. – Tab Alleman Jun 11 '15 at 18:49
  • Brilliant. Were you using exec msdb.dbo.sp_start_job to kick off the job? – EL_Beau Jun 11 '15 at 19:00
  • Pretty sure that was it, yes. I believe I called it from the same stored proc that populated the JobHistory table. I vaguely remember that I had to do something to handle concurrency, but I don't remember what it was. – Tab Alleman Jun 11 '15 at 19:02

2 Answers2

0

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

Community
  • 1
  • 1
user3662215
  • 526
  • 4
  • 12
  • Thanks for your response. we are passing parameters (file name) and we cannot install integration services on the application server (see above) so these won't work, unfortunately. – EL_Beau Jun 11 '15 at 18:40
0

you can use stored procedures in the SSIS catalog to deploy the project and execute the packages

https://msdn.microsoft.com/en-US/library/jj820152.aspx?f=255&MSPPError=-2147217396

Joscion
  • 49
  • 6
  • Unfortunately we're using sql server 2008 and are unable to use catalogs, so this isn't an option- thanks though! – EL_Beau Jun 11 '15 at 18:38