SSIS Project Deployment Model Overview
By Tom Nonmacher
The SQL Server Integration Services (SSIS) Project Deployment Model, introduced in SQL Server 2012, is a significant shift from the traditional package deployment model. This model offers several advantages, including parameters, project-level connection managers, and the ability to deploy the entire project with one action. Available in SQL Server 2019, this model is an essential tool for ETL professionals and developers working with SQL Server.
In the SSIS Project Deployment Model, packages are no longer standalone entities, but part of a larger unit called a project. This project contains one or more packages, connection managers, and parameters that can be shared across all packages in the project. This model results in consolidated, organized, and streamlined ETL processes.
Project parameters, a new feature in this deployment model, allow us to assign values that can be used across multiple packages in a project. We can set these project parameters at runtime, providing greater flexibility and control. Here's an example of how to create a project parameter in T-SQL:
DECLARE @var nvarchar(50) = 'MyProjectParameter';
EXEC [catalog].[create_project_parameter] @var, 'SSISDB', 'MyProject', 'MyDescription', 1, NULL, NULL, 1
Another important feature of the SSIS Project Deployment Model is the project-level connection manager. This allows you to define connections that can be shared by all packages in the project, facilitating consistency and reducing redundancy. This is particularly useful in environments where multiple packages are connecting to the same data source.
The SSIS Project Deployment Model also supports the Integration Services Catalog (SSISDB), a database that stores SSIS projects, packages, parameters, and environments. It enables an easy deployment process where projects are deployed directly to the SSISDB and can be managed in SQL Server Management Studio (SSMS).
The SSIS Project Deployment Model also correlates with Azure SQL and Azure Synapse, enabling cloud-based data integration scenarios. This model supports deploying SSIS projects to Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF), providing a cloud-based ETL and data integration solution.
Despite the fact that MySQL 8.0 and DB2 11.5 are not directly compatible with the SSIS Project Deployment Model as it is a feature of SQL Server, data from these databases can still be manipulated and moved using SSIS packages. This is done by setting up the appropriate connection managers in the SSIS project.
In conclusion, the SSIS Project Deployment Model offers a host of features that streamline and enhance ETL processes. Whether you are working with SQL Server 2019, Azure SQL, Azure Synapse, MySQL 8.0, or DB2 11.5, understanding this model is key to efficient data integration and management.