New SSIS 2012 Designer Features

Data Flow

Connection Assistants

The Source Assistant and Destination Assistant are new Data Flow toolbox items in SQL Server 2012. Dragging them onto the designer will open a wizard that guides you through the process of adding a new source or destination component to your data flow. The first page of the wizard (Figure 4-24) asks you what type of data source you’d like to be connecting to: SQL Server, Excel, or Flat File. After you select the data source type, the wizard automatically determines the best connection manager and source or destination component for you. For example, when you select SQL Server, the wizard will pick the OLE DB provider. You are given the option to create a new connection manager or reuse an existing one. When the wizard completes, it adds a new Source or Destination component to your Data Flow that you can then connect to other transforms.

The Connection Assistant user interface includes a check box that allows the wizard to automatically filter out data sources for which it can’t find providers on your system. If you clear this check box, all data sources will be displayed. If you select a data source with no provider installed, a message will be displayed with a hint as to where you can download the preferred provider.

Improved Column Mapping

A number of usability improvements have been made to the column mapping functionality in the SSIS Data Flow. Data Flow components are now much smarter about the way they map incoming columns. Most will automatically match columns based on name and data type instead of relying on Lineage IDs like they did in previous releases. This means that you can connect a new source component to an existing set of transformations, and SSIS will automatically resolve the column mappings, assuming that all of the expected columns exist. If SSIS is unable to resolve all of the expected column mappings, an error icon is displayed on the path with the missing columns (Figure 4-25).

Double-clicking the error icons (or right-clicking the path and selecting Resolve References from the shortcut menu) opens the new Resolve References dialog box (Figure 4-26). This dialog box lets you map output columns to input columns, as well as delete columns that are no longer required. The dialog box has a lot of useful functionality, such as filtering capabilities, automatic mapping based on name and data type, and importing and exporting the column mappings to and from Excel. Resolving column references with this dialog box will fix all column mappings for an entire execution tree.

Editing Components in an Error State

Previous versions of SSIS did not allow you to open up the editor for a Data Flow component if it was in an error state. You would need to resolve any column mapping errors, or you would be limited to using only the Advanced Editor. Data Flow components remember their metadata in SQL Server 2012, which allows you to edit them even if they are in an error state.

Grouping

SQL Server 2012 allows you to group together one or more objects in the Data Flow. Creating a group in the Data Flow is similar to adding a Sequence container in the Control Flow, except it exists only within the designer. Groups do not affect the way the Data Flow actually runs. Groups can be expanded and collapsed, and are a useful way to hide portions of very complicated Data Flows. Figure 4-27 shows a Data Flow with a group that isolates some of the processing logic. This group could be collapsed to hide the transforms inside.

Simplified Data Viewers

Data viewers have been simplified in SQL Server 2012. There is only one type of viewer now, the Grid View. To add a data viewer, right-click the path on which you want to see the data and select Enable Data Viewer. Figure 4-28 shows the icon that indicates an active data viewer. You can configure which columns are displayed by double-clicking the path, and selecting the Data Viewer tab.

Row Count and Pivot Transform User Interfaces

A simple user interface has been added for the Row Count transform (Figure 4-29). The interface has a simple drop-down control and allows you to select any Int32 or Int64 package variables.

The Pivot transform has also been updated to include its own user interface (Figure 4-30), which displays a diagram of a pivot table at the top, allowing you to select the columns to use as the Pivot Key, the Set Key, and the Pivot Value.

Flat File Source Changes

A couple of improvements were made to the Flat File Source parser: It can now support ragged-right delimited files and embedded qualifiers.

Ragged-Right Delimited Files

Ragged-right delimited files can have rows with a varying number of columns. The columns will always appear in the same order, but certain rows will be missing one or more of their trailing columns. These missing columns are meant to be interpreted as NULL values. Listing 4-1 shows an example of this file format. Notice that each row has a Key and AlternateKey value, but the SubCategoryKey and WeightMeasureCode fields are optional. The second data row is missing the final field (WeightMeasure Code), and the third data row is missing both the final two fields (SubCategoryKey and WeightMeasure Code).

Listing 4-1 The ragged-right delimited file format

Key,AlternateKey,SubCategoryKey,WeightMeasureCode
211,FT-R92R-F8,14,LB
212,HL-U509-R,31
184,RM-R600
426,FR-M63B-40,12,LB

The Flat File Source parser in previous versions of SSIS would not look for row delimiters until it had seen the expected number of columns. Table 4-1 shows how the sample data set would be interpreted in SQL Server 2005 and SQL Server 2008. Notice that the entry for the third data row (key 184) is parsed as a single column.

Table 4-1 Results from the ragged-right delimited file in previous versions of SSIS.

Key	AlternateKey	SubCategoryKey	WeightMeasureCode
211	FT-R92R-F8	14	LB
212	HL-U509-R	31	184,RM-R600
426	FR-M63B-40	12	LB

The SSIS Flat File Source now fully supports the ragged-right delimited file format. Table 4-2 shows what the data set looks like when parsed in SQL Server 2012.

Table 4-2 Results from the ragged-right delimited file when parsed correctly.

Key	AlternateKey	SubCategoryKey	WeightMeasureCode
211	FT-R92R-F8	14	LB
212	HL-U509-R	31
184	RM-R600
426	FR-M63B-40	12	LB

Embedded Qualifiers

If a field value in a flat file contains the column delimiter (for example, a comma or tab), you can use a qualifier mark when the field value starts and ends. This is typically an apostrophe (‘) or double quote (“) character. To use the qualifying character as a literal within the string, you would typically “escape” it by doubling the character. Listing 4-2 shows a sample data set that uses double quote characters as a text qualifier. The first data row contains a double quote literal character, which is escaped using a second double quote character.

Listing 4-2 A data set that uses embedded qualifiers

Key,AlternateKey,EnglishDescription
396,HS-3479,"High-quality 1"" thread-less headset, with a grease port"
252,FR-R92R-56,"Our lightest and best quality aluminum frame"
261,FR-R38B-62,"The LL Frame provides a safe, comfortable ride"

Although text qualifiers were supported, the Flat File Source did not support embedded qualifiers in previous versions of SQL Server. In fact, it was unable to parse data sets like the one in Listing 4-2, and would return an error while parsing the first data row due to the lack of a delimiter immediately after the double quote qualifier. Table 4-3 shows how the Flat File Source correctly interprets embedded qualifiers in SQL Server 2012.

Table 4-3 Results from the data containing embedded qualifiers when parsed in SQL Server 2012.

Key	AlternateKey	WeightMeasureCode
396	HS-3479	High-quality 1″ thread-less headset, with a grease port
252	FR-R92R-56	Our lightest and best quality aluminum frame
261	FR-R38B-62	The LL Frame provides a safe, comfortable ride