Using Yaml templates with SSIS in a Azure DevOps Pipeline

I needed to create an easy way to for my team to add a YAML pipeline to many projects. There is a variety of SSIS, .Net and Framework for IIS and .Net and Framework for exe execution. Thankfully, I had plenty of time to work through issues and experimentation. I thought of naming this “I struggled so you don’t have to (as much, hopefully)”.

My challenge was to create templates for the 10 to 100 of repos and small apps that exist in this environment that uses OnPremise hosting. They are SSIS with SQL, Framework/.Net IIS applications and some Framework/.Net console apps that are scheduled/ran with SQL Server Agents.

Since we have so many projects that need pipelines, I decided to put in the extra effort of making it more DRY with templates to share. The alternative is to make it “wet” where we copy and paste for each pipeline. This could be a better approach if you I found How to share Azure Dev Ops Build Pipelines a great starting point and then modified and added to it as many of our needs were different.

These are for OnPremise environments, the Azure pipeline YAML can be found in Microsoft documentation (maybe I will add those someday).

I also have an SDK Style SQL Project YAML example on my other post as well as in the Github repository.

The Code

I’ve shared the yaml code in my Github Repository .

Benefits and Good Parts

• Reuse templates, less copy/paste
• it’s now quick to create a new pipeline
• The built in tasks are very helpful
• It works with OnPremise using the Environments
• I could install the ADO deploy agent without needing firewall changes
• The conditionals in yaml, especially the isMain variable

Challenges

• I could not get Variable Group values to evaluate in the deployment task and had to hard code some of those values.
• A lot of the MS Learn documentation is Azure focused
• Thinking about security and how to talk to IT about installing the ADO deploy agent
• Getting all the parameters and variables correct. That took a lot of trial and error.

One .Net IIS Example

See my Github Repository example .

ADO OnPremise Agent Security

Read through Prepare Permissions .

For OnPremise hosted agents, it’d be best to have a local user created for the service to run under for each environment (even split up for database, IIS, other server) to give the least priveledge. Then create the agent with a PAT specific to that user after it is in ADO.

I didn’t find a lot of resources on how to do this properly. It seems like Microsoft didn’t consider security when making the NT AUTHORITY/SYSTEM the default for the agent. Maybe they consider the OnPremise Agent to be secure?

I found an article from Michael Boeynams . We didn’t go this far, but his approach is the most secure that I’ve found. His GitHub link with PowerShell scripts could be modified to set the required permissions for IIS deployment. Using a script would be much better than clicking around until you get the correct working permissions (you document it right?).

We had to put our local account into the Administrators group. We were unable to set the permissions needed in C:\Windows\System32\inetsrv\Config and were hesitant to do so. One of our team members worried that changing the permission inheritance could somehow hurt IIS.

Variables

A quick note about variables and expressions after I had a mix up and had to dig deeper. They are evaluated at runtime or compile time depending on the syntax used.

• ${{ variables.isMain }} is evaluated at compile time and will be “$[eq(variables[‘Build.SourceBranch’], ‘refs/heads/main’)]”
• $[ variables.isMain ] is evaluated at runtime and will be “true”
• $(isMain) is evaluated at runtime and will be “true”

usage: condition: eq($(isMain), 'True')

variables[‘isMain’] is the same as variables.isMain

  - job: Test
      displayName: 'Echo Build.SourceBranch'
      steps:
      - script: echo "Build.SourceBranch is $(Build.SourceBranch), isMain is $- $(isMain) is runtime- $[variables.isMain], vs compiletime ${{ variables.isMain }}"

the result is "Build.SourceBranch is refs/heads/isMainForSSIS, isMain is $- True is runtime- $[variables.isMain], vs compiletime $[eq(variables['Build.SourceBranch'], 'refs/heads/isMainForSSIS')]"

Runtime parameters are related .

Look at the security page and improve your pipeline security. “Use parameters instead of variables: Unlike variables, a running pipeline can’t modify pipeline parameters” caught my attention

A more in depth test of variables

variables:
  isMainBranch: $[eq(variables['Build.SourceBranch'], 'refs/heads/testBranch')]
  ${{ if eq(variables['Build.SourceBranch'], 'refs/heads/testBranch') }}:
    isMain: 'true'
  ${{ else }}:
    isMain: 'false'
stages:
  - stage:
    jobs:
      - job: 'isMain'
        steps:
        - ${{ if eq(variables.isMain, true) }}:
          - script: echo " isMain - Build.SourceBranch is $(Build.SourceBranch), isMain is $- $(isMain) is runtime- $[variables.isMain], vs compiletime ${{ variables.isMain }}"
        - ${{ else }}:
          - script: echo "not main -Build.SourceBranch is $(Build.SourceBranch), isMain is $- $(isMain) is runtime- $[variables.isMain], vs compiletime ${{ variables.isMain }}"
      - job: 'isMainBranch'
        steps:
        - ${{ if eq(variables.isMainBranch, true) }}:
          - script: echo " isMainBranch"
        - ${{ else }}:
          - script: echo "not isMainBranch"

Retaining Builds for Auditors

Using the pipelines/onProdDeploySuccess.yml template , the build will be retained indefinitely and the git commit will be tagged.

“I think we’d need to go at least 2 years since an auditor could come in the current year asking about last year. Can we go indefinite for starters and set a threshold later?” ~ A knowledgable business leader

If you are using multiple publishes to prod, it could be tagged multiple times.

Conclusion

Hopefully, these will save you time and help you create more DRY YAML templates