SQL Server 2014 was released earlier this month. There was an extensive overhaul of SSIS in 2012, but nothing new in 2014. It’s a good bet that most shops are still running 2008 or 2008R2, so many of you haven’t seen the changes from 2012. The lucky thing is that the skills you have from earlier releases of SSIS will transfer to 2012.

I have divided the 2012 enhancements and features into several categories:

• Cosmetic
• Convenient
• Development
• Deployment
• Logging and troubleshooting

Cosmetic

There are quite a few things that work the same in SSIS 2012 but don’t look the same. The first, and a disappointment to me, is that the development tool is no longer called BIDS. It is now SQL Server Data Tools, or SSDT. BIDS is just so much easier to say, but changing the name of products is the popular thing to do at Microsoft.

The original SSDT that you install with the SQL Server 2012 media works with Visual Studio 2010. If you are interested in running it in Visual Studio 2012, you can download the appropriate SSDT BI here. Things get better, I promise, but this version has the Metro-look with basically one color. I am using that version at my current customer, and I find it more difficult than the VS 2010 version to work with visually.

The next cosmetic thing is the amazing rounded corners of the icons. Again, this change makes no difference in how you work. The status icons have changed. They are now located at the top right of the task or component.

If you take a look at a data flow, you will also find that the green connectors are now blue. Again, it really doesn’t make any difference in how you work.

Another change that is more cosmetic, is the new SSIS Toolbox. If you just look for the Toolbox in the View menu, it will be empty.  If you right-click in the design area or click the new SSIS Toolbox icon in the upper right area , you will open it up. There are several sections to the new toolbox and you can move the items around if you wish.

Convenient

While the Cosmetic changes are nothing to be that excited about, the Convenient changes are things we have been asking about for years. The two biggest bug fixes enhancements are Undo/Redo and paste functionality that doesn’t put your pasted task in a different county.

When working with SSIS 2012, you need to start thinking in terms of projects instead of packages (more on this later).  You can add connection managers at the project or package level. When you add the connection manager to the project level, all of the packages in the project automatically inherit it. So, going forward, start adding connection managers at the package level only if they are specific to that package. When you do add a connection manager at the package level, you can right-click and promote it to the project level. That’s convenient!

One of the biggest headaches when working in the data flow is when a change is made at a source which makes the following destinations blow up. In previous versions of SSIS, you would get an ugly dialog box that was difficult to understand. Now you can easily fix mapping issues in the data destination or with the new Resolve References dialog found by right-clicking the connector.

You drag the column names from the edge lists into the mapping area in the center.

One convenient new feature is a new symbol next to connection managers, variables and tasks denoting that an expression is used for one of the properties. This is going to save some troubleshooting time!

Speaking of variables, there is now a built-in way to move variables to a different scope and to see all variables at once regardless of scope.

One more convenient change: two-clicks to enable a data-viewer instead of five or six.

Development

There is not much to talk about in this category. If you use Change Data Capture (CDC), there is a new CDC Control task, a CDC Source and a CDC Splitter transform. These will make working with CDC easier and eliminate most of the coding. You also get CDC for Oracle. In addition, there is a control that works with the new Data Quality Services.  One new task that I really love is the Expression Task. It allows you to easily assign a value to a variable when none of the other methods will work.

Deployment

By far, this is the category with the most extensive changes. Beginning with 2012, there is a new way to work with SSIS called the Package Deployment Model. I recently presented an hour webinar just on SSIS deployment. Without going deep into this topic, here are the highlights:

• The project is now the unit of work instead of the package when it comes to deployment
• The Project Deployment Model uses the new SSIS Catalog
•  You can deploy the project from VS using a wizard. You supply just the server name and folder.
• Building the project creates an ISPAC file. This file has everything required to deploy the project.
• Instead of configuration files, the Catalog stores configurations.
• You can set up one or more Environments to store the values, like server names, for configuration.
• Parameters, variables that are read-only inside the packages, are used to pass configuration values into the packages.
• You can use a new version of dtexec to run the packages, but there is also a new set of T-SQL commands for running packages.

Logging and Troubleshooting

If you deploy with the new Project Deployment Model, you get logging by default. The level of logging is configurable at the server, project, package or execution level. A built-in set of reports lets you view execution results. You can also download a Reporting Pack if the built-in reports do not meet your needs.

On the trouble-shooting side, is the new Data Tap feature. This feature saves a copy of the data the way it looks at a point in the data flow. This is like a Data-Viewer except that it happens when the package is deployed and a physical file results. I’ll post two blogs on this topic soon!