The SSIS project deployment model uses the SSISDB catalog to manage projects, packages, parameters, environments, and executions. Environment variables let you keep environment-specific values outside the deployed package, making the same SSIS project reusable across Development, Test, and Production.
Quick answer: There are three practical SSIS environment design patterns:
- Shared Nothing: Separate SQL Server and SSISDB catalog for each environment.
- Shared Server: One SQL Server and SSISDB catalog, with separate folders for each environment.
- Shared Project: One deployed project is reused across environments, with separate SSIS environments providing environment-specific values.
Choose the pattern based on workload isolation, infrastructure availability, deployment frequency, and operational requirements.
1. Shared Nothing
Each deployment environment has its own SQL Server and SSISDB catalog. Projects, packages, and environments are deployed independently to Development, Test, and Production.
Characteristics
- Folder, project, and environment names can remain consistent across environments.
- Each environment has dedicated SQL Server and SSISDB resources.
- Deployment and runtime failures are isolated between environments.
- Works well with automated CI/CD pipelines because each target is independently deployable.
When to use
- Each environment requires dedicated SQL Server capacity.
- Production workloads require strong resource and operational isolation.
- Different environments need independent maintenance or release schedules.
- The additional infrastructure cost is acceptable.
2. Shared Server
Multiple deployment environments use the same SQL Server and SSISDB catalog, but each environment is isolated in its own SSISDB folder. For example, Dev, Test, and Prod folders can contain their respective projects and environments.
Characteristics
- One SQL Server hosts the SSISDB catalog for multiple environments.
- Folders provide a logical boundary between environments.
- Projects and SSIS environments can be deployed separately to each folder.
- SSISDB permissions can be managed at the folder boundary.
When to use
- A single SQL Server is available for multiple environments.
- Workloads are small enough to share server capacity.
- You want logical separation without maintaining separate SQL Server instances.
- Infrastructure cost and operational simplicity are more important than full resource isolation.
Available on Microsoft Store
SSRS Reports Migration Wizard
A simple Windows tool for migrating SSRS reports, data sources, and related configurations between report servers.
3. Shared Project
In this pattern, the same deployed SSIS project is used across multiple target environments. Each environment has its own SSIS environment containing the values required by that environment.
Characteristics
- Development, Test, and Production environments can use the same project.
- Each SSIS environment contains the same variable names with environment-specific values.
- The project is configured with references to the required environments.
- The environment reference is selected when the package execution is configured.
- A single package execution can use variables from only one environment.
When to use
- The same project should be promoted across environments with minimal deployment differences.
- Environment-specific values can be externalized into SSIS environment variables.
- Workloads are suitable for the available shared infrastructure.
- The project structure and package logic remain consistent across environments.
How to choose an SSIS environment pattern
| Pattern | Infrastructure | Isolation | Typical use |
|---|---|---|---|
| Shared Nothing | Separate SQL Server / SSISDB per environment | High | Critical or resource-intensive workloads |
| Shared Server | One SQL Server / SSISDB with separate folders | Logical | Multiple environments with shared capacity |
| Shared Project | Project reused across environments | Depends on infrastructure pattern | Consistent project promotion with environment-specific values |
SSIS environment best practices
- Keep environment-specific connection strings, paths, and other runtime values in environment variables rather than hard-coding them in packages.
- Use the project deployment model when you want to manage environments through SSISDB.
- Use consistent project and parameter names across environments to simplify deployment automation.
- Protect sensitive environment variables and restrict access to the SSISDB objects that contain them.
- For automated deployments, validate the project and environment references in the target environment before execution.
SSIS environments are part of the project deployment model and can be referenced by projects to resolve parameter values at execution time. A project can reference multiple environments, but a package execution uses values from a single environment reference.
Pro tips:
1. Follow this SSIS Catalog migration guide for a step-by-step guide of migrating SSISDB between SQL Server environments.
2. Check whether your SSIS Catalog is migration-ready before starting a migration.
3. Learn how to open SSIS packages from an ISPAC file.
See more
Visual Studio Marketplace
SSIS Catalog Migration Wizard
Extend Visual Studio with an easy way to migrate SSIS Catalog projects.
Kunal Rathi
With over 15 years of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.
