0

I have inherited a production 2016 SQL Server with SSIS installed, which has many SSIS packages deployed to msdb (mostly warehouse ETL workloads that run nightly).

I would like create a new Integration Services Catalog and deploy future SSIS projects to SSISDB. Can these two deployment methods and package locations coexist? In other words, if I create the new catalog and start using the project deployment model for new SSIS projects, will that have any effect on the dtsx packages already in msdb?

1

Yes, you can use both the Package Deployment Model (msdb) and the Project Deployment Model (SSISDB) in tandem with no ill-effects.

As a consultant, if I walked into a place that had both, it'd indicate that they've been using SSIS before the 2012 release and there's been no reason to upgrade existing work to a new model. In heavily regulated industries, going through the bureaucracy just to make your life easier often isn't a consideration. That or someone had a very good reason for using the legacy/msdb.

A lot of "design sins" (lack of configuration, lack of logging) in the Package Deployment Model aren't a concern with the Project Deployment Mode (logging is automatic and connection managers are easily externally configurable). The Project Deployment Model also makes parent/child design patterns more easily implemented.

From a management perspective, that's the real beauty of the SSISDB. If you want to run a package, you call the native procedures and it spins up dtexec. For packages in msdb, you are looking at either an xp_cmdshell call or a SQL Agent job as there's no native way to run them.

Backup and recovery of an SSISDB is a touch more complex due to key management but it's documented.

Deployment of packages uses two different mechanisms (dtutil vs isdeploymentwizard/managed object model/bulk insert)

Execution uses the same executable: dtexec

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.