A YAML Azure DevOps SSIS Pipeline

# Build the SSIS package and create artifacts that can be deployed
# Create a ispac for the 'Development', 'Test' and 'Production' which are the same as the Visual Studio Build Configuration drop down options
# https://learn.microsoft.com/en-us/sql/integration-services/devops/ssis-devops-overview

## Usage


parameters:
  - name: dtproj
    type: string
  - name: artifactName
    type: string
  - name: sourceFolder
    type: string
  - name: isMain
    type: string
    values:
     - 'true'
     - 'false'

jobs:
  - job: BuildAndCreateArtifacts
    displayName: "Build and Create .ispac Artifact"
    steps:
      - task: SSISBuild@1
        displayName: "Build SSIS Package - Development"
        inputs:
          projectPath: ${{ parameters.dtproj }}
          outputPath: '$(Build.ArtifactStagingDirectory)/Development'
          configuration: 'Development'
          stripSensitive: true
      - task: SSISBuild@1
        displayName: "Build SSIS Package - Test"
        inputs:
          projectPath: ${{ parameters.dtproj }}
          outputPath: '$(Build.ArtifactStagingDirectory)/Test'
          configuration: 'Test'
          stripSensitive: true
          # if Test isn't defined, continue on
          continueOnError: true
      - task: SSISBuild@1
        displayName: "Build SSIS Package - Production"
        inputs:
          projectPath: ${{ parameters.dtproj }}
          outputPath: '$(Build.ArtifactStagingDirectory)/Production'
          configuration: 'Production'
          stripSensitive: true

      - task: PublishBuildArtifacts@1
        displayName: "Publish The .ispac Build Artifacts"
        inputs:
          PathtoPublish: $(Build.ArtifactStagingDirectory)
          ArtifactName: ${{ parameters.artifactName }}

      # properties will need to be set manually in SSMS > package > configure
      # - task: PublishBuildArtifacts@1
      #   displayName: "Publish SSISSSISCatalog configuraiton Build Artifacts"
      #   inputs:
      #     PathtoPublish: '${{ parameters.sourceFolder }}/ssisConfiguration.json'
      #     ArtifactName: '${{ parameters.artifactName }}_config'

# Deploy/Publish the SSIS Package to the Database Server 
# Dev and Production Environments that are setup in the ADO UI under Pipelines > Environments
# https://learn.microsoft.com/en-us/sql/integration-services/devops/ssis-devops-overview

## Usage
## see https://dev.azure.com/AveraHealthPlans/Software/_git/Examples/Pipelines/SSIS
## Build an .ispac for each environment, then use those artifacts here

parameters:
  # should match the <name>.ispac
  - name: artifactName
    type: string
  # 'Development', 'Test', 'Production'
  - name: environmentName
    type: string
  # since we can't pull these directly from the variable group and use $(DatabaseAdoEnvironmentName) (it's not evaluated)
  # this should be from the variable Group
  - name: databaseAdoEnvironmentName
    type: string
  # the name of the agent under the ADO environment
  - name: databaseAdoResourceName
    type: string
  - name: databaseServer
    type: string
  # Example: '/SSISDB/MyFolder/MyProject'
  - name: destinationPath
    type: string


jobs:
  - deployment: 'Deploy_${{ parameters.environmentName }}'
    displayName: 'Deploy ${{ parameters.environmentName }}'
    # dependsOn: ${{ parameters.stageDependsOn }} I kept getting Stage PublishProduction job Deploy_Production depends on unknown job Deploy_Development. so I removed this
    environment:
      name: ${{ parameters.databaseAdoEnvironmentName }}
      resourceName: ${{ parameters.databaseAdoResourceName }}
      resourceType: VirtualMachine
    strategy:
      runOnce:
        deploy:
          steps:
            - checkout: none
            # TODO: create json configuration files, publish them from the ssisBuild and use them here
            # for now: properties will need to be set manually in SSMS > package > configure on creation and if they change
            # https://learn.microsoft.com/en-us/sql/integration-services/devops/ssis-devops-overview?view=sql-server-ver16#ssis-catalog-configuration-task
            # - task: SSISCatalog@0
            #   inputs:
            #     # 'filePath', 'inline', 'none'
            #     configSource: 'filePath'
            #     # note: I'm not certain about using this, look at the documentation
            #     # configPath: '${{ parameters.artifactName }}/${{ parameters.artifactName }}_config/Project.params'
            #     # targetServer is where the SSISDB catalog is hosted and usually the same as destinationServer
            #     targetServer: ${{ parameters.databaseServer }}
            #     connectionStringSuffix: ''
            #     # 'win' or 'sql'
            #     authType: 'win'
            #     rollBackOnError: true

            # This will not create a new Package on the database in SSISDB
            # It will only update the existing ones
            # - task: SSISDeploy@1
            #   displayName: "Deploy SSIS Package"
            #   inputs:
            #     # Example: your build stage artifact from ssisBuild.yml
            #     # The auto download artifacts puts it in
            #     sourcePath: '$(Pipeline.Workspace)/${{ parameters.artifactName }}/${{ parameters.environmentName }}/${{ parameters.artifactName }}.ispac'
            #     # Example: 'SSISDB' or 'File System'
            #     destinationType: 'SSISDB'
            #     destinationServer: ${{ parameters.databaseServer }}
            #     destinationPath: ${{ parameters.destinationPath }}
            #     authType: 'win'
            #     whetherOverwrite: true
            #     whetherContinue: false

            # This will create or update the package.
            # If creating you still need to add the parameter overrides in SSMS > Project > Configure             
            - task: PowerShell@2
              displayName: "Deploy SSIS Package"
              inputs:
                targetType: 'inline'
                script: |
                  # SSISDeploy.exe needs to be on the VM with SSISDeploy.exe in the environment variable path
                  # https://learn.microsoft.com/en-us/sql/integration-services/devops/ssis-devops-standalone?view=sql-server-ver16
                  # Path to the SSISDeploy.exe
                  $ssisDeployPath = SSISDeploy.exe
                  
                  # Source path to the ISPAC file
                  $sourcePath = "$(Pipeline.Workspace)/${{ parameters.artifactName }}/${{ parameters.environmentName }}/${{ parameters.artifactName }}.ispac"
                  
                  # Destination type: CATALOG or FILE
                  $destinationType = "catalog"
                  
                  # Destination server (for CATALOG)
                  $destinationServer = "${{ parameters.databaseServer }}"
                  
                  # Destination path in SSISDB (for CATALOG)
                  $destinationPath = "${{ parameters.destinationPath }}"
                  
                  # Authentication type (WIN, SQL, ADPWD, ADINT)
                  $authType = "win"
                  
                  # Project password (if required)
                  $projectPassword = ""
                  
                  # Execute SSISDeploy.exe
                  ## SSISDeploy.exe -s:Example.ispac -d:"catalog;/SSISDB/ExampleA;AHDC790DBd01" -at:win
                  & SSISDeploy.exe -s:$sourcePath -d:"$destinationType;$destinationPath;$destinationServer;" -at:$authType
            # requires sysadmin server role 
            - template: ssisCreateAgentJob.yml
              parameters:
                artifactName: ${{ parameters.artifactName }}
                environmentName: ${{ parameters.environmentName }}
                databaseServer: ${{ parameters.databaseServer }}
                databaseName: ${{ parameters.databaseName }}
                ssisPackagePath: ${{ parameters.destinationPath }}