Running SSIS Packages in Denali

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.)

Quickly adding new columns to tables in Denali

More and more, I am starting to really like what I see in Denali.

The other day, this headline caught my eye: "ONLINE NON-NULL WITH VALUES COLUMN ADD IN SQL SERVER 11".

In short, it will be possible to quickly add a new non-nullable column to a table. This operation is quick because it is a metadata change. Prior to this new feature, it would be necessary to wait until the server physically manipulates every row in the table. This manipulation would involve exclusive table locking. It would possibly include splitting pages and lots of disk I/O.

This feature would be an absolute boon in a 24x7 system and it should be handy for avoiding lengthy schema upgrades on any system with large tables.

You can read about this feature in detail here.