Execute package from SSIS catalog programmatically in SQL Server 2014

0
votes

Does anybody know if Microsoft.SqlServer.Management.IntegrationServices can still be used to manage remote execution of SSIS packages in SQL Server 2014? Remotely running a project-deployed SQL 2012 SSIS Package suggests that this worked in SQL Server 2012.

MSDN page Running and Managing Packages Programmatically also lists the above mentioned namespace for working with packages (although it doesn't state if it works with remote packages).

My packages are stored in the SSISDB catalog which need to be triggered from a web application running on a different server. When I run the program in my dev environment with SSIS installed locally everything works okay but in production I am getting the following error:

Exception type: FileNotFoundException Exception message: Could not load file or assembly 'Microsoft.SqlServer.Management.IntegrationServicesEnum, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

As this dll is not referenced by my project, I am wondering if the production server requires SSIS or components of installed in order to work correctly.

I have referenced the following libraries in my project:

  • Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc.dll
  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.Management.IntegrationServices.dll
1
c#sql-serverssis
Are you certain you were actually running it remotely? Because this link says you need to utilise SQL Agent (or by extension of logic some other remote execution utility) msdn.microsoft.com/en-us/library/ms403355(v=sql.110).aspx. I suspect that you've always been running it on the client (i.e. the web server) – Nick.McDermaid
That link describes a different approach and is not using the API I mentioned (Microsoft.SqlServer.Management.IntegrationServices namespace). That article doesn't take into account packages stored in the SSISDB catalog. – lintunen
Thanks for the clarification! – Nick.McDermaid

1 Answers

0
votes

I was never able to figure an answer out to my original question so I ditched the API and went with stored procedures instead, using catalog.start_execution.