Friday, April 21, 2017

[Powershell] Run powershell code 30 seconds after the job it's in finishes(!)

We have a process that, when it fails, has a tracking table, and as each step is run it updates that row in the tracking table.  If the job fails, we know in which step it failed.

I also have a process that looks at failed jobs every minute, and sends out the tracking table info when a SSISDB job fails, so we get a useful message.  (See SSISDB in this blog).

However, the tracking table means that a job will wait to be fixed.  I don't want to update it as part of the job, since then I can't get the state of the tracking table. So what I need is either to kick off another job that waits 2 minutes... or use powershell.  :)

Next up is feeding it a parameter, and setting the background script to use an update statement

Note: works in powershell 2 or greater.  This seems much easier to do in newer versions.

First script "runinforeground.ps1":
start-process -filepath "powershell" -argumentlist "c:\temp\runinbackground.ps1"

Second Script "RunInBackground.ps1":
$now = get-date
start-sleep -s 10
$now | out-file c:\temp\whatisnow.txt #here is where I'd do the invoke-sqlcmd2 and the update

SQL Server Agent Job Step (CmdExec):
powershell.exe "c:\temp\runinforeground.ps1"