New SSIS 2012 Designer Features

  • 9/15/2012
Contents
This chapter, from Microsoft SQL Server 2012 Integration Services, provides an overview of the new features and productivity enhancements in the Integration Services designer for SQL Server Data Tools.

In This Chapter

  • The Integration Services Designer

  • Configuration and Deployment

  • New Tasks and Data Flow Components

  • Control Flow

  • Data Flow

  • Scripting

  • Expressions

  • Summary

The Integration Services Designer

Visual Studio

The first thing you’ll notice when starting a new SSIS project in SQL Server 2012 is that SQL Server Data Tools (formerly Business Intelligence Development Studio) has been upgraded to Microsoft Visual Studio 2010 SP1. Along with the upgrade comes an updated look and feel (shown in Figure 4-1): You’ll find many small visual improvements, such as an updated color scheme, new icons, and rounded corners on the tasks and transforms.

Figure 4-1

Figure 4-1 The SQL Server 2012 designer for Integration Services.

NOTE

Changes were made to way the SSIS Visual Studio project is packaged, so that Microsoft will be able to release an update for SQL Server Data Tools that supports new versions of Visual Studio without waiting for a new release of SQL Server.

Undo and Redo

Perhaps the biggest usability feature missing from previous versions of the product is undo and redo functionality. You can undo most operations within the designer by pressing Ctrl+Z or clicking the Undo button. You can redo changes with Ctrl+Y, or by clicking the Redo button on the toolbar.

Getting Started Window

The Getting Started (SSIS) window (Figure 4-2) appears when creating new SSIS projects. It provides a live feed from Microsoft.com, displaying SSIS news, as well as links to tutorial videos and samples. If the window has been closed, you can open it by clicking the SSIS menu and selecting Getting Started.

Figure 4-2

Figure 4-2 The Getting Started window.

Toolbox

SSIS now has its own toolbox window. The SQL 2012 toolbox includes significant performance improvements over previous versions, and will automatically display third-party tasks and components installed on your development machine. Items in the toolbox are divided into different categories (which change depending on whether you are looking at the Control Flow or the Data Flow designer). The bottom area of the toolbox displays the name and description of the currently selected item. Most toolbox items will also display in this area a help button and a Find Samples link that will bring up samples that demonstrate how the item can be used within an SSIS package. You can display the toolbox from the SSIS menu by selecting SSIS Toolbox, or by clicking the new SSIS Toolbox button in the upper-right corner of the designer area.

Tasks in the Control Flow toolbox (Figure 4-3) are divided into four categories: Favorites, Common, Containers, and Other Tasks. The Favorites section displays the two most commonly used tasks by default, the Data Flow Task and the Execute SQL Task.

Figure 4-3

Figure 4-3 The toolbox for the Control Flow.

Components in the Data Flow toolbox (Figure 4-4) are divided into five categories: Favorites, Common, Other Transforms, Other Sources, and Other Destinations. The Favorites category contains the new Source and Destination Assistants, which are described later in this chapter.

Figure 4-4

Figure 4-4 The toolbox for the Data Flow.

Toolbox items can be moved between categories by right-clicking the item name, and selecting one of the Move To options. The shortcut menu also gives you the Refresh1 Toolbox option, which will look for newly installed third-party tasks and components, and the Reset Toolbox Defaults option, which moves the tasks back to their default categories.

Variables Window

Many usability improvements have been made to the SSIS Variables window (Figure 4-5). It now appears at the bottom of the designer by default, and like most Visual Studio tool windows, it can be docked to any location within the designer. You can display the Variables window from the SSIS Menu by selecting Variables, or by clicking the new Variables button in the top right corner of the designer window.

Figure 4-5

Figure 4-5 The SSIS Variables window.

The Variables window shows the default value of a variable. If the variable is set to evaluate using an expression, the expression is displayed in the final column, and the value cannot be modified directly. Clicking the ellipsis button at the end of the row will open the Expression Builder dialog box. Setting a new expression on a variable will automatically set its EvaluateAsExpression property to True.

In SQL Server 2012, new variables are always created at the Package scope. This is different from previous versions, where the variable would be created in the scope of whichever task or container was currently selected in the designer. The Variables window also contains a new Move Variable button, which allows you to move a variable to a new scope (Figure 4-6).

Figure 4-6

Figure 4-6 The Move Variable button allows you to change a variable’s scope.

Zoom Control

Both the Control Flow and Data Flow designer surfaces contain a new zoom control in the lower-right corner. This control allows you to easily resize the designer view in (up to 5x size increase) and out (up to 10 percent of the original size). The bottom of the control has a Fit View To Window button that will automatically resize your view so that all package contents fit on one screen.

Autosave and Recovery

SSIS projects now make use of the AutoRecover functionality provided by Visual Studio. This feature allows you to recover your work if SQL Server Data Tools shuts down unexpectedly (due to power failure, crash, or misbehaving third-party add-on). When Visual Studio restarts after an unexpected shutdown, you will be prompted to recover any unsaved changes to your SSIS packages. You can control the AutoRecover settings on the Tools | Options… | Environment | AutoRecover preferences page.

Status Icons

Previous versions of SSIS used colors (green, yellow, and red) to indicate the status of tasks and components while the package was running. The same colors are used in SQL Server 2012, but instead of changing the background color of the task or component, a status icon is displayed in the upper-right corner of the box (Figure 4-7).

Figure 4-7

Figure 4-7 Status is used to indicate progress instead of changing the background color of the box.

NOTE

Using status icons instead of colors is one of the many accessibility changes that was made for SSIS in SQL Server 2012. Although many people might miss the flashing green and red box colors used in previous versions, not everyone was able to enjoy them, as perception of red and green are affected by the most common form of colorblindness.

Annotations

A number of improvements were made to package annotations, including the following:

  • Annotations are persisted in plain text in the package (.dtsx) file. Previous versions of the product had the annotation text encoded in a way that made it very difficult to extract for documentation purposes.

  • The annotation window automatically grows as you type.

  • Annotations now accept new lines; just press Enter.

  • You can easily change the font style, size, and color of an annotation block by right-clicking the annotation block and selecting Set Annotation Text Font.

  • You can create a new annotation by clicking the design surface and starting to type.

Save to your account

This chapter is from the book