By: Arshad Ali | Comments | Related: 1 | 2 | 3 | 4 | More > Integration Services Development
Problem
I heard there are couple of enhancements in SQL Server 2012 for SSIS. What are these enhancements and how does improves the usability, productivity, manageability and performance? Check out this tip to learn more.
Solution
SQL Server Integration Services (SSIS) first appeared in SQL Server 2005. SSIS is a successor of DTS (Data Transformation Services) and was completely rewritten with a new architecture from DTS. Since SQL Server 2005, SSIS has been improved and enhanced in each subsequent SSIS release. Though the enhancements were quite significant in SQL Server 2008, there were not that many enhancements in SQL Server 2008 R2. Now the good news for SSIS developer is SQL Server 2012 has several new enhancements that I will cover in a series of upcoming tips.
In the first tip of the series, I am going to talk about some of the UI or usability changes which improves the productivity of a SSIS developer. For the new SSIS Project Deployment Model, refer to these tips:
- SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2)
- SSIS Package Deployment Model in SQL Server 2012 (Part 2 of 2)
SQL Server 2012 Undo and Redo Features
Very often I had difficult a time creating SSIS packages. Unfortunately, when I did something wrong and wanted to revert the last change, I was unable to do so. It was very frustrating. As an example, sometimes I unintentionally deleted a task, which I wanted to get back. At times, I had to revert back to the SSIS package version in the VSTF version store. This was all because we did not have any way to undo or redo our changes. Now with SQL Server 2012, we have this feature in the SSIS designer.
As you can see in the image above, you can now use Undo or Redo commands using the menu bar, toolbar or by pressing CTRL+Z for "undo" and by pressing CTRL+Y for "redo".
The designer now tracks all the actions that you perform, which you can see in the image above and below. You have the ability to Undo or Redo as many actions as you want.
SQL Server Integration Services Toolbox Enhancements
Now the components inside the toolbox have been placed in few more categories (Favorites and Common) for ease of use. These categories are not fixed and you can customize it as per your need. For example, you can place all your frequently used components inside "Favorites" section which appears at the top of the Toolbox as shown below:
To move a component to some other location/category, right click on the component and click on the location where you want that component to be placed as shown below.
On a final note, one really good aspect of the enhanced toolbox is the information section on the bottom of the toolbox. This area displays details about selected component and provides an option to directly jump to the documentation for that selected component. This is really useful for someone new to SSIS development and/or someone that does not have prior knowledge about the component.
SQL Server Integration Services Designer Surface Enhancements
When working with the SSIS designer, one of the first things that you will notice is the look and feel changes of the component placed on the designer area. It looks more elegant as well as easy to maintain and align.
Although it was possible to zoom in/out in the designer surface area in earlier SSIS versions by holding the CTRL key and scrolling the mouse, there are some enhancements in SQL Server 2012. In SQL Server 2012, the aforementioned zooming is supported as well as the addition of a scale on the right bottom of the designer with a vertical slider to zoom in/out on the surface area. There is also a button on bottom of the scale which you can use to fit all the content within the designer surface/window. This setting will automatically adjust the size to make the objects fit on the screen.
There have been already couple of commands for formatting the layout of package's components. There is an addition to it for automatically resizing the selected components as shown in the image below.
SQL Server Integration Services Solution Explorer Enhancements
There are some noticeable changes in the SSIS Solution Explorer. Deployment of SSIS packages has been a challenge as we had to deploy each SSIS package individually. There was no way to create a deployment package containing all the SSIS packages similar to some of the SSRS and SSAS functionality to deploy all objects. SQL Server 2012 now supports a new project deployment model as well as the legacy deployment model. Discussion about new Project Deployment Model in itself is a big topic, so check out the SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2) and SSIS Package Deployment Model in SQL Server 2012 (Part 2 of 2) tips. In a nutshell, the Legacy Deployment Model lets you deploy each individual package separately whereas the new Project Deployment Model lets you create deployment packet (which is an *.ispac file) from the SSIS project. Each SSIS project which you create in SQL Server 2012, by default gets created in Project Deployment Model which you can change if needed as shown below:
As previously indicated the new SSIS projects get created in the Project Deployment Model. When you right click on the project you will notice two more options. The "Project Parameters" let's you define the parameters in the project and the "Deploy" option let's you create the deployment package or deploy to the Integration Services Catalog database.
If you right click on the package in the Solution Explorer, you will notice one more option (Entry-Point Package). This option is helpful in the scenario when you have a parent package which calls child packages. You can set the Entry-Point Package option for the parent package.
Next Steps
- As you prepare to adopt SQL Server 2012 be sure to leverage these new features to help improve your productivity as an SSIS Developer.
- Check out these related tips:
- Review other SQL Server 2012 tips
- Review my all previous tips
- SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2)
- SSIS Package Deployment Model in SQL Server 2012 (Part 2 of 2)
- The sample code, example and UI is based on SQL Server 2012 CTP 1, it might get changed in further CTPs or in final/RTM release.
About the author
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
