Roadmap › SSIS — SQL Server Integration Services › Module: Deployment, Scheduling and Performance
Lessons in this course (13)
11. Project vs package deployment model and SSISDB
Intermediate★ Must knowLesson 11 of 13
| Package deployment (legacy) | Project deployment (2012+) | |
|---|---|---|
| Unit | Individual .dtsx | Whole project .ispac |
| Stored | File system or msdb | SSISDB catalog |
| Configuration | Package configurations (XML, SQL table, env vars) | Parameters + environments |
| Logging | Log providers you configure | Automatic catalog logging + reports |
| Versioning | Manual | Catalog keeps project versions; rollback |
SSISDB catalog workflow:
- Create the catalog (SSMS → Integration Services Catalogs → Create Catalog; needs CLR enabled and a password for the encryption key).
- Create a folder, deploy the
.ispac(Deployment Wizard, SSMS,ISDeploymentWizard.exe, Azure DevOps task). - Create environments (DEV/QA/PROD) with variables (server name, file paths; sensitive values encrypted).
- Add an environment reference to the project and map parameters to environment variables.
- Execute via SSMS,
catalog.create_execution+catalog.start_executionstored procedures, or a SQL Agent job step (type SQL Server Integration Services Package, select environment).
sql
DECLARE @exec_id BIGINT;
EXEC SSISDB.catalog.create_execution @folder_name = N'ETL', @project_name = N'SalesDW', @package_name = N'LoadFactSales.dtsx',
@reference_id = 1, @execution_id = @exec_id OUTPUT;
EXEC SSISDB.catalog.set_execution_parameter_value @exec_id, @object_type = 50, @parameter_name = N'LOGGING_LEVEL', @parameter_value = 1;
EXEC SSISDB.catalog.start_execution @exec_id;bat
dtexec /ISServer "\SSISDB\ETL\SalesDW\LoadFactSales.dtsx" /Server "PRODSQL01" /Env 1 /Par "$Project::LoadDate";"2026-10-20"