I was skimming SSIS Logging in Denali and I noticed that the author used a feature that I had heard was going to be in Denali, but had forgotten about.
You can run a package using straight-up Transact-SQL.
Developers have been asking me for this ability for four or five years. Since it wasn't available, we have tried and/or implemented all sorts of stuff. Things like:
- Running packages via SQL-CLR. (Microsoft recommends that you don't do that.)
- Running packages via a call to a web service running on IIS that is co-located on the same server as SQL Server. (That is a lot of extra work, installation and configuration. It is more stuff that can break and it is more code to maintain.)
- Setting up a job and letting developers have all sorts of permissions just to test a package on a server. (That is more configuration to do and you are going to wind up with jobs whose purpose has been forgotten as developers transition into and out of projects.)
Denali looks better and better to me all of the time.
(Plus, all of that logging is good too.)