June 13, 2006 at 7:05 am
Howdy
I would like to know if there is a way for me to configure the error of a DTSX to show as the error/message in the Job it is called from.
To elaborate:
I have several DTSX packages.
I am calling them from a single job.
The DTSX, when run in the SSIS designer errors with a FK error.
The DTSX when called from the job errors.
The Log File Viewer message says “…The package execution failed. The step failed.”
I would like the Log File Viewer message to say “…violation of foreign key constraint…” which is the actual error.
Does anyone know how to do this?
June 14, 2006 at 2:36 am
Yep. Don't use the "Execute SSIS Package" job step. Use the command-line job step and use dtexec.exe to fire the package. You get much more information in your job output that way.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
June 14, 2006 at 6:05 am
@jamie: That's an interesting suggestion. How do you get more infomration - is there more put into the default logging, or is that information available to store in variables, tc. ?
I'm looking at designing (my 1st!) SSIS package, and currently it looks like I will have 4 SSIS packages - the idea being that these packages could, in a fallover system, be called as individual modules after resolving the underlying problems. It seems more manageable that way than trying to use checkpoints, and the clients wants the ability to manage the componenets at a modular level.
What additional information is available by using the command-line interface, as opposed to the Execute SSIS Package?
June 14, 2006 at 6:11 am
Wanderer,
The reason you get more info with the command-line is that SSIS job step is just...well...crap! Under the covers it uses dtexec.exe anyway in exactly the same way - it just doesn't expose the same debuggig informaiton.
There's a really good article about it here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html
on the SQLIS Wiki.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
June 14, 2006 at 6:15 am
Lovely. Thanks.
I can't seem to get the syntax correct and there are a ton of params.
What do I absolutely need to put into the string?
I am reading this article now...http://www.databasejournal.com/features/mssql/article.php/3509601
I think I got it, I used the dtexecui.exe to build the string.
Thanks again!
June 14, 2006 at 7:33 am
Thanks Jamie,
Off I go to play then. I'll check that link as well. Cheers
August 3, 2006 at 7:27 am
Hi,
I happened to be reading this, and it helps my case too.
For e.g. my target tables have some business check constraints built into them e.g. Maturity value > 100 etc. When some inputs rows fail this condition, the error redirection provides me the error code and description of "....constraint violation"
It does not provide me with details of which business or check constraint failed, or whether it was a PK or FK failure.
Can i log more detailed messages rather than just the above. Can i also locate the exaxt column which failed the rows....
looking forward to your help...
Regards
Amit
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply