I have followed the examples from tSQLt and have successfully created unit and integration tests for my SQL Server database. I know that Redgate sells DevOps tools for automating tSQLt unit and integration tests, but I don't have that kind of money.
My no-cost idea was to run a SQL Agent task on a schedule. It's easy enough to create a T-SQL Job Step with the command EXEC tSQLt.RunAll;. While I have SQL Agent configured to send me emails if a job step fails, I don't know how Agent would know whether I had any failing tests when running the EXEC tSQLt.RunAll; step and alert me.
There's nothing to stop me from manually running these tests, but I really would love to automate them so I can focus on other things.
Has anyone been able to do this or achieve a similar result using SQL Agent?
As Martin Smith points out, tSQLt raises an error at the end of the execution if any of the tests failed or ran into an exception. Any errors throughout are caught, so you will always get a complete run.
The Agent allows you to capture the output, so you will be able to see what failed.