Ansible and SQL Server – the power of format_json

Date:
Author:
Category:
Tags: , ,

Example of Ansible tasks to manipulate MS SQL Server DB – format_json + ConvertTo-Json is used to convert the PowerShell invoke-sqlcmd command line response into JSON.

- hosts: db_servers
  gather_facts: no
  tasks:
    -name: 'get all Address Ids in TW'
     win_shell: "invoke-sqlcmd -username \"DB_USER\" -password \"DB_PASS\" -Query \"SELECT * FROM Addresses WHERE Postcode LIKE 'TW%'\" | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors | ConvertTo-Json"
     register: response

    -set_fact:
       addresses: "{{ response.stdout|from_json}}"

    -name: 'remove Addresses in TW'
     win_shell: "invoke-sqlcmd -username \"DB_USER\" -password \"DB_PASS\" -Query \"DELETE FROM Addresses WHERE AddressId = {{ item.AddressId }}\""
     with_items: "{{ addresses }}"