SSIS Environment Design Patterns

SSIS environments design pattern

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.
SSIS Environment design pattern - Shared nothing

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. 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 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

Kunal Rathi

Been working on the Microsoft data platforms for more than a decade. Helping customers transform their data into insights, Cloud & DevOps enthusiast.