Post reply

Agent task with PowerShell script: moves to next step before PS script completes?

  • December 6, 2017 at 7:47 am

    #326735

    I have an SQL agent job that has three steps.

    Step 1 is to use PS to get some  data and put it in a file (queries Azure for stuff)

    Step 2 is  to do some ETL tasks on said data

    Step 3 is to do some database stuff with said data

    The issue is, step 1 starts and completes immediately, but the PS tasks seems to run in a background thread.  So that task takes around 90 seconds to complete, but the SQL agent job finishes in 10 seconds and steps 2 and 3 do nothing because the data from step 1 is never done yet.

    I could do some sort of wait 90 seconds command, but there has to be a better way?

    I tried playing with  the Start-Job and script block commands in the PowerShell script then ending it with
    Get-Job | Wait-Job | Out-Null
    Remove-Job -State Completed

    Hopwever, it seems when I call the PS script via the agent task it still neglects to wait and just kicks the script off as its own background process.

    I need a way to call a powershell script task and NOT spawn a async thread.

    Anyone else run into this or have a solution?  I must be doing this the hard way or missing something obvious.

    Thanks!

  • December 6, 2017 at 7:59 am

    #1970934

    Unfortunately its down to SQL believing it has completed what it needs to do and in theory it has but hasn't, as the task says to execute the script which it has done, it doesn't wait.

    My solution would be to  add in a WAIT FOR DELAY step in-between step 1 and 2 to wait for say 2 minutes

    Step 1 - Do your Azure stuff
    Step 2 - Wait (WAITFOR DELAY '00:02:00')
    Step 3 - ETL
    Step 4 - Other stuff

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply