This is one of two reasons I don't use the execute SQL task. If I get called at 1 AM because the SQL failed I want to know where it failed and what error I got. I don't want to have to rerun the SQL in SSMS to find the error. And I may have to determine if I can rerun the earlier steps in the SQL, may need to restore or recreate certain files. Not things you want to have to think about at 1 AM.

The second reason is I have to update the SSIS package anytime I make a SQL change. I know this can be a good thing if you keep versions of each update. But luckily we haven't ran into any issues, yet.

We use an 'Execute process task' to call a 'bat' file that executes the SQL. I can set this to put the log from the SQL out to a file that I can then review.

So until someone can tell me how I can see this same type of 'log' information for an execution of a SQL job, I will not be using the execute SQL task.