This is the 21st day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Note: This section mainly introduces the SSIS project upgrade (upgrade to SSIS2012). So there is no focus on the detailed operation of each step (the personal demo is using SSIS 2012 from the beginning). But take a closer look at the transformation of the project deployment model.

In this article, the first SSIS project will be converted to SSIS2012 using EITHER SQL Server Data Tools — Business Intelligence (SSDT-BI) or Visual Studio 2012. You can view the new features in SSIS 2012 here. If you want to use the new SSIS 2012 Catalog (Catalog), your project must follow the Project Deployment Model (demonstrated in this article).

Convert to SSIS 2012 using SSDT-BI

In part 13 of this series, an OVERVIEW of SSIS variables, we created an SSIS project (My_Second_SSIS_Project) from scratch. First, we used SQL Server Data Tools — Business Intelligence (SSDT-BI) to transform the previous SSIS project named My_First_SSIS_Project into SSIS 2012.

There are at least two ways to perform SSIS project transformation: You can Open the solution file (*.sln) from Windows Explorer through the Open With menu, or you can start SQL Server Data Tools-BI, and then Open the SSIS solution from the Open Project link on the start page. In the SSIS Variables Overview, you demonstrated how to create a new SSIS project from the start page, a process very similar to transforming an SSIS solution using the “Open Project” link. Windows Explorer is used here for the demonstration.

The following figure shows this approach — open the Visual Studio solution file using the Open Method menu in Windows Explorer — my_first_SSis_project.sln:

I prefer to start SSDT-BI and then open the SSIS project (or solution) file to convert. In Windows 8/2012, open SQL Server Data Tools in Visual Studio 2012, as shown in the figure:

Start Page has a lot of help information. You can view the Start Page by clicking Start Page on the View drop-down menu, as shown in the figure below:

The start page includes a link to Open an existing Project (” Open Project…” ), as shown in the figure:

Ssdt-bi will transform an EARLIER version of the SSIS project created. Click the Open Project link and then browse to the My_First_SSIS_Project SSIS Project file (or solution file) as shown:

Ssdt-bi warning, this is a one-way upgrade. Once the SSIS project named My_First_SSIS_Project has been upgraded to ssDT-BI 2012 format, it will not be accessible from Business Intelligence Development Studio (BIDS).

When you open the SSIS project built in previous versions, you will see the dialog shown in Main:

Going back in time (2008), it was possible to accidentally update the SSIS project from SSIS 2005 to SSIS 2008. This can be annoying to some developers because, in practice, there is little or no warning that an upgrade is imminent. Developers complained (which, of course, was a tragic oversight), and Microsoft responded by adding a screen with this warning message to make sure developers knew that the upgrade was irreversible.

After the SSIS project is upgraded, you will no longer be able to open the SSIS project in the previous development environment. If you want to maintain an earlier version of an SSIS project, you need to make a copy of the project before upgrading. For a better approach, consider using source control.

Each time the SSIS project is opened from a non-local disk source in SSDT-BI, the “security warning” shown below displays:

After confirming the security warning, the “Migration Report” will display in the default Web browser, as shown in the figure below:

The SSIS Software Package Upgrade wizard (shown in Figure 9) starts when the migration report is displayed:

Click the Next button to continue. The next screen contains a list of SSIS packages to upgrade, as shown. By default, all packages should be checked, and the default Settings should be accepted:

After selecting the software package you want to upgrade, click Next to continue. The options for managing upgrades are then displayed, leaving the default options:

I rarely change the default on the Select Package Management Options screen. When making changes, one or both of “Validate upgrade packages”, “Create new Package ID” (” Validate Upgrade Packages “, “Create New Package ID”) are usually added. There is no need to update the provider name in the connection string unless you upgrade all the servers connected to the SSIS package at the same time (although I haven’t had a problem upgrading provider names over the years because they are backward compatible). The package that validates the upgrade is “double-checked”. The upgrade will be known at the end of the operation. I might choose to create a new package ID to keep my SSIS package ID value unique across the enterprise.

At execution time — from the file system, MSDB database, or SSIS package store — you can set an option to validate the package ID. If the SSIS package is executed with this validation, the original package ID may be retained. Usually, I check the continue upgrade process when the package fails check box. If you have started this process, you want it to upgrade as many packages as possible. If you fail to upgrade one or more SSIS package files, you can manually upgrade or rebuild directly. Always leave the Ignore Configuration check box selected to manually convert the SSIS package configuration to package (or project) parameters after the package upgrade is complete. Note: Package configuration can still be configured and used in SSIS 2012.

Click the Next button to continue. This wizard will execute for a few seconds (or minutes), depending on the number of SSIS packages to upgrade and their complexity. After completion, the “Upgrade Status” interface will be displayed, as shown in the figure:

Deployment Models

Open “My_First_SSIS_Project” — the SSIS project just converted, as shown:

In Solution Explorer, notice the text “(Package Deployment Model)” to the right of the project name, as shown:

The Microsoft SSIS team has done an excellent job of supporting backward compatibility in SSIS 2012. The deployment model is a big part of this backward compatibility. When importing SSIS packages from SSIS 2008, the behavior will be exactly the same as in 2008 due to the “package deployment model”.

The Package Deployment Model is the name Microsoft uses for the Deployment, execution, and management of SSIS packages in the 2005 to 2008 R2 version of SSIS. SSIS 2012 support package deployment model, that is, SSIS 2008 package can be run in SSIS 2012. There are some warnings (not always warnings), but they are well known and relatively easy to resolve. For example, SSIS packages executed in the Package Deployment Model cannot take advantage of the SSIS 2012 Catalog, You cannot use project-level Connection Managers or Project or Package Parameters.

The default Deployment Model for SSIS 2012 is the Project Deployment Model. Microsoft provides wizards for transforming between models. To convert My_First_SSIS_Project to Project Deployment Model, right-click the Project name in Solution Explorer, Then click Convert to Project Deployment Model as shown:

Right-click and convert the project deployment model to the package deployment model

Start the Project Conversion Wizard as shown in the figure below:

The first step in the project Transformation wizard is to select the package:

The next step is to specify the project properties — the project Protection Level and Description properties:

If the SSIS project contains “execute package tasks”, they will be upgraded in the next step:

The new project deployment model is the primary reason for updating the Execution package task. SSIS 2012″ Execute package Task “contains a new property: Reference Type.

As with previous SSIS versions, subpackages can be executed from either a file system or an MSDB database. The Reference Type property must be set to External Reference in order to execute subpackages stored at these locations. To Reference subpackages contained in the same Project as the parent package, set Reference Type to Project Reference.

By using this step in the Project Transformation Wizard, you can assign external references to the package deployment model to project references to the Project Deployment model.

The next two steps of the wizard enable us to convert Package Configuration values to Parameters, starting with the following image:

The Configurations interface shown above and the Parameters interface shown below do not apply to My_First_SSIS_Project because we have not configured Package Configurations.

Since no parameters were created in the previous step, there are no parameters to configure in the steps shown below:

Now we are ready to perform the transformation as shown:

When the conversion is complete, a report screen displays the results of the conversion.

At the same time, a dialog box is displayed informing the user that the changes will not be saved until the project is saved. The best way to save a project is to save everything (click file -> Save All).

After converting the SSIS project, it will appear in Solution Explorer with no text in parentheses to the right of the project name, as shown:

Note several new virtual objects in solution Explorer: project.params and “Connection Managers”.

conclusion

In this article, we use SQL Server 2012 Data Tools — BI to transform the first SSIS project — My_First_SSIS_Project — into SSIS 2012; The imported SSIS project is then transformed from package deployment model to Project Deployment Model.

Translation reference

An Overview of Project Conversion – Level 14 of The Stairway to Integration Services An Overview of Project Conversion – Level 14 of The Stairway to Integration Services aims to have a comprehensive and clear understanding of SSIS. All contents are carried out on the basis of the original text. Due to various reasons such as version differences and personal doubts, the principle of complete translation is not adopted. Hopefully eventually it will be more conducive to learning and understanding SSIS,

Thanks for your support!