I was wondering if anyone had good solutions for managing changes to SSIS packages over time and through a change control process. I'm trying to navigate the team towards SSISDB catalog and the problem I am seeing is how easy it is to deploy projects/packages.
I'm currently thinking of doing something like:
Use Local SQL Server to test/deploy new ssis packages
Create new Jobs in local
Script Jobs out for DBAs
Once a week do a weekly deploy of changes to testing/production
Package A is new so it requires a new job script
Package B has already existed but is being tweaked so the weekly push will just update and the job stays as is.
Package C needs to move from 4am to 3:30am so a new script is created with a job change to DBA
Does this make sense? Packages deployed to the catalog will sit in the catalog until scheduled. New jobs require DBA input on the script generated for SQL Server Agent Jobs.
My goal is to reduce time and effort involved to move from development to release.