SSIS In Denali. Sexy! But Substance?

In Denali, SSIS has been given a face lift, and it looks pretty sexy, that's a given. But for my first post on the subject, I wanted to look instead at some of the new functionality to see how it performed, and one of the biggest new features in terms of functionality, is the Integration Services Database, so...what does it give us?

Well, I started by creating a simple SSIS package in the VS 2008 Shell. (We have been promised VS 2010 shell for RTM!) I used the new Source Assistant and Destination Assistant to create a data flow that just pumps rows from one table to another.

Then, in SSMS, I created an Integration Services Catalog, by right clicking on the Integration Services folder. Two points of note here were that it made me enable CLR, and it enforced a complex password...

 ...Then I created an organisational folder, under the catalog...

...I then tried to import the package from the context menu of the folder. It did not work, but to be fair, I am using CTP1! So I went back to BIDS and used the new Deployment Wizard (I'll blog about this soon, because it is quite good, and very, very different!) and deployed the project to the SSIS database. Denali uses the concept of deploying projects, much more rigorously than previous version, as many of the new features (such as parameters) are based at the project level.

After deployment, The project and package appeared under my folder. Right clicking the package allowed me to run a validation process, shown below. I could also have done this at the project level...

...Everything seemed Ok, so I ran the package. This gave me a jazzed up "Progress Window", which furnished me with status information in real-time...

...After package execution, a right click on the Catalog, allowed me to click the operations button, which gave me a history of all the actions that I had performed so far...

...I then found a whole raft of extra information in the SSISDB Database, that was automatically created when I created the catalog. Some of the most interesting tables included Executions table, the Catalog_Property table and the Data_Type_Mapping table, all shown below...

...Finally, a quick look at the Properties of the Project, revealed the Previous Versions Window. This allows us see, and easily roll back to a previous version of the package, in the event that you had not tested your code properly! (Not that would every happen of course!)...

...So in conclusion, there will be many more Denali SSIS posts to come, but I'm sure you can tell from this post alone, that DBAs are going to love it!!!