Implementing a Data Warehouse with Microsoft SQL Server 2012: Dynamic Packages

Lesson 1: Package-Level and Project-Level Connection Managers and Parameters

In SQL Server 2012, SSIS introduces parameters and project-level connection managers. Parameters enable you to assign values to properties within packages at the time of package execution. Project-level connection managers allow you to define the data source connection once and use it in all packages that are part of the project. Both features are available only to projects developed for the project deployment model. This means that SSIS packages created with prior versions of SQL Server have to be upgraded to the new project deployment model if you want to use these new features.

Both functionalities are a replacement for the package configurations used in previous versions of SQL Server. In SQL Server 2012, SSIS also takes advantage of build configurations from Microsoft Visual Studio, which enable you to define multiple configurations and set the values of your parameters per configuration. This makes it easier to debug and deploy packages in SQL Server Data Tools against different SSIS servers.

Using Project-Level Connection Managers

Project-level connection managers allow you to set up a connection manager on the project level. This means that you can define a connection for the whole project and use it in all packages that are part of the project. In prior versions of SQL Server, the connections were always contained within each package.

To create a project-level connection, define a new connection manager by right-clicking Connection Managers in Solution Explorer under your project and selecting the New Connection Manager option. An alternative method is to convert an existing package-level connection to a project-level connection. You can do this by opening the package, right-clicking the connection you want to convert in the Connection Managers window, and selecting Convert To Project Connection, as shown in Figure 9-1.

Project-level connections are a very useful new feature when it comes to developing and maintaining your packages. Note that in cases when you need to have a connection available only for the specific package, you can still create a connection at the package scope.

Parameters

In SQL Server 2012, SSIS introduces parameters. Parameters allow you to assign values to properties within packages at the time of package execution. They can also be used in SSIS expressions—for example, to use an Expression task to set a variable value based on the specific value of the parameter. There are two types of parameters: project parameters, which are created on the project level, and package parameters, which are created at the package level. You can use and set parameters only in projects developed for the project deployment model. When you are using the project deployment model, projects are deployed to the Integration Services catalog. Details regarding deployment possibilities are explained later in Chapter 11, “Installing SSIS and Deploying Packages.”

Using Parameters

You can use a single parameter to assign a value to multiple package properties, and you can use a parameter in multiple SSIS expressions. Depending on where the project is in the project deployment life cycle, you can assign up to three different types of values to each parameter. The three types are listed in the order in which they can be applied to the parameter:

• Design default value. The default value assigned when the project is created or edited in SQL Server Data Tools (SSDT). This value persists with the project.

• Server default value. The default value assigned during project deployment or later, while the project resides in the SSIS catalog. This value overrides the design default.

• Execution value. The value that is assigned in reference to a specific instance of package execution. This assignment overrides all other values but applies to only a single instance of package execution.

Note that the last two types of values become relevant after the SSIS project has been deployed to the Integration Services catalog.

Defining Parameters

You add project or package parameters by using SSDT. Usually you create a project parameter by selecting Project.params in Solution Explorer under your project name, as shown in Figure 9-2. To add a package parameter, you must first open the package and then select the parameters tab in the package design area.

When you open the package or project parameters window, you can define a new parameter by clicking the Add Parameter icon (the first icon on the left; it looks like a blue cube). Figure 9-3 shows a parameter window with one parameter called pTK463DWConnectionString.

When you create a parameter in SSDT, there are several properties to specify:

• Name. The name of the parameter. The first character of the name must be a letter or an underscore.

• Data type. The data type of the parameter.

• Value. The default value for the parameter assigned at design time. This is also known as the design default.

• Sensitive. If the value of this property is True, parameter values are encrypted in the catalog and appear as NULL when viewed with Transact-SQL or SQL Server Management Studio.

• Required. Requires that a value other than the design default be specified before the package can be executed.

• Description. For maintainability, the description of the parameter. In SSDT, set the parameter description in the Visual Studio Properties window when the parameter is selected in the applicable parameters window.

You can edit parameters in the list in the parameter window, or you can use the Properties window to modify the values of parameter properties. You can delete a parameter by using the Delete Parameter toolbar button. By using the Add Parameters To Configurations toolbar button (the last button on the right), you can specify a value for a parameter for a specific build configuration that is used only when you execute the package in SQL Server Data Tools. Build configurations are explained in the next topic.

Another approach to creating a parameter is to do so implicitly by right-clicking a control flow task or a connection manager and selecting the Parameterize option. A Parameterize dialog box opens, as shown in Figure 9-4. Here you can specify which property should be dynamically evaluated at run time and whether the parameter should be created (the latter is specified by selecting the Create New Parameter option).

Build Configurations in SQL Server 2012 Integration Services

Build configurations in Visual Studio provide a way to store multiple versions of solution and project properties. The active configuration can be quickly accessed and changed, allowing you to easily build multiple configurations of the same project. Two levels of build configurations can be defined in Visual Studio: solution configurations and project configurations.

The new project deployment model in SQL Server 2012 takes advantage of more possibilities for build configurations from Visual Studio than earlier versions did. You can now set project and package parameters to get values from build configurations. Also, some of the project-level properties can be set via build configurations (for example, deployment server name and server project path).

Creating Build Configurations

A project can have a set of defined project properties for every unique combination of a configuration and platform. You can create an additional project or solution configuration by using the Configuration Manager:

1. Select the Configuration Manager option from the Configurations drop-down list on the Standard toolbar to open the Configuration Manager dialog box. Alternatively, you can first open the project’s Property Pages dialog box (right-click the project name in Solution Explorer and select Properties), as shown in Figure 9-5, and click the Configuration Manager button.

   

   Figure 9-5 The project Property Pages dialog box.

2. In the Active Solution Configuration drop-down list, select New.

3. In the New Solution Configuration dialog box, enter the name of the solution configuration you would like to create. Select the Create New Project Configurations check box to also create the project configuration.

You can now change the active configuration directly from the Solution Configurations drop-down list on the Standard toolbar or from the Configuration Manager dialog box.

Using Build Configurations

You can bind parameter values to build configurations by using the parameter window:

1. Open the project or package parameters window.

2. In the parameters window, select the last toolbar button on the left (Add Parameters To Configurations).

3. In the Manage Parameter Values dialog box that appears, you can add values for each parameter for each configuration. Click the Add button to add a parameter to the configuration.

4. In the Add Parameters window, select parameters and click OK.

5. Set the appropriate values of parameters for your configurations, as shown in Figure 9-6.

Figure 9-6 shows that the pNoOfRows parameter will have a value of 10,000 when the package is executed using the Production configuration and 100 when using the Development configuration. This means that if you have multiple parameters and need to change the value of the parameters at design time when you are developing or debugging the SSIS project, you just need to switch between build configurations to have all parameter values changed at once.

You can also use build configurations to set build, deployment, and debugging configuration properties for a project. To assign different project properties based on configuration, right-click the project in Solution Explorer and select Properties. Figure 9-7 shows the deployment properties inside the project’s Property Pages dialog box. If you have to deploy your project to multiple servers, you can set different values for the Server Name and Server Project Path properties for each configuration, so that you can quickly switch between deployment environments at design time by using configurations.

Property Expressions

In previous chapters, you saw some examples of setting connection managers or control flow task properties at run time. The SSIS expressions used to update properties of the control flow during package execution are called property expressions. You can apply a property expression in two ways. First, you can set the property as an expression through the properties window by clicking the ellipsis (...) button of the Expressions property. This will open the Property Expression Editor, as shown in Figure 9-8. In this dialog box, you can select a property from the drop-down list and then type an expression.

The second way to set property expressions is to use the task or container editors, which offer one or more ways to set properties through expressions. Figure 9-9 shows the Execute SQL Task Editor. On the Expressions tab, you can specify property expressions.

Practice: Implementing Parameters

In this practice, you will use parameters to make your packages dynamic. In the first exercise you will parameterize the connection string, and in the second exercise you will use a parameter value to filter the source query in the data flow task. The third exercise focuses on setting up an additional build configuration to test project execution against another database.

If you encounter a problem completing an exercise, you can install the completed projects from the Solution folder that is provided with the companion content for this chapter and lesson.

EXERCISE 1 Set a Parameter for a Connection String

In this exercise, you parameterize a connection string by using a project parameter.

1. If you are missing the database objects from Chapter 5, “Designing and Implementing Data Flow,” execute the needed SQL code from that chapter to have all the stage and dimension tables available in the TK463DW database.

2. Start SQL Server Data Tools, open the TK 463 Chapter 9 project in the Starter folder, and then open the FillStageTablesParameters.dtsx package.

   Notice that this package is using two connections, AdventureWorks2012 and TK463DW.

3. In the Connection Managers window, right-click the TK463DW connection and select Convert To Project Connection. You have changed the connection from the package level to the project level and can now see this connection in the Solution Explorer window in the Connection Managers folder. The TK463DW connection can now be used by any other package within the same SSIS project.

4. Right-click Project.params in Solution Explorer, and then click Open or double-click Project.params to open it.

5. Click the Add Parameter button on the toolbar.

6. Name the parameter by setting the Name property to pTK463DWConnString and set the Data Type property to String. In the Value property field, type Data Source=localhost;Initial Catalog=TK463DW;Provider=SQLNCLI11.1;Integrated Security=SSPI;.

7. Close the Project.params window.

8. Inside the FillStageTablesParameters package, right-click the (project) TK463DW connection in the Connection Managers window and select Parameterize.

9. In the Parameterize dialog box, select the Use Existing Parameter check box and select the pTK463DWConnString parameter in the drop-down list. Notice that the project parameter name starts with $Project::. Click OK to close the Parameterize dialog box.

10. Look at the (project) TK463DW connection in the Connection Managers window and notice a small icon next to it reflecting that this connection is parameterized.

EXERCISE 2 Use a Parameter in the Data Flow Task

In this exercise, you create a package-level parameter that will be used to filter source data. You will use this parameter in the data flow task to filter only rows from the source for which the year of the modified date is equal to or greater than the parameter value.

1. If necessary, start SQL Server Data Tools, open the TK 463 Chapter 9 project, and then open the FillStageTablesParameters.dtsx package from the previous exercise for editing.

2. Select the Parameters tab and click the Add Parameter button on the toolbar.

3. Name the parameter by setting the Name property to pYear and set the Data Type property to Int16. For the Value property, enter 2002.

4. Click the Data Flow tab and open the Person OLE DB Source adapter.

5. In the OLE DB Source Editor, change the data access mode to SQL Command and enter the following SELECT statement to retrieve the necessary rows and use a parameter placeholder inside the query.

   SELECT
     BusinessEntityID,
     PersonType,
     NameStyle,
     Title,
     FirstName,
     MiddleName,
     LastName,
     Suffix,
     EmailPromotion,
     AdditionalContactInfo,
     Demographics,
     rowguid,
       ModifiedDate
   FROM
     Person.Person
   WHERE
     YEAR(ModifiedDate) >= ?

6. Click the Parameters button to open the Set Query Parameters dialog box.

7. For the Variables property, select the $Package::pYear parameter as the source for the query parameter. Click OK twice to close the window and the OLE DB Source Editor.

8. Execute the package and observe the number of rows displayed in the data flow area.

9. Change the parameter value to 2008 and execute the package. Notice that fewer rows are read from the OLE DB Source adapter in the data flow area.

EXERCISE 3 Use Build Configurations

In this exercise, you create an additional database, TK463DWProd, and then create a new build configuration in Visual Studio to use this database when running the SSIS package build from the previous exercise.

1. Start SSMS and connect to your SQL Server instance. Open a new query window by clicking the New Query button.

2. You will create a new database and the stg.Person table so that you can execute the SSIS package created in the previous exercise. Execute the provided T-SQL code to create the database and the table.

   USE master;
   IF DB_ID('TK463DWProd') IS NOT NULL
     DROP DATABASE TK463DWProd;
   GO
   CREATE DATABASE TK463DWProd
    ON PRIMARY
    (NAME = N'TK463DWProd', FILENAME = N'C:\TK463\TK463DWProd.mdf',
     SIZE = 307200KB , FILEGROWTH = 10240KB )
    LOG ON
    (NAME = N'TK463DWProd_log', FILENAME = N'C:\TK463\TK463DWProd_log.ldf',
     SIZE = 51200KB , FILEGROWTH = 10%);
   GO
   ALTER DATABASE TK463DWProd SET RECOVERY SIMPLE WITH NO_WAIT;
   GO
   USE TK463DWProd;
   GO
   CREATE SCHEMA stg AUTHORIZATION dbo;
   GO
   CREATE TABLE stg.Person
   (
    BusinessEntityID INT          NULL,
    PersonType       NCHAR(2)     NULL,
    Title            NVARCHAR(8)  NULL,
    FirstName        NVARCHAR(50) NULL,
    MiddleName       NVARCHAR(50) NULL,
    LastName         NVARCHAR(50) NULL,
    Suffix           NVARCHAR(10) NULL,
    ModifiedDate     DATETIME     NULL
   );

3. If necessary, start SQL Server Data Tools, open the TK 463 Chapter 9 project, and then open the FillStageTablesParameters.dtsx package from the previous exercise for editing.

4. Select the Configuration Manager option in the Solution Configurations drop-down list.

5. In the Configuration Manager dialog box, select the New option in the Active Solution Configuration drop-down list.

6. In the New Solution Configuration dialog box, enter Production as the name of the configuration and click OK. Close the Configuration Manager dialog box.

7. Right-click Project.params in Solution Explorer, and then click Open (or double-click Project.params).

8. Click the Add Parameter To Configurations button on the toolbar.

9. In the Manage Parameter Values dialog box, click Add. Select the pTK463DWConnString parameter and click OK. Notice that the value of the parameter was copied to both configurations.

10. Change the Production configuration to use the newly created database, TK463DWProd. The value should look like this—Data Source=localhost;Initial Catalog=TK463DWProd;Provider=SQLNCLI11.1;Integrated Security=SSPI;.

11. Click OK and save the SSIS project to store the values for the configurations.

12. Execute the package first under the Development configuration and then under the Production configuration by selecting the different values in the Solution Configurations drop-down list. Look at the stg.Person table in the TK463DWProd database to see if it contains data.

Lesson Summary

• Use parameters to set up connection properties at run time.

• Parameters and project-level connection mangers can only be used with the new project deployment model introduced with SSIS in SQL Server 2012.

• Use property expressions to change the control flow properties at run time.

Lesson Review

Answer the following questions to test your knowledge of the information in this lesson. You can find the answers to these questions and explanations of why each answer choice is correct or incorrect in the “Answers” section at the end of this chapter.

1. Which parameter types are available in SSIS in SQL Server 2012? (Choose all that apply.)

   1. Project-level parameters

   2. Solution-level parameters

   3. Control flow–level parameters

   4. Package parameters

2. Which properties can be set by using build configurations? (Choose all that apply.)

   1. Parameter values

   2. Variable values

   3. Control flow task properties

   4. The Deployment Server Name property

3. Which properties can be set by using property expressions? (Choose all that apply.)

   1. SQL statement for the Execute SQL task

   2. Variable values

   3. Data flow task properties

   4. The Lookup transformation SqlCommand property