This content is part of the Essential Guide: Guide to SQL Server data management and data quality
Manage Learn to apply best practices and optimize your operations.

Further SSIS tips: Parameters and undoing operations

Learn more new tips and tricks for SSIS from expert Robert Sheldon. Check out the last of a three-part series on changes to this important tool.

Editor's note: Check out the other two parts of the series: a tip on new SSIS features for BI developers and a tip on further SSIS 2012 features for connection managers.

Another handy new feature in SSIS 2012 is the ability to create parameters that let you easily pass property values into a package when it's executed. For example, if you want to pass in the name of a SQL Server instance whenever you run a package, you simply create a parameter and provide the instance name when you execute the package.

Figure 1. Creating a package parameter

Package and project parameters

You can also create parameters at either the package or project level. To create a package parameter, go to the new Parameters tab in SSIS Designer, click the Add button, and enter the applicable information in the row created for that parameter. For example, Figure 1 shows a parameter named DbName, which is used to pass the database name into the package whenever that package is executed.

Notice that, along with providing a parameter name, you must specify the data type, an initial value and description. The settings for the other properties, Sensitive and Required, depend on your individual requirements. The Sensitive property determines whether the value is encrypted, and the Required property determines whether a value must be supplied when the package is executed. The default value for both properties is False.

Figure 2. Creating a project parameter

It's just as easy to create a project parameter. To do this, double-click the Project.params node in Object Explorer. This launches the Project.params tab, as shown in Figure 2. Notice that the tab lists a parameter with the same name as the package parameter above. Because the project and package parameters are separate from each other and operate at a different scope, you can use the same names. When you later reference the parameters, you'll be able to differentiate them by the project or package name.

Figure 3. Parameterizing an SSIS component

That's all there is to creating package and project parameters. Not surprisingly, you can use package parameters only in the package they're created, but you can use project parameters in any package in the project.

After you create a parameter, the next step is to associate it with a specific property in a component. To do so, right-click the component and then click Parameterize, as shown in Figure 3. In this case, the component is the AS2012 project connection manager.

Figure 4. Assigning a parameter to a component's property

When you click the Parameterize option, the Parameterize dialog box appears, as shown in Figure 4. Here, you select one of the component's properties and the parameter you want to associate with that property. In this case, the InitialCatalog property is selected and the project parameter DbName is assigned to that property.

Once you associate a property with a variable, you build and deploy your package as you would any other package. You then supply the parameter value when you run the package. SSIS provides several ways to do that. For example, if you run your package from SQL Server Management Studio, you can provide the parameter values from Object Explorer. To do so, connect to the SSIS instance where you deployed your package and expand the Integrations Services Catalog node until your project is displayed. Right-click the project and then click Configure. This launches the Configure dialog box, shown in Figure 5.

Figure 5. Setting parameter values in SQL Server Management Studio

Notice that, in this case, the dialog box displays the DbName parameter for both the project (Features2012) and the package (Package.dtsx). However, if you instead launch the Configure dialog from the package itself, only the package-level parameter will be displayed.

As shown in Figure 5, each parameter is assigned the initial value defined when the parameter was created. You can overwrite that value by clicking the Browse button (the ellipses) to the right of the parameter and providing the new value. Then, when you run the package, it uses that value. The best part is that you can supply a different (or the same) value each time you run the package.

Undo and redo operations

Out of all the great new features added to SSIS 2012, the one that's likely to have developers cheering the loudest is the ability to undo and redo their actions. In the past, if you inadvertently deleted a component with a complex configuration and then discovered that you needed the component after all, you had to start over. Now, you can undo and redo up to 20 actions in the Control Flow, Data Flow, Parameters and Event Handlers tabs, as well as in the Variables window. The toolbar now includes the Undo button and Redo buttons, and the Edit menu now includes the Undo and Redo options.

Figure 6. Undoing one or more operations

To undo a single action, simply click the Undo button or select the Undo option from the Edit menu. If you want to undo multiple steps, click the down arrow associated with the Undo button, highlight the actions you want to undo and then press Enter, as shown in Figure 6.

The Redo button works just like the Undo button. To redo a single action, click the Redo button or select the Redo option from the Edit menu. If you want to redo multiple actions, click the down arrow associated with the Redo button, highlight the actions you want to redo and then press Enter, as shown in Figure 7.

Figure 7. Redoing one or more operations

The addition of the undo and redo features in SSIS Designer might seem like a minor undertaking, but they can save you more time than just about any other changes to SSIS. Once you start using them, you'll never want to go back.

SQL Server Integration Services 2012

Following this tip on these new SSIS features will go a long way in making your life easier. They've been seamlessly integrated into the SSIS environment and will become a matter of habit in no time. Plus, SSIS 2012 includes a number of other enhancements that help to improve the server environment, deployment processes, memory management, data quality and specific SSIS components. But the features described here and in parts one and two of this series are a great place to start as you move into the world of SQL Server 2012.

Dig Deeper on Microsoft SQL Server Integration Services (SSIS)