SQL Agent is tricky because:
1. If you have only one SQL Agent job being your 'host' you can't run mutliple jobs in parallel
2. If you want to have a non power-user trigger a SQL Agent job from some kind of client you need quite a bit of custom plumbing to get it all to work
Here's another idea: use BULK INSERT instead of SSIS.
But anyway all of these various methods of loading data have already been covered ad nauseum elsewhere.
We load end-of-day transaction files from multiple banks using the same package. We have multiple SQL Agent jobs and use the /config parameter to provide a different config file for each bank. Package has no problems executing in parallel.
As for non-power users managing the jobs, that's done from a web-page that calls sp_start_job. The app pool identity of the site has the permissions required to manage the jobs.
Federico Iori (2/6/2013)
2) you call xp_cmdshell to execute your package. This won't be usable on systems that dis-allow usage of xp_cmdshell.
How could I launch from T-SQL a SSIS package ? The only way I know is using xp_cmdshell.. purpose of the article was a T-SQL stored procedure able to launch a SSIS package, and this requires xp_cmdshell
That is precisely the wrong approach to take. Forget the gaping security implications of having to keep xp_cmdshell enabled for the whole server
. Why on earth would you contemplate launching an executable within the memory space of the Database Engine, consume it's resources and possibly adversely affect load performance?
PhilCart, your idea is :
T-SQL stored procedure should
1) Create with dynamic SQL and launch syncronously a job that launches BULK_TRANSFER_DATA.dtsx
2) Create with dynamic SQL and launch ( possibly syncronously) a second job that launches the SSIS package generated at point 1 and saved into a specific folder
3) Delete the 2 jobs and the created dtsx
It is possible of course, maybe safer than xp_cmdshell, but requires some coding ...
Would you try to provide it ? My article does not indicate the way, but offers a working solution..
Moreover, as long as I know, a SSIS package can create another SSIS package only to the file system ...