Guide to SQL Server data management and data quality
A comprehensive collection of articles, videos and more, hand-picked by our editors
Editor's note: This is the second part of a three-part series on new features in SSIS. Please follow this link for the first part, "Great new SSIS features for developers."
An SSIS 2012 feature guaranteed to make the developer's life easier is the ability to turn off a connection manager. By default, when you open a package, SSIS checks all the data sources to ensure that the external metadata is valid. If your package includes a lot of connections to network resources or if data source access is slow or temporarily unavailable, this process can take an extraordinary amount of time, made all the more frustrating if you regularly reopen your package.
Offline connection managers
You can get around this issue by setting the DelayValidation property on the individual components; but if you find yourself having to reset this property often and there are many such components, you can spend a lot of time dealing with this issue, rather than actually developing the package.
But SSIS 2012 has come up with a handy solution. With a couple simple clicks, you can now specify that a connection manager is either offline or online. To set a connection manager to work offline, right-click the component in the Connection Managers windows, and then click Work Offline, as shown in Figure 1.
When you set a connection manager to work offline, SSIS adds an icon (a red down-arrow) before the connection manager name in the Connection Managers window. Figure 2 shows the AW2012 connection manager after it has been set to work offline.
Once you set a connection manager to work offline, the components that use this connection will not try to validate the metadata until the connection manager is back online. In fact, any task or data flow component that references the connection manager is marked with a red reversed X to indicate that the component cannot acquire a connection.
When you're ready for a connection manager to be back online, you simply right-click the connection manager and then click Work Offline again to deselect that option. You can also wait until you close and reopen the package, in which case all connection managers are validated and set to work online.
It might seem that, if reopening the package sets all the connection managers to working online, there is little advantage to using the Work Offline option. The DelayValidaton property, while a bit cumbersome, persists after you close and then reopen a package. Unlike that property, SSIS lets you set all the connection managers to work offline before you even open a package. With the project open, but not the package, select the Work Offline option from the SSIS menu, and then open your package. All connection managers will be set to work offline, thus avoiding that initial validation process.
Column mapping resolution
Imagine this scenario: You create an SSIS package, configure an elaborate data flow and then discover a column name in your data source has changed. In the past, SSIS was none too pleased about such disruptions, especially when it came to the data paths in your data flow. Remapping columns as a result of such changes often resulted in reconfiguring or re-implementing individual components. SSIS 2012 comes to the rescue once more with a new feature that makes updating column references a breeze.
Let's look at an example to understand how this works. Figure 3 shows a data flow that contains a Flat File source and an OLE DB destination. After the package was created, one of the column names in the data source changed, which caused that big red reversed X you see next to the data path.
If you right-click the data path, you'll discover a new option -- Resolve References -- which is shown in Figure 4. By clicking this option, you can modify the input and output references within the data flow, as it passes through a particular data path.
Clicking this option launches the Resolve References dialog box, shown in Figure 5. You can tell that three of the columns are properly mapped -- Column 1, Column 2 and Column 3 -- by the way they appear in the Mapped Columns grid in the center of the dialog box. Notice that the Source list includes the ColA column. This is the column whose name changed from Column 0 to ColA. Also notice that the Destination list includes the Column 0 column. Originally both the source and destination columns shared this name and were properly mapped to one another.
To map the ColA column to the Column 0 column, you drag each column to the appropriate row in the Mapped Columns grid so they're manually mapped, as shown in Figure 6.
Once you've added the columns to the Mapped Columns grid, you can verify your changes by clicking the Preview Changes button. This launches the Preview Resolve References dialog box, shown in Figure 7. As you can see, Column 0, which targets the OLE DB destination, now maps back to the ColA column.
Those of you who've dealt with data path references no longer mapping will have a unique understanding of what a timesaver this new feature can be. With a few simple clicks, you can remap your columns and be back in business in no time.