SSIS Environment Design Patterns

In SQL Server 2012, Microsoft introduced a new deployment model in SSIS called the project deployment model. This new model has many benefits in terms of managing SSIS project deployments, executions, and configurations. Every new version of SQL Server also upgrades the SSIS Catalog with new features. In this article, I will describe SSIS environment design patterns that we can use to set up SSIS Catalog.

Shared Nothing

This is the most commonly used design pattern to set up SSIS Deployments. As the name suggests, each deployment environment (Dev, Test, Prod) has a separate SQL Server and SSISDB setup to deploy SSIS projects and configure them using SSIS Environments.

Characteristics

1. Name of the folders, projects, and environments are the same in all the deployments environments.
2. Easy to manage deployments and setup.
3. Easy to implement continuous deployment (CD) strategy using DevOps frameworks.

When to use

1. You have a dedicated SQL Server for each deployment environment to run SSIS workloads.
2. Workloads are critical and cannot share SQL Server capacity with different deployment environments.
3. SSIS code is rapidly changing.

Shared Server

In this design pattern, the same SSIS project is deployed to different folders. Folder names reflect the target deployment environment (Dev, Test, Prod)

Characteristics

1. Folder names are different for different target environments.
2. The same projects and SSIS environments are deployed to different folders.

When to use

1. You have a single SQL Server for multiple target deployment environments.
2. Workloads are less critical and less resource-intensive.
3. Code is rapidly changing.

Shared Project

In this design pattern, multiple target environments (Dev, Test) share the same SSIS project.

Characteristics

1. Each target deployment environment (Dev, Test, Prod) has its own SSIS Environment.
2. All the SSIS Environments have the same variables but different values based on the target deployment environment.
3. All environments reference to the project.
4. The environment is referred to in the scheduler or during the package execution time, depending on the target environment (Dev, Test, etc.).

When to use

1. Workloads are less critical and less resource-intensive.
2. SSIS code is not changing frequently.

We have seen different SSIS design patterns we can use to set up SSIS deployments depending on the availability of the resources.

See More

Pro tips
1. This article describes how to Open SSIS Packages from ispac file.