New SSIS 2012 Designer Features

Control Flow

Expression Adorners

There is now a visual indicator when tasks, connection managers, and variables make use of SSIS property expressions. This new feature (called Adorners) adds a white expression icon to the upper-left corner of the object’s regular icon for tasks and variables (Figure 4-22). For connection managers, the expression icon is added to the left of the connection manager’s name.

Figure 4-22 Tasks with property expressions are now highlighted in the designer.

Connection Managers

The Connection Managers view in the designer window (Figure 4-23) has had a few visual updates in SQL Server 2012. Icons have been updated to show the different connection manager types, and an expression icon is shown for connection managers that use expression properties. Shared connection managers, which are declared at the project level, appear with a (project) prefix before their name.

Figure 4-23 The new Connection Managers view in the SSIS designer window.

Connection managers can be individually taken offline. This is much like the Work Offline mode, which can be set at the SSIS project level. When a connection manager is offline, all calls to it are essentially short-circuited and return right away. This is useful when a server is offline, as it prevents long waits while connections timeout. A connection manager’s connectivity status is validated when a package is opened. If the connection cannot be established, the connection manager is automatically placed in Offline mode. You can put a connection manager online by right-clicking it and selecting Test Connectivity from the shortcut menu. To force a connection manager into an offline state, right-click it and select Work Offline.

Execute SQL Task

The Execute SQL task has a new property called TypeConversionMode. This property controls whether the Execute SQL task attempts to perform data type conversion at runtime. For example, imagine the query for the Execute SQL task returns a value of “101” as a VARCHAR(10) data type—a string—and you are assigning it to a package variable with an Int32 data type—a number. In SQL Server 2012, the Execute Package task attempts to convert the value to the package variable type when the TypeConversionMode is set to Allowed. If the TypeConversionMode is set to None, strict data type matching is enforced, and you get an error at runtime, the same behavior you’d get in previous versions of SSIS. Packages upgraded from earlier versions of SSIS will have a default TypeConversionMode of None to preserve compatibility. New Execute SQL tasks will have a default value of Allowed.