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

• Maxer

  SSCrazy Eights

  Points: 8964

  More actions

  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!

• Ant-Green

  SSC Guru

  Points: 113544

  More actions

  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