CODEWITHSUNDEEP
sql-serverssis
Martin Brandl
Martin Brandl

Reputation: 59031

Retrieving and executing available SSIS Package remotely

I want to develop an ASP.NET site where a user can select a SSIS Package, provide some parameters and executes it.

I read about Loading and Running a Remote Package Programmatically so it seems like the only way to run a SSIS Package from server x invoked remotely by server y is to either use SQL Server Agent or writing a handcrafted webservice. Is this correct or do I miss something?

If I choose to use SQL Server Agent, do I need to create a job for each package I want to execute? Also, am I able to read all available SSIS packages remotely?

Upvotes: 0

Views: 58

Answers (2)

billinkc
billinkc

Reputation: 61269

Yes, you missed the other called out approach of

or a remote component to start the packages on the remote computer

To me, that would be something like PsExec

The challenge with SQL Agent as you'd discover is two fold. The first is that only one instance of the job can be running at one time. Perhaps your web page only allows/assumes one user is running FoobitzLoader.dtsx at a time but generally web sites allow more than one user to do an action.

The second challenge, and maybe this is desirable, is that the running of an Agent job is asynchronous. The process looks like

  • Please start job X
  • Ok it's started

So now you're polling the msdb.dbo.sysjobhistory (I think) table looking for the process to be complete. Maybe that's what you want. Maybe not. The invocation of the SSIS package through PsExec will be like it is "normally"

  • Please start job X
  • Ok it's started
  • We're validating
  • We've loaded rows
  • We've loaded more rows
  • ...
  • Package completed successfully

That may or may not be a good thing as your users might close the web page whilst the process is running and that kills the dtexec process. Not sure your use case but that's probably something you'd want to guard against.

Upvotes: 1

Joe C
Joe C

Reputation: 3993

If you are using SQL2012 or later you can use the catalog. Doing a search for SSISDB will get you plenty of articles.

This way your web application can query the catalog to find out what packages and parameters are available to populate your form(s). Then you can issue a TSQL statement from your application to start the package running.

Upvotes: 1

Related Questions