Lesson 5: Adding Package Configurations for the Package Deployment Model

Applies To: SQL Server 2016 Preview

Package configurations let you set run-time properties and variables from outside of the development environment. Configurations allow you to develop packages that are flexible and easy to both deploy and distribute. Microsoft Integration Services offers the following configuration types:

• XML configuration file
• Environment variable
• Registry entry
• Parent package variable
• SQL Server table

In this lesson, you will modify the simple Integration Services package that you created in Lesson 4: Adding Error Flow Redirection to use the Package Deployment Model and take advantage of package configurations. You can also copy the completed Lesson 4 package that is included with the tutorial. Using the Package Configuration Wizard, you will create an XML configuration that updates the Directory property of the Foreach Loop container by using a package-level variable mapped to the Directory property. Once you have created the configuration file, you will modify the value of the variable from outside of the development environment and point the modified property to a new sample data folder. When you run the package again, the configuration file populates the value of the variable, and the variable in turn updates the Directory property. As a result, the package iterates through the files in the new data folder, rather than iterating through the files in the original folder that was hard-coded in the package.

Important
This tutorial requires the AdventureWorksDW2012 sample database. For more information about how to install and deploy AdventureWorksDW2012, see Reporting Services Product Samples on CodePlex.

Lesson Tasks

This lesson contains the following tasks:

• Step 1: Copying the Lesson 4 Package
• Step 2: Enabling and Configuring Package Configurations
• Step 3: Modifying the Directory Property Configuration Value
• Step 4: Testing the Lesson 5 Tutorial Package
  
  
  
  

  Step 1: Copying the Lesson 4 Package

  Applies To: SQL Server 2016 Preview
  
  In this task, you will create a copy of the Lesson 4.dtsx package that you created in Lesson 4. Alternatively, you can add the completed lesson 4 package that is included with the tutorial to the project, and then copy it instead. You will use this new copy throughout the rest of Lesson 5.

  To copy the Lesson 4 package

  1. If SQL Server Data Tools is not already open, click Start, point to All Programs, point to Microsoft SQL Server 2012, and then click SQL Server Data Tools.
  2. On the File menu, click Open, click Project/Solution, select SSIS Tutorial and click Open, and then double-click SSIS Tutorial.sln.
  3. In Solution Explorer, right-click Lesson 4.dtsx, and then click Copy.
  4. In Solution Explorer, right-click SSIS Packages, and then click Paste.
     By default, the copied package is named Lesson 5.dtsx.
  5. In the Solution Explorer, double-click Lesson 5.dtsx to open the package.
  6. Right-click anywhere in the background of the Control Flow tab then click Properties.
  7. In the Properties window, update the Name property to Lesson 5.
  8. Click the box for the ID property, then click the dropdown arrow, and then click <Generate New ID>.

  To add the completed Lesson 4 package

  1. Open SQL Server Data Tools and open the SSIS Tutorial project.
  2. In Solution Explorer, right-click SSIS Packages, and click Add Existing Package.
  3. In the Add Copy of Existing Package dialog box, in Package location, select File system.
  4. Click the browse (…) button, navigate to Lesson 4.dtsx on your machine, and then click Open.
     To download all of the lesson packages for this tutorial, do the following.
     

     1. Navigate to Integration Services Product Samples
     2. Click the DOWNLOADS tab.
     3. Click the SQL2012.Integration_Services.Create_Simple_ETL_Tutorial.Sample.zip file.
  5. Copy and paste the Lesson 4 package as described in steps 3-8 in the previous procedure.
     
     

     Step 2: Enabling and Configuring Package Configurations

     Applies To: SQL Server 2016 Preview
     

     In this task, you will convert the project to the Package Deployment Model and enable package configurations using the Package Configuration Wizard. You will use this wizard to generate an XML configuration file that contains configuration settings for the Directory property of the Foreach Loop container. The value of the Directory property is supplied by a new package-level variable that you can update at run time. Additionally, you will populate a new sample data folder to use during testing.

     To create a new package-level variable mapped to the Directory property

     1. Click the background of the Control Flow tab in SSIS Designer. This sets the scope for the variable you will create to the package.
     2. On the SSIS menu, select Variables.
     3. In the Variables window, click the Add Variable icon.
     4. In the Name box, type varFolderName.
        

        Important
        Variable names are case sensitive.

     5. Verify that the Scope box shows the name of the package, Lesson 5.
     6. Set the value of the Data Type box of the varFolderName variable to String.
     7. Return to the Control Flow tab and double-click the Foreach File in Folder container.
     8. On the Collection page of the Foreach Loop Editor, click Expressions, and then click the ellipsis button (…).
     9. In the Property Expressions Editor, click in the Property list, and select Directory.
     10. In the Expression box, click the ellipsis button (…).
     11. In the Expression Builder, expand the Variables folder, and drag the variable User::varFolderName to the Expression box.
     12. Click OK to exit the Expression Builder.
     13. Click OK to exit the Property Expressions Editor.
     14. Click OK to exit the Foreach Loop Editor.

     To enable package configurations

     1. On the Project Menu, click Convert to Package Deployment Model.
     2. Click OK on the warning prompt and, once the conversion is complete, click OK on the Convert to Package Deployment Model dialog.
     3. Click the background of the Control Flow tab in SSIS Designer.
     4. On the SSIS menu, click Package Configurations.
     5. In the Package Configurations Organizer dialog box, select Enable Package Configurations, and then click Add.
     6. On the welcome page of the Package Configuration Wizard, click Next.
     7. On the Select Configuration Type page, verify that the Configuration type is set to XML configuration file.
     8. On the Select Configuration Type page, click Browse.
     9. By default, the Select Configuration File Location dialog box will open to the project folder.
     10. In the Select Configuration File Location dialog box, for File name type SSISTutorial, and then click Save.
     11. On the Select Configuration Type page, click Next.
     12. On the Select Properties to Export page, in the Objects pane, expand Variables, expand varFolderName, expand Properties, and then select Value.
     13. On the Select Properties to Export page, click Next.
     14. On the Completing the Wizard page, type a configuration name for the configuration, such as SSIS Tutorial Directory configuration. This is the configuration name that is displayed in the Package Configuration Organizer dialog box.
     15. Click Finish.
     16. Click Close.
     17. The wizard creates a configuration file, named SSISTutorial.dtsConfig, that contains configuration settings for the value of the variable that in turn sets the Directory property of the enumerator.
         

         Note
         A configuration file typically contains complex information about the package properties, but for this tutorial the only configuration information should be <Configuration ConfiguredType="Property" Path="\Package.Variables[User::varFolderName].Properties[Value]" ValueType="String"> <ConfiguredValue></ConfiguredValue> </Configuration>.

     To create and populate a new sample data folder

     1. In Windows Explorer, at the root level of your drive (for example, C:\), create a new folder named New Sample Data.
     2. Locate the sample files on your computer and copy three of the files from the folder.
     3. In the New Sample Data folder, paste the copied files.
        
        

     Step 3: Modifying the Directory Property Configuration Value

      
     Applies To: SQL Server 2016 Preview
     

     In this task, you will modify the configuration setting, stored in the SSISTutorial.dtsConfig file, for the Value property of the package-level variable User::varFolderName. The variable updates the Directory property of the Foreach Loop container. The modified value will point to the New Sample Data folder that you created in the previous task. After you modify the configuration setting and run the package, the Directory property will be updated by the variable, using the value populated from the configuration file instead of the directory value originally configured in the package.

     To modify the configuration setting of the Directory property

     1. In Notepad or any other text editor, locate and open the SSISTutorial.dtsConfig configuration file that you created by using the Package Configuration Wizard in the previous task.
     2. Change the value of the ConfiguredValue element to match the path of the New Sample Data folder that you created in the previous task. Do not surround the path in quotes. If the New Sample Data folder is at the root level of your drive (for example, C:\), the updated XML should be similar to the following sample:
        <?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading><DTSConfigurationFileInfo GeneratedBy="DOMAIN\UserName" GeneratedFromPackageName="Lesson 5" GeneratedFromPackageID="{F4475E73-59E3-478F-8EB2-B10AFA61D3FA}" GeneratedDate="6/10/2012 8:16:50 AM"/></DTSConfigurationHeading><Configuration ConfiguredType="Property" Path="\Package.Variables[User::varFolderName].Properties[Value]" ValueType="String"><ConfiguredValue></ConfiguredValue></Configuration></DTSConfiguration>
        The heading information, GeneratedBy, GeneratedFromPackageID, and GeneratedDate will be different in your file, of course. The element to note is the Configuration element. The Value property of the variable, User::varFolderName, now contains C:\New Sample Data.
     3. Save the change, and then close the text editor.

   

  Step 4: Testing the Lesson 5 Tutorial Package

  Applies To: SQL Server 2016 Preview
  
  At run time, your package will obtain the value for the Directory property from a variable updated at run time, rather than using the original directory name that you specified when you created the package. The value of the variable is populated by the SSISTutorial.dtsConfig file.
  To verify that the package updates the Directory property with the new value during run time, simply execute the package. Because only three sample data files were copied to the new directory, the data flow will run only three times, rather than iterate through the 14 files in the original folder.

  Checking the Package Layout

  Before you test the package you should verify that the control and data flows in the Lesson 5 package contains the objects shown in the following diagrams. The control flow should be identical to the control flow in lesson 4. The data flow should be identical to the data flow in lessons 4.
  Control Flow
  

  

  Data Flow
  

  

  To test the Lesson 5 tutorial package

  1. On the Debug menu, click Start Debugging.
  2. After the package has completed running, on the Debug menu, and then click Stop Debugging.

   

  Lesson 6: Using Parameters with the Project Deployment Model