The upcoming release of SQL Server 2012 includes a major overhaul for Integration Services (SSIS). I write about the changes in SSIS, big and small, in Introducing Microsoft SQL Server 2012 (Microsoft Press, 2012) which will be available in March as a free download. Meanwhile, if you really can’t wait that long, you can get a sneak preview of a few chapters beginning February 1.
When writing a book like this which focuses on all the new stuff, it’s easy to get caught up in minutiae. As I begin to wind down the writing process for this book, I thought it would be interesting to step back and consider which enhancements I thought were particularly helpful for the package development process. There are certainly other enhancements that are helpful too, but the following list includes the ones that are special favorites of mine because they address problems that I encounter most often during my own package development or when teaching students about SSIS.
In no particular order, here is my list:
- Interface overhaul. Business Intelligence Development Studio (BIDS) is now SQL Server Data Tools (SSDT), but that’s not the important part. Inside SSDT, the Toolbox window now includes a Favorites folder into which you can place components that you use regularly. I find that people can spend a lot of time hunting through the list of available components, so this feature can really help you out when there’s only a handful of components that you use in every package. There are several other changes to the interface, such as zoom control, and icons to specify whether a component succeeded or failed during package execution that improve the development experience as well.
- Shared Connection Managers. The idea of building something once for reuse many times is not new, and yet until now you’ve had to add the same connection managers to package after package, which turns into a rather tedious task if a change was required to each package’s connection manager. Now you can set up a connection manager once and reference it in multiple packages. As an added bonus when you use a Cache Connection Manager that you share between a parent and child package, the two packages share the same cache which optimizes performance for lookups against the same source in both packages.
- Undo and Redo. How many times have you made a change to a package and then instantly regretted it? I bet it’s happened at least once! Fortunately, the Undo button is now enabled so you can reverse that change, and the Redo button is available also in case you change your mind afterwards.
- Resolve References Editor. Managing metadata in the data flow pipeline is a concept with which many beginners struggle, especially when they make a change early in the data flow that has a ripple effect across the downstream components and causes metadata errors. Now SSIS includes a Resolve References Editor that you can use to quickly resolve the mapping of input and output columns between components. You can use it to check that columns are mapped to one another properly, and also to see which columns remain unmapped.
- Variable scope. Here’s another commonly encountered problem. How many times have you added a variable only to realize later that you inadvertently assigned its scope to a task rather than to the package? Your only option was to delete the variable and add a new one while taking care to set the scope correctly. The new SSIS creates new variables at the package scope by default, and allows you to move an existing variable to a different scope.
- Left() function. In previous versions, you can use the Right() function or the Substring() function to work with portions of a string, but there was no Left() function until the SQL Server 2012 release. Hooray!
- ReplaceNull() function. A very common task in data warehousing is to replace NULL values with some value. This new function just simplifies that task.
- Project deployment model and parameters.Working with SSIS projects and deployment is a completely new way of setting up packages to work in a new environment in SQL Server 2012, whether test or production. When you combine this deployment model with the use of parameters, you use parameters instead of configuration files to set run-time values for expressions. Most people didn’t understand what to do with configuration files, so this will be a welcome change indeed for that group. Even if you did work successfully with configuration files, you will likely still find it easier to set up parameters than configuration files.
You can download the upcoming ebook to get more details about the items in my list above. Specifically, Chapter 6 covers Integration Services and is available now in the first draft of the ebook. And come back to visit this blog often – I’ll be providing more details in the coming weeks about each of these items that space did not permit me to include in the ebook.
Meanwhile, you might also like these resources about SSIS and the upcoming release:
- The SSIS product development team provides posts from time to time to elaborate on various aspects of the product. You can use the Denali tag on the blog site to focus on the the new features of 2012.
- Jamie Thomson has a comprehensive list of changes with screen shots, which after all are worth more than a thousand words!
- Jamie also posted an excellent walk-through of the level of detail that package execution can log in the SSIS catalog.
- Last, but not least, Jamie has published a reporting pack that helps you monitor logging in the SSIS catalog.
I think my list of favorite enhancements is heavily biased by my experiences this week working with a new SSIS 2008 r2 developer. Perhaps you have a different set of favorites. If so, share your thoughts! Which SSIS features do you like best in the SQL Server 2012 release?