Convert SQL Server Integration Services Package from Deployment Model to Project Deployment Model

By: Daniel Calbimonte   |   Comments (2)   |   Related: More > Integration Services Development

Problem

I have been working with SQL Server Integration Services (SSIS) 2008 and I heard that SSIS 2012, 2014 and 2016 has a Project Deployment Model. Can you explain how this new functionality works?

Solution

Of course! We are here to help.

Requirements

1. SQL Server 2012 or 2014 installed.
2. SQL Server Integration Services (SSIS) installed.
3. SQL Server Data Tools  (SSDT) or BIDS (Business Intelligence Development Studio) installed.
4. We are also using the Adventureworks2014 database, but this is optional.

Introduction

SQL Server 2005 and 2008 use the legacy Package Deployment Model. This model is based on the package, which is the unit of deployment. However, SSIS packages usually interact with others and it is necessary to pass connection and parameters from one package to another. The new model is the Project Deployment Model which means deployment is per project and not per package.

In SQL Server 2008, we store all the information in configuration files. Now we store the configuration information in project connections and project parameters. In this tip, we will show how to:

• Convert Project Deployment Model to legacy Package Deployment Model
• Convert legacy Package Deployment Model to Project Deployment Model
• Create a Project connection and use it in several packages

Convert Project Deployment Model to Legacy Package Deployment Model in SSIS

Open SSDT and create a SSIS project.  In the solution parameters, add some parameters to the project.

In order to convert this Project Deployment Model to the legacy Package Deployment Model, go to Project > Convert to Package Deployment Model:

You will receive an error message related to the compatibility check. It will verify if the package can be converted:

Since we are using Project parameters, it is not possible to convert to the old model. You will receive error messages like these:

If you check the messages, you will notice that these new features are not compatible with the Package Deployment Model. This is because Project Parameters are a new feature not supported in the old Package Deployment Model:

Remove the project parameters in the package and try again. If everything is OK, the menu will show the Package Configurations option. This option is only available when you convert from Project Model to Package Model.

Create an XML configuration file and finish the wizard (for more information about creating a configuration file, review this tip. In Package Deployment Models, the configuration files are used to store configurations. You can store the information in the registry, XML files, environment variables, parent package variables and SQL Server:

Convert Legacy Package Deployment Model to Project Deployment Model in SSIS

Now we are going to convert the legacy Package Deployment Model to the Project Deployment Model. To do this, go to Project > Convert to Project Deployment Model:

The Integration Services Project Conversion Wizard will be displayed:

Select the packages that you want to convert:

Specify the project name and the protection level (by default, the EncryptSensitiveWithUserKey is used):

The next step is to update the execution Tasks:

You can select the existing configurations (the XML file that we created).

In Create Parameters, we will create a project parameter to store the information of the XML configuration file of the previous step. You can assign a name and the scope for the parameter:

You can specify and configure the values of the parameter:

In the Results interface, you will be able to see if there are errors or not:

Once converted you will notice in the menu that the option to create Package Configuration disappeared:

Create an SSIS Project Connection and use it in Several Packages

In this new example, we are going to create a project connection and use it in two different packages in the same project. One package is to import from SQL Server to Excel and the other one to import from SQL Server to a flat file.

In the Project Deployment Model, you can create connections at the project level. In the Solution Explorer, in Connection Managers, select the New Connection Manager option:

Select the type of connection of your project. In this example, we will create an OLEDB connection:

The new Connection will be created at the project level.

We will use the connection in our first package:

The SqlExcel.dtsx is a package used to export data from SQL Server to Excel. It has a Data Flow:

Inside the Data Flow it contains an OLE DB Source and an Excel destination:

Select the project connection that we created:

In the same way, we can use the project connection in the other package. This package imports data from SQL Server to a flat txt file.

The tasks of the package are the following:

In the OLE DB Source, select the Project Connection:

Conclusion

In this tip, we showed how to convert Package Deployment Models to Project Deployment Models and vice versa. We also learned how to use Project Parameters. These Parameters are available in the Project Deployment Model.

Next Steps

• For more details about the deployment models and configuration files you can review these links:
  • Using XML Package Configurations with Integration Services SSIS
  • Deployment of Projects and Packages
  • Create Parameters
• Review all of the SQL Server Integration Services tips on MSSQLTips.com.

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips