We use Sql Server Integration Services (SSIS) to import text files into our database. The dtsx files are deployed into the MSDB on the Sql Server. Everything works well with deployment with a powershell DSC script. But times change and we must deploy the packages to the catalog. Here is what we did.
Convert
The first step is to convert the project. We used the “Convert to Project Deployment Model” wizard that is started from the Project menu or the context menu in the Solution Explorer. Because this is a lift-and-shift we don’t replace the configuration bij parameters, so in the step “create parameters” we unselect everything. After the conversion we can still open the “Package Configuration” and see the properties are set from there.
Build
The reason we converted the project is because we need an .ispac file for the release to the catalog. During the build the ispac file is created. We gather alle dtsx and ispac files in an atrifact.
Deploy
Deployment will take the new ispac files in the artifact. With the code sample from microsoft (see references) we install the ispac into the catalog with DeployProject. This change is in the powershell DSC.
Conslusion
Our SqlJobs had to load the package from the catalog now. This was a minor change:
SET @Command = N'/SQL "\"\FOLDER\PACKAGENAME\"" /SERVER "\"' + @Server + '\"" /CHECKPOINTING OFF /REPORTING E’
SET @command=N'/ISSERVER "\"\SSISDB\FOLDER\PROJECTNAME\PACKAGENAME\"" /SERVER "\"' + @Server + '\"" /CHECKPOINTING OFF /REPORTING E'
After these steps the SSIS packages are in the catalog. The impact on the system is minimal because the sqljob remains on the same location and handles the location change. Happy dev, happy dba.
References
- Convert the project to the Project Deployment Model: https://docs.microsoft.com/en-us/sql/integration-services/lesson-6-2-converting-the-project-to-the-project-deployment-model?view=sql-server-ver15
- Code sample for deployment: https://docs.microsoft.com/en-us/sql/integration-services/ssis-quickstart-deploy-powershell?view=sql-server-ver15
