Lesson 6: Using Parameters with the Project Deployment Model

Applies To: SQL Server 2016 Preview

SQL Server 2012 introduces a new deployment model where you can deploy your projects to the Integration Services server. The Integration Services server enables you to manage and run packages, and to configure runtime values for packages.
In this lesson, you will modify the package that you created in Lesson 5: Adding Package Configurations for the Package Deployment Model to use the Project Deployment Model. You replace the configuration value with a parameter to specify the sample data location. You can also copy the completed Lesson 5 package that is included with the tutorial.
Using the Integration Services Project Configuration Wizard, you will convert the project to the Project Deployment Model and use a Parameter rather than a configuration value to set the Directory property. This lesson partially covers the steps you would follow to convert existing SSIS packages to the new Project Deployment Model.
When you run the package again, the Integration Services service uses the parameter to populate 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 specified by the parameter value rather than the folder that was set in the package configuration file.

Important
This tutorial requires the AdventureWorksDW2012 sample database. For more information about how to install and deploy AdventureWorksDW2012, see Considerations for Installing SQL Server Samples and Sample Databases.

Lesson Tasks

This lesson contains the following tasks:

1. Step 1: Copying the Lesson 5 Package
2. Step 2: Converting the Project to the Project Deployment Model
3. Step 3: Testing the Lesson 6 Package
4. Step 4: Deploying the Lesson 6 Package
   
   

   Step 1: Copying the Lesson 5 Package

   
   Applies To: SQL Server 2016 Preview
   
   In this task, you will create a copy of the Lesson 5.dtsx package that you created in Lesson 5. Alternatively, you can add the completed lesson 5 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 6.

   To copy the Lesson 5 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 5.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 6.dtsx.
   5. In the Solution Explorer, double-click Lesson 6.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 6.
   8. Click the box for the ID property, then click the dropdown arrow, and then click <Generate New ID>.

   To add the completed Lesson 5 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, Lesson 5.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 5 package as described in steps 3-8 in the previous procedure.
      After copying the Lesson 5 package, if you currently have the packages from the previous lessons in your solution, right-click each package from lessons 1-5 and click Exclude From Project. When done you should have only Lesson 6.dtsx in your solution.
      
      

      Step 2: Converting the Project to the Project Deployment Model

      Applies To: SQL Server 2016 Preview
      

      In this task, you will use the Integration Services Project Conversion Wizard to convert the project to the Project Deployment Model.

      Converting the Project to the Project Deployment Model

      1. On the Project Menu, click Convert to Project Deployment Model.
      2. On the Integration Services Project Conversion Wizard Introduction page, review the steps then click Next.
      3. On the Select Packages page, in the Packages list, clear all checkboxes except Lesson 6.dtsx then click Next.
      4. On the Specify Project Properties page, click Next.
      5. On the Update Execute Package Task page click Next.
      6. On the Select Configurations page, make sure the Lesson 6.dtsx package is selected in the Configurations list, then click Next.
      7. On the Create Parameters page make sure the Lesson 6.dtsx package is selected, and the Scope is set to Package, in the Configuration Properties List, then Click Next.
      8. On the Configure Parameters page verify that the values for Name and Value are the same name and value specified in Lesson 5 for the variable and configuration value, then click Next.
      9. On the Review page, in the Summary pane, notice that the wizard has used the information from the configuration file to set the Properties to be converted.
      10. Click Convert.
          When the conversion completes a message is displayed warning that the changes are not saved until the project is saved in Visual Studio. Click OK on the warning dialog.
      11. On the Integration Services Project Conversion Wizard click Close.
      12. In SQL Server Data Tools, click the File menu, then click Save to save the converted package.
      13. Click the Parameters Tab and verify that the package now contains a parameter for VarFolderName and that the value is the same path specified for the New Sample Data folder from the Lesson 5 configuration file.

      
      

      Step 3: Testing the Lesson 6 Package

       

      Applies To: SQL Server 2016 Preview
      

      At run time, your package will obtain the value for the Directory property from the VarFolderName parameter.
      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 6 package contains the objects shown in the following diagrams. The control flow should be identical to the control flow in lesson 5. The data flow should be identical to the data flow in lesson 5.
      Control Flow
      

      

      Data Flow
      

      

      TO test the Lesson 6 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.

       

      Step 4: Deploying the Lesson 6 Package

      Applies To: SQL Server 2016 Preview
      

      Deploying the package involves adding the package to the SSISDB catalog in Integration Services on an instance of SQL Server. In this lesson you will add the Lesson 6 package to the SSISDB catalog, set the parameter, and execute the package. For this lesson you will use SQL Server Management Studio to add the Lesson 6 package to the SSISDB catalog, and deploy the package. After deploying the package you will modify the parameter to point to a new location then execute the package.
      In this lesson you will:
      

      • Add the package to the SSISDB catalog in the SSIS node in SQL Server.
      • Deploy the package.
      • Set the package parameter value.
      • Execute the package in SSMS.

      To Locate or add the the SSISDB catalog

      1. Click Start, point to All Programs, point to Microsoft SQL Server 2012, and then click SQL Management Studio.
      2. On the Connect to Server dialog box, verify the default settings, and then click Connect. To connect, the Server name box must contain the name of the computer where SQL Server is installed. If the Database Engine is a named instance, the Server name box should also contain the instance name in the format <computer_name>\<instance_name>.
      3. In Object Explorer expand Integration Services Catalogs.
      4. If there are no catalogs listed under Integration Services Catalogs then add the SSISDB catalog.
      5. To Add the SSISDB catalog, right-click Integration Services Catalogs and click Create Catalog.
      6. On the Create Catalog dialog box select Enable CLR Integration.
      7. In the Password box, type a new password then type it again in the Retype Password box. Be sure to remember the password you type.
      8. Click OK to add the SSISDB catalog.

      To add the package to the SSISDB catalog

      1. In Object Explorer, right-click SSISDB and click Create Folder.
      2. In the Create Folder dialog box type SSIS Tutorial in the Folder name box and click OK.
      3. Expand the SSIS Tutorial folder, right-click Projects, and click Import Packages.
      4. On the Integration Services Project Conversion Wizard Introduction page click Next.
      5. On the Locate Packages page, ensure that File system is selected in the Source list, then click Browse.
      6. On the Browse For Folder dialog box, browse to the folder containing the SSIS Tutorial project, then click OK.
      7. Click Next.
      8. On the Select Packages page you should see all six packages from the SSIS Tutorial. In the Packages list, select Lesson 6.dtsx, then click Next.
      9. On the Select Destination page, type SSIS Tutorial Deployment in the Project Name box then click Next.
      10. Click Next on each of the remaining wizard pages until you get to the Review page.
      11. On the Review page, click Convert.
      12. When the conversion completes, click Close.

      When you close the Integration Services Project Conversion Wizard, SSIS displays the Integration Services Deployment Wizard. You will use this wizard now to deploy the Lesson 6 package.

      1. On the Integration Services Deployment Wizard Introduction page, review the steps for deploying the project, then click Next.
      2. On the Select Destination page verify that the server name is the instance of SQL Server containing the SSISDB catalog and that the path shows SSIS Tutorial Deployment, then click Next.
      3. On the Review page, review the Summary then click Deploy.
      4. When the deployment completes, click Close.
      5. In Object Explorer, right-click Integration Services Catalogs and click Refresh.
      6. Expand Integration Services Catalogs then expand SSISDB. Continue to Expand the tree under SSIS Tutorial until you have completely expanded the project. You should see Lesson 6.dtsx under the Packages node of the SSIS Tutorial Deployment node.

      To verify that the package is complete, right-click Lesson 6.dtsx and click Configure. On the Configure dialog box, select Parameters and verify that there is an entry with Lesson 6.dtsx as the Container, VarFolderName as the Name and the path to New Sample Data as the value, then click Close.
      Before continuing create a new sample data folder, name it Sample Data Two, and copy any three of the original sample files into it.

      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 Sample Data Two.
      2. Open the c:\Program Files\Microsoft SQL Server\110\Samples\Integration Services\Tutorial\Creating a Simple ETL Package\Sample Data folder and then copy any three of the sample files from the folder.
      3. In the New Sample Data folder, paste the copied files.

      To change the package parameter to point to the new sample data

      1. In Object Explorer, right click Lesson 6.dtsx and click Configure.
      2. On the Configure dialog box, change the parameter value to the path to Sample Data Two. For example C:\Sample Data Two if you placed the new folder in the root folder on the C drive.
      3. Click OK to close the Configure dialog box.

      To test the Lesson 6 package deployment

      1. In Object Explorer, right click Lesson 6.dtsx and click Execute.
      2. On the Execute Package dialog box, click OK.
      3. On the message dialog box click Yes to open Overview Report.

      The Overview report for the package is displayed showing the name of the package and a status summary. The Execution Overview section shows the result from each task in the package and the Parameters Used section shows the names and values of all parameters used in the package execution, including VarFolderName.