[Request] Add AgentJob Output file handling

[rawiriblundell]

Hi team, I'm going through an exercise of exporting SQL agent jobs and converting them to yaml i.e. bringing them under Ansible's heel. In the process, I've noticed that there is an outlier statement/directive that doesn't appear to be covered by lowlydba.sqlserver e.g.

@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DATE))_$(ESCAPE_SQUOTE(TIME)).txt',

or for another example:

@output_file_name=N'D:\AgentJobLogs\_RecycleErrorlog.txt'

sp-add-jobstep includes capability for defining this directly

dbatools handles this separately with the Set-DbaAgentJobOutputFile cmdlet

Please consider building this capability into agent_job_step.ps1

Consider the following examples of (untested) role tasks:

- name: Create SQL Agent job steps
  lowlydba.sqlserver.agent_job_step:
    sql_instance: "{{ ansible.hostname }}"
    database: "{{ step.database_name|default('master') }}"
    job: "{{ job.name }}"
    step_name: "{{ step.step_name }}"
    step_id: "{{ step.step_id }}"
    cmdexec_success_code: "{{ step.cmdexec_success_code|default('0') }}"
    on_success_action: "{{ step.on_success_action|default('QuitWithSuccess') }}"
    on_success_step_id: "{{ step.on_success_step_id|default('0') }}"
    on_fail_action: "{{ step.on_fail_action|default('QuitWithFailure') }}"
    on_fail_step_id: "{{ step.on_fail_step_id|default('0') }}"
    retry_attempts: "{{ step.retry_attempts|default('0') }}"
    retry_interval: "{{ step.retry_interval|default('0') }}"
    subsystem: "{{ step.subsystem|default('TransactSql') }}"
    command: "{{ step.command }}"
    #output_file_name: "{{ step.output_file_name|default(omit) }}"
  loop: "{{ item.value.job_steps }}"
  loop_control:
    loop_var: step
  vars:
    job: "{{ item.value }}"
  with_dict: "{{ sql_agent_jobs }}"

- name: Set output file for job steps
  win_shell: |
    Import-Module dbatools
    Set-DbaAgentJobOutputFile -SqlInstance "{{ ansible.hostname }}" -Job "{{ job.name }}" -Step "{{ step.step_name }}" -OutputFile "{{ step.output_file_name }}"
  loop: "{{ item.value.job_steps }}"
  loop_control:
    loop_var: step
  vars:
    job: "{{ item.value }}"
  with_dict: "{{ sql_agent_jobs }}"
  when: step.output_file_name is defined

vs

- name: Create SQL Agent job steps
  lowlydba.sqlserver.agent_job_step:
    sql_instance: "{{ ansible.hostname }}"
    database: "{{ step.database_name|default('master') }}"
    job: "{{ job.name }}"
    step_name: "{{ step.step_name }}"
    step_id: "{{ step.step_id }}"
    cmdexec_success_code: "{{ step.cmdexec_success_code|default('0') }}"
    on_success_action: "{{ step.on_success_action|default('QuitWithSuccess') }}"
    on_success_step_id: "{{ step.on_success_step_id|default('0') }}"
    on_fail_action: "{{ step.on_fail_action|default('QuitWithFailure') }}"
    on_fail_step_id: "{{ step.on_fail_step_id|default('0') }}"
    retry_attempts: "{{ step.retry_attempts|default('0') }}"
    retry_interval: "{{ step.retry_interval|default('0') }}"
    subsystem: "{{ step.subsystem|default('TransactSql') }}"
    command: "{{ step.command }}"
    output_file_name: "{{ step.output_file_name|default(omit) }}"
  loop: "{{ item.value.job_steps }}"
  loop_control:
    loop_var: step
  vars:
    job: "{{ item.value }}"
  with_dict: "{{ sql_agent_jobs }}"

Thanks in advance