If you need to make a job fail via t-sql, simply use the throw statement:
THROW 51000, 'Failure', 1;
In fact, throwing ‘Failure’ from any program (powershell included) will cause the job to fail:
if($ret -eq 1)
{
throw "Failure"
}
How to cause a Job to fail via t-sql
Published
Sql Server Powershell 