Chapter 7

Upgrading Packages to SQL Server 2012

With every new version of Integration Services, there is a path you must follow to upgrade your existing solution to the latest version of the tool. In SQL Server 2012, you can upgrade your 2005 or 2008 SSIS package using the SSIS Package Upgrade Wizard.

The SSIS Package Upgrade Wizard fully upgrades your packages, but you should note a few things upon the completion of the upgrade. The wizard automatically updates connection strings that use an outdated data provider to the latest drivers that come natively with SQL Server. This is a nice feature, but if the packages you are upgrading use configurations to overwrite data source information, as described in Section 7 of this book, you must manually upgrade the providers in the configurations; this wizard, by default, does not upgrade configuration values.

The wizard also does not upgrade packages to utilize many of the new features available in SSIS 2012. For example, it does not upgrade Execute Package Tasks to use the new internal project reference. Also, after converting, the wizard leaves your packages set to the old package deployment model. New packages that are developed in SSIS use the project deployment model, which is detailed in Lesson 8.

You can invoke the SSIS Package Upgrade Wizard by simply opening your packages in SQL Server Data Tools (SSDT) or you can upgrade your packages manually by running SSISUpgrade.exe, the SSIS Package Upgrade Wizard executable (Figure 7-1). If you’ve installed SQL Server in the default paths, you will find the upgrade wizard in the folder location C:Program FilesMicrosoft SQL Server110DTSBinnSSISUpgrade.exe.

Figure 7-1

c07f001.tif

The SSIS Package Upgrade Wizard walks you through selecting the packages targeted for upgrade and then enables you to apply a set of rules that the wizard should adhere to during the conversion. Figure 7-2 shows the Select Package Management Options screen where you can configure the conversion rules.

Figure 7-2

c07f002.tif

Try It

In this Try It, you convert an existing ETL solution that loads a datamart from SSIS 2008R2 to an SSIS 2012 solution. The solution includes several packages for loading dimension tables, a fact table, and a master package that runs all of these in the correct order. After this lesson, you will know how to use the SSIS Package Upgrade Wizard to upgrade packages to SSIS 2012.

You can download the complete project both prior to conversion (Lesson 7.zip) and following the conversion (Lesson 7_Completed.zip) from www.wrox.com.

Lesson Requirements

Make the following changes to convert the SSIS project to SSIS 2012. You can also find the fully converted Lesson 7 project at www.wrox.com:

  • Download the completed SSIS 2008R2 project that will be converted from www.wrox.com, and then unzip and save it to C:ProjectsSSISPersonalTrainer.
  • Use SSDT to convert the packages.
  • Do not execute the packages after completing the conversion.

Hints

  • Open the solution in SSDT and use the SSIS Package Upgrade Wizard to convert the packages.

Step-by-Step

1. Open the Datamart Load.sln file in SQL Server Data Tools. This file is in the folder C:ProjectsSSISPersonalTrainerLesson 7, which you downloaded from www.wrox.com.
2. Open the solution file to bring up the Visual Studio Conversion Wizard. Click Next when the welcome screen appears.
3. The Visual Studio wizard creates a backup of your original files by default. On the Choose Whether To Create a Backup screen, accept the default location for storing a backup of the original files and then click Next.
4. Click Finish to complete the Visual Studio upgrade and begin the SSIS Package Upgrade Wizard.
5. When the SSIS Package Upgrade Wizard welcome screen appears, click Next.
6. Select the packages targeted for the upgrade. Figure 7-3 shows all packages selected, but you can uncheck packages you do not want to upgrade. Also, if any package had been encrypted with a password, you would enter that password here. These packages do not have a password, so click Next.

Figure 7-3

c07f003.tif
7. On the Select Package Management Options screen, use the default conversion rules as shown in Figure 7-4 and click Next.

Figure 7-4

c07f004.tif
8. Before the conversion starts, a confirmation screen appears that shows the work the wizard is about to begin. Click Finish to begin the upgrade.
9. A successful conversion should match Figure 7-5. Click Close to end the SSIS Package Upgrade Wizard. You may have to click Close a second time to end the Visual Studio Conversion Wizard as well. You can now explore the converted packages in the Solution Explorer, but do not execute them because they reference nonexistent tables.

Figure 7-5

c07f005.tif
Please select Lesson 7 on the DVD, or online at www.wrox.com/go/ssis2012video, to view the video that accompanies this lesson.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset