For those SQL Server and development shops that have built a lot of SQL Server Integration Services (SSIS) packages out there and have utilized Visual Studio Team Foundation Server or Visual SourceSafe to provide version control over those SSIS packages, you will be glad to learn about the new versioning capabilities in SQL Server 2012, code named “Denali”. In fact, when I’ve been trying out the new version of SSIS, I have found the overall deployment experience to be a huge step forward over the 2005/2008 deployment model with many advances in terms of configuration management and deployment to a server.
For today, I’m just going to focus on version control, because it’s been difficult in the past to get something working that is easy to configure and get working. And it is very nice to see this now natively in the product. In the picture below, you will see that a new node is available in the Object Explorer tree in SQL Server Management Studio (SSMS) that is set aside just for Integration Services. You can still log in directly to the SSIS service from SSMS, but that is only going to show SSIS packages that were deployed in the old, more arduous model, which you will see referred to as “Legacy Model” in BIDS. The new model is simplified and wizard-driven.
The first thing you have to do is to set-up an SSIS “Catalog” which stores SSIS “Projects” that then store SSIS “Packages”. Now when you right-click on the Projects node in the tree, you will see a “Versions” menu item which launches the screen below. Note that on this screen you will see the project versions, which one is active, descriptions and the option to rollback to an earlier version with “Restore to Selected Version”. You will see 2 packages in my project on the screenshot to the left. Notice that “Project” is essentially project-level from BIDS, which is now Visual Studio 2010 in Denali. When I rolled back to my initial version of the project, I lose the 2nd package in my project. Why is that? Because I added a 2nd package in my project in rev 3 of my project. This is a project-level version control feature.
But they’ve really done a nice job with these capabilities. I found them intuitive and easy to pick-up.
What I also want to show you today is how to deploy your SSIS packages and projects to this new Catalog feature in SQL Server. Again, notice that the Catalog from the above screen shot is available to DBAs from the SSMS database connection. You do not need to log into an SSIS service connection here. When I am in BIDS (VS2010), I just right-click on my project in Solution Explorer, right-click and choose Deploy. The dialogs that you will walk through in the wizard will allow you to deploy the new project format or to projects in the catalog. When you are making changes to packages and projects in SSIS that you’ve already deployed to the server, you will see a notification that you are going to overwrite the existing deployment. You just accept that and continue through the deployment process. SSIS will handle the work for you of retaining the previous version and that will now appear to you in the Versions dialog from SSMS. This allows you to rollback to that previous version if you need to. That’s all there is to it.
Lastly, you will see on the right-click menu from the BIDS project level, that you can convert between legacy deployment and the new project-based deployment models. This worked for me perfectly with my new Denali-based packages but I haven’t yet had a chance to try it on SQL Server 2008 SSIS packages.
Enjoy! Best, Mark
