Deployment Models In SQL Server Integration Services (SSIS)

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.

• In this model, package is the unit of deployment. At a time we can deploy single package not multiple like in SSIS 2012.
  
  
• Packages and configuration saved in the file system. Package with extension ‘.dtsx’ and configuration file with extension ‘.dtsConfig’.
  
  
• Packages can be deployed either in File system or under msdb database.
  
  
• Packages are validated just before execution. You can also validate a package with dtExec or managed code.
  
  
• Packages are run in a separate window process.
  
  
• During execution, events that are produced by a package are not captured automatically. A log provider must be added to the package to capture events.
  
  
• Under this model, package configuration is required for each and every package under the project.
  
  
• When we deploy same package again, it’ll overwrite the old one and due to this reason there was no way to check the previous history that how much time we’ve deployed our packages.

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:

• Step 1: Create Package Configuration File.
• Step 2: Create a deployment utility.
• Step 3: Copy Deployment folder on destination.
• Step 4: Package Installation.

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:



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.



Click on the Add button and you’ll get Package configuration Wizard as shown below.



Click on Next and select type of configuration you want in your package.



I’m selecting XML configuration file and will save the file at desired location.



Click on Next and select the properties you want to be exported to the configuration file.



Click on Next and you’ll get a summary of your configuration file.



Click on Finish to finalize your configuration file creation. After doing the above steps, you’ll get something like below.



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:



On this window, set CreateDeploymentUtility to ‘True’ and click on OK.

Now build your Project as shown below.



On successful build, you’ll get the following message in the output window.

------ Build started: Project: Project1_BIDS (package deployment model), Configuration: Development------
Build started: SQL Server Integration Services project: Incremental...
Creating deployment utility...
Deployment Utility created.
Build complete -- 0 errors, 0 warnings
========== 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.



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.

 

• In this model, the project is considered as a unit of deployment. This means we can deploy whole project.
  
  
• Projects containing packages and parameters are deployed to SSISDB catalog on SQL Server instance.
  
  
• During execution, events that are produced by the package are captured automatically and saved in the catalog.
  
  
• One disadvantage I found under this deployment model is that, you cannot deploy one or more packages without deploying the whole project. But SSIS 2016 introduced an Incremental Package Deployment feature that allows you to deploy one or more packages without deploying the whole project.
  
  
• Deployment version or deployment history can be easily maintained in this model.
  
  
• When you build a project under this deployment model, it’ll create a deployment file with .ispac extension as shown below.
  
  

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.



Click next and select your Project Deployment File as Source.



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.



Click Next and you’ll get the summary of the deployment i.e. Source, Destination, etc.



Click on Deploy to finalize the setup. On successful deployment you’ll get the following window.



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.



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.