0

We have SSIS package deployed to SSISDB: We can to execute from SSMS (while providing parameter inputs), as well as script this execution in TSQL. We can run the SSMS standard reports for SSISDB to see progress and logs, and we can query the SSISDB.catalog.* views for the same.

But I have a request of SSISDB being orchestrated, not from SQL Agent, but rather from an external Windows Workflow Foundation (WF) that manages several web-services.

What is the possible (and best) ways of calling SSISDB packages within a service-oriented architecture?

E.g. would it be an idea to write a rest-api, that can pass parameters down to the ssis-packages, execute those, and return a response with the package status/result. Or am I on the wrong track?

Martin Thøgersen
  • 1,538
  • 18
  • 33
  • 1
    I think your on the right path. Either develop some kind of powershell script or/and use the dtexec utility where you can parameterize and return status. Documentation link here: https://learn.microsoft.com/en-us/sql/integration-services/packages/dtexec-utility – SqlKindaGuy Oct 10 '17 at 12:33
  • There's a whole execution API that you can use to run packages. https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-run-tsql-ssms?view=sql-server-2017 I do suggest you rethink running SSIS packages from a service oriented architecture - a requirement to run packages on demand often means you have some functionality in the package that isn't normal ETL functionality, and might be better achieved with a different tool. Also consider what happens if two people try to run the same package at the same time, – Nick.Mc Jun 26 '18 at 23:02

0 Answers0