Luis Cazares (10/30/2013)
below86 (10/30/2013)
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.If you just want to know where and why the task failed, you could use SQL Server error handling to get an even better error log. To avoid changes on the SSIS package, you could simply use Stored Procedures instead of the full query.
I'm not sure that your way is the way to go, but if it works for you, then I can't suggest you to stop using it, just wanted to give some ideas.;-)
I know how to tell where my SSIS package failed it's just finding out where in the SQL it failed and what error it gave. And from what I've done, granted not a lot, I don't see that I can get that detail from the execute SQL task. If you have a 1,000 line SQL file you are executing with say 20 individual SQL 'steps' within it. I need to know that SQL 'step' 10 failed for 'duplicate key' or whatever the error is. My log file will give me that and I can also see how many rows were processed by each step. I haven't done much with stored procedure so I am not aware of any 'log' that is created when it is executed. Where/and how would I find that information? Does the stored procedure need to be set up in any special way for this to happen? Or is is a setting on the database or server?
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.