Convert Project model back to package model

  • What started out as an attempt to use the SSIS Project-model combined with deployment using the SSISDB catalog has been superseded by the requirement to use devops+pipeline to deploy packages from dev to production.

    This means using direct deployment at file level and converting everything build from project to package.

    Are there any best practices in such a scenario? Can i simply convert the project connection (managers) back to package connections (where the connection itself is stored in the package xml) ?

     

  • Which part of an SSIS project deployment do you think cannot be achieved through DevOps?


  • Good question. I do not have much experience with Devops this way. I assume that depoying the packages/project from visual studio directly to the SSISDB repository is a 'native' process. Really no clue how one could incorporate Devops into that process.

    When using package model, then the package xml would store everything within the xml, so it is a matter of transfering files.

  • Assuming you have your SSIS solution checked in to Git, the DevOps deployment is a two stage process.

    1. A build pipeline builds the solution and publishes the build artifacts (ie, ispacs in this case) to a nominated folder

      1. This pipeline is written in YAML

    2. A release pipeline, picks up the published artifacts and deploys them to SSISDB on a nominated server, using a standard Deploy SSIS task

    There's a bit of a learning curve, but it's certainly do-able.

    What is potentially more tricky is the deployment of SSISDB environments, variables and configurations.


  • it really shows a lack of understanding of how SSIS Catalog deployment works, even within VS (which does invoke a command line tool to deploy).

    everything can be deployed from CI/CD pipeline, even if more complicated than just a file deployment.

    have your people look at https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-deploy-cmdline?view=sql-server-ver16 - in this you will have several links to deploy instructions/scripts, including Powershell and C# methods.

    also have a look at https://marketplace.visualstudio.com/items?itemName=SSIS.ssis-devops-tools and https://learn.microsoft.com/en-us/sql/integration-services/devops/ssis-devops-standalone?view=sql-server-ver16

  • Yes, it is due to a lack of understanding how this part of SSIS works, mainly due to working with customers that picked up SSIS in the period when only package model was offered. And that is why I am asking these question on sqlservercentrals in the first place.

    Let me check on the links provided and thanks for your input.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply