In our previous article we’ve seen an overview of SQL Server Integration Services (SSIS) and its benefits with a small and simple project. In that article we have also seen something about Deployment model.
In this article we’ll discuss about the deployment models available in SQL Server Integration Services (SSIS) and we’ll also see the steps needed to deploy packages in both the models.
So, let’s begin,
Integration Services supports 2 deployment models i.e.
  1. Package Deployment Model
  2. Project Deployment Model
Prior SSIS 2012, in all versions like SSIS 2005, 2008 or 2008 R2 we had ‘Package Deployment Model’. With the introduction of SQL Server 2012 or 2014, a new Deployment model introduced named ‘Project Deployment Model’.
Let’s see both deployment models in details.

Package Deployment Model

SQL Server Integration Service (SSIS) 2005, 2008 and 2008 R2 versions were using this deployment model.
The following is the example showing how we can configure our SSIS 2008 created package for deployment on another computer.
In this deployment model, to deploy any packages, we need to go through the following four steps:
Let’s take a look at each step.
Step 1: To create Package Configuration File:
In this step, create a package configuration file that updates properties of package elements at runtime. To create a package configuration file, right click anywhere on the Control Flow area and select ‘Package Configuration’ as shown below:
Package Configuration
On clicking this you’ll get Package Configuration Organizer, check ‘Enable package configuration’ option and you’ll get Add button to add your configuration as shown below.
Enable package configuration
Click on the Add button and you’ll get Package configuration Wizard as shown below.
Package configuration Wizard
Click on Next and select type of configuration you want in your package.
select type of configuration
I’m selecting XML configuration file and will save the file at desired location.
selecting XML configuration
Click on Next and select the properties you want to be exported to the configuration file.
properties
Click on Next and you’ll get a summary of your configuration file.
Click on Next
Click on Finish to finalize your configuration file creation. After doing the above steps, you’ll get something like below.
Click on finish
Now we’re done with Step 1 where we created the package configuration file.
Step 2: To create a deployment utility
At this step, we require a package deployment utility for our project, which contains the package that we want to deploy.
To create Deployment Utility, right click on your package and select Properties and you’ll get the following window:
create Deployment Utility
On this window, set CreateDeploymentUtility to ‘True’ and click on OK.
Now build your Project as shown below.
build
On successful build, you’ll get the following message in the output window.
  1. ------ Build started: Project: Project1_BIDS (package deployment model), Configuration: Development------
  2. Build started: SQL Server Integration Services project: Incremental...
  3. Creating deployment utility...
  4. Deployment Utility created.
  5. Build complete -- 0 errors, 0 warnings
  6. ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
If you go to \bin folder of your project you’ll find a newly created directory named ‘Deployment’ with some files.
bin folder
These files are nothing but your configuration file, Manifest file and your packages you want to deploy.
Step 3: Copy Deployment folder
Copy your Deployment folder under which you had built your project to the target computer on which you want to deploy your package.
Step 4: Package Installation
Install your package with help of the ‘Package Installation Wizard’ to the file system or to an instance of SQL Server.
As we can see, we’ve added 3 packages in our project and if we want to deploy those packages, we need to do it one-by-one.
So, this was some information regarding package deployment model. Now, let’s move to the next deployment model.

Project Deployment Model

The Project Deployment Model was introduced in SQL Server 2012 Integration Services. This model provides variety of features than Package Deployment Model.
The project deployment file shown above is a self contained unit of deployment that includes only the essential information about the packages and the parameters of the project.
Project Deployment Model doesn’t require any package configuration file as we did in Package Deployment. Here you can build and deploy your package under your catalog. Let’s deploy our package which we have created under Project deployment model.
After successful build, you’ll get ‘Integration Services Project Deployment File (.ispac)’ file under a bin/Deployment folder. Double click on that file and it’ll open Deployment Wizard as shown below.
Integration Services Project Deployment File
Click next and select your Project Deployment File as Source.
Project Deployment File
Click Next to choose Destination where you want to deploy your SSIS packages.
I’ve already created my Integration Service Catalog and I’ll deploy my package over there.
Integration Service Catalog
Click Next and you’ll get the summary of the deployment i.e. Source, Destination, etc.
summary of the deployment
Click on Deploy to finalize the setup. On successful deployment you’ll get the following window.
Deploy
We’ve successfully deployed our package in this Model which seems very simple as compared to Package Deployment Model.
Now, if you open Integration Services Catalog under SQL Server, you’ll find your deployed package as shown below.
package
So with this, we’re done with Deployment models available in SSIS.
Conclusion
This was the article based on Deployment Model in SSIS. In this article, we learned the types of deployment models. Also, we have seen the steps need to deploy packages in and we deployed packages via both the deployment models.
In our next article, we’ll learn to upgrade package deployment model to Project deployment model that means we’ll upgrade our older version package to higher version. Till then keep learning and sharing.
If there's any mistake in this article or I missed any points, then please let me know or feel free to add via your comments. Please provide your valuable feedback and comments that enable me to provide a better article the next time.