Collaborating on SSIS Projects

Question

For organizations that use SSIS for enterprise ETL, I'm wondering what are some management strategies being used. The starting assumption is that you have several SSIS projects deployed across the environment, making use of Integration Services Catalogs. The SSIS packages contained inside are executed with SQL Agent jobs. For a single developer it is a fairly simple matter to deploy projects, manage changes, etc. But this becomes cumbersome if there are possibly multiple employees that may need at some point to make changes and deploy a new version of the project. I'm just curious how this is handled in other organizations, how are the projects shared and managed, how are the package/project versions verified, how are deployments standardized, etc.

Thanks,

Bill

Answer 1

Hi @Bill Powell ,

Here is some of 'best practices' as an answer on your questions.

SSIS dev. environment, Visual Studio (VS):

• Design modular processes with reusable SSIS packages. No big monolithic SSIS packages.
• Keep all SSIS projects under source control: TFS, Azure DevOps, and the like.
• Use BIML if applicable.

SSIS run-time environment on a server:

• SSIS Catalog keeps by default 10 deployed versions (configurable) of a SSIS project. It allows to rollback to any of them if any issue comes up.
• Use *.ispac project deployment files for deployments.
• Use SSIS catalog Configurations, Connections Manager, Environments, Reports.
• Periodic maintenance for SSISDB database.
• Backup SSISDB database.

Propagation and comparison across different run-time environments:
Commercial product - ssis-catalog-compare-v4

Answer 2

Hi @Bill Powell ,

1. how are the projects shared and managed
   We should set Protection Level of the ssis projects and packages, please refer to Access Control for Sensitive Data in Packages.
   And then copy folders within Integration Services projects, please refer to Copy project items.
2. how are the package/project versions verified
   Please refer to Checking the Version and Build Number of an SSIS Package.
3. how are deployments standardized
   We can refer to Deploy Integration Services (SSIS) Projects and Packages.

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.