Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Integration Services (SSIS) Convert from Package Deployment Model to Project Deployment Model


By:   |   Last Updated: 2016-06-03   |   Comments (2)   |   Related Tips: 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.

Project parameters in SQL Server Integration Services

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

Convert to package deployement model in SQL Server Integration Services

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

Convert to Package compatibility message in SQL Server Integration Services

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

Convert error messages in SQL Server Integration Services

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:

Parameter compatibility error in SQL Server Integration Services

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.

Package configurations option in SQL Server Integration Services

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:

Configuration type in SQL Server Integration Services

 

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:

Convert to project deployment in SQL Server Integration Services

The Integration Services Project Conversion Wizard will be displayed:

Integration Services Project Conversions wizard in SQL Server Integration Services

Select the packages that you want to convert:

Selecting packages in the Integration Services Project Conversion Wizard

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

Specifying projects in the Integration Services Project Conversion Wizard

The next step is to update the execution Tasks:

Update Exeute packages Task in the Integration Services Project Conversion Wizard

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

Select configurations in the Integration Services Project Conversion Wizard

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:

Create parameters in the Integration Services Project Conversion Wizard

You can specify and configure the values of the parameter:

Configure parameters in the Integration Services Project Conversion Wizard

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

Results in the Integration Services Project Conversion Wizard

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

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:

Project connection in SQL Server Integration Services

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

Add Connection Manager in SQL Server Integration Services

The new Connection will be created at the project level.

Connection created in SQL Server Integration Services

We will use the connection in our first package:

List of packages in SQL Server Integration Services

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

Data flow task in SQL Server Integration Services

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

OLE DB Source and Excel destination in SQL Server Integration Services

Select the project connection that we created:

Selecting a connection in SQL Server Integration Services

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 sqltxt package in SQL Server Integration Services

The tasks of the package are the following:

The Data flow tasks in SQL Server Integration Services

In the OLE DB Source, select the Project Connection:

Connection to adventureworks in SQL Server Integration Services

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


Last Updated: 2016-06-03


get scripts

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, June 02, 2018 - 2:43:35 AM - Adarsh Back To Top

While converting from project deployment to leagcy package deployment, I do not see the package configurations under SSIS tab.Not sure why that happened.I was able to convert the legacy package for the 1st time and then converted it back to project deployment, that's when I started getting the issue "To make it compatible with the package deployment model,remove the parameters"

I am using vs 2017 and sql server 2008 R2.

 


Friday, March 23, 2018 - 6:04:35 PM - Thomas Kelley Back To Top

Hi and thank you for the detailed write up regarding the project deployment model.  I have been requested by a customer to change my package to the project deployment model.  My package is called twice from a SQL job to allow a separate configuration file for each run to change the parameters accordingly.  In this example, I needed to import two sets of identically formatted text files with different file names and variable settings into a database using the same import process (hence the one package).  What is the comparable approach to handle this in the project deployment model? 

 


Learn more about SQL Server tools