Running a SSIS package from the web.

I have some data to import into SQL Server so I done it manually in SQL Server Management and save the process as a SSIS package. It was save as "SQL server".

It took me quite sometime to find out where is it stored. It was found in MSDB database. I actually have to start SQL Management under the "Integration" service to see that.

The bad news is that I can't edit it at all.

Ok, then I save it as file. and I am able to edit it with SQL Management "file open" method. And I actually noticed that I cannot create new SSIS package. I must have missed some steps in the "new Project" creation process.

Back to the original issue. Now that I have it created, I want to run it in an ad hoc way rather than schedule it. I then realize that I could not do it in Stored Procedure. It require XP_CMDSHELL to be enabled. According to internet, this is not a good security practice.

Other netizens recommended to create a SQL job to run the SSIS but don't schedule it. A Stored Procedure is created to run the job by using "sp_run_job 'myjob'".

I then create a php page to run the stored procedure by its name.

In this way, I could actually execute the SSIS package in an ad hoc way.

My question is - why have to make it so complicated to run a ssis package?