How to pass a DataTable between 2 runbooks RRS feed

  • Question

  • I have been pulling my hair out with this issue over the last couple of days and thought somebody may be able to save me from going insane!!

    Basically I am trying to setup an automated process that extracts some data from a SQL Server, exports it to CSV and then emails that CSV file to a specified email address, simples one would think!

    I am using Powershell Runbooks as I was told on a previous thread that Workflows are dead in the water.

    In an attempt to break the script up into manageable chunks are created 3 separate scripts: -

    1. Start Script
    2. Main Script
    3. Data Retrieval Script

    The Start Script basically collects the variables from the Automation Account and passes them to the Main Script which in turn calls the Data Retrieval Script using Start-AzAutomationRunbook which returns a DataTable generate via Invoke-Sqlcmd.

    Initially I tried to save the data to a CSV file directly within script 3 but I cannot then access that back in Script 2 I assume because it is not persisted.  So I am attempting to Write-Output my DataTable from Script 3 and then retrieve that output stream from within Script 2 using Get-AzAutomationJobOutput.

    Now Script 3 seems to be logging each individual record from the SQL DataTable to the Output window when I run it, I haven't yet seen this complete it just takes forever which leads me to believe that I'm doing something very wrong!

    Can anybody recommend a strategy for achieving the above without creating a single unmanageable script file, is the way I'm doing things sensible and I'm being impatient or am I just asking too much at this stage of Azure Automations development?

    To add, I have left the scripts running and I end up with multiple jobs running for my Data Retrieval Script and then it errors with: -

    The runbook job was attempted 3 times, but it failed each time.

    Why would this fail after such a short period of time as the documentation states for the -MaxWaitSeconds parameter: -

    Specifies the number of seconds this cmdlet waits for a job to finish before it abandons the job. The default value is 10800, or three hours.

    Thursday, October 17, 2019 3:15 PM


  • Ok, gave up on trying to pass stuff between Powershell Runbooks and simply saved the csv file to a local storage account, I called the script using Start-AzAutomationRunbook using the -Wait flag and then simply get the CSV file from local storage in my calling script.

    This all works fine and feels like a more scalable approach anyhow.

    • Marked as answer by Andy Breward Friday, October 18, 2019 9:50 AM
    Friday, October 18, 2019 9:49 AM