April 16, 2004 at 11:00 am
I have a DTS Package that was running okay until I attempted to put in code with a parameter in the Select Statement (verses the Where Clause) of the Execute SQL Task. The code was as follows:
INSERT INTO TableB SELECT ?, * FROM TableA
I had to copy the above code into Package Properties by during the following: Go to Package Properties, Disconnected Edit, Select Tasks, Select Specific Task, Click SQL Statement Property Name and click Edit. Paste the code.
The statement would not parse in the Execute SQL Task Dialog but it executes fine. In the mean time, I noticed in the Event Log I was receiving a warning from the SQLSERVERAGENT. The warining had a Category of Job Engine and an Event ID of 208. In the Description it stated "SQL Server Scheduled Job 'Job Name' (long hex value) - Status: Failed - Invoked on: data and time - Message: The job failed."
Again, the job (which executes the dts package) executes fine. I even receive the Job Completed Successfully email message from the Send Mail Task within the DTS Package. But, I do not know why it is given me the Warning about Job Failed in the Event Log. I have about 15 other Jobs (that executes dts packages) set up and they execute fine without this particular warning.
Thanks in advance, Kevin
April 19, 2004 at 8:00 am
This was removed by the editor as SPAM
April 21, 2004 at 12:13 pm
I had a similar problem recently where the DTS pkg execution looks ok, but the execution status returned to the job is failure (returned status 1 instead of 0). MSDB tables sysdtspackagelog and sysdtssteplog show successful execution for everything.
The only way to debug this is to turn on error logging to a file in the DTS pkg. (And once you debug your problem you should turn it off because will append to this log every time and you can't choose to overwrite it.) It will show detailed execution status of every step in the pkg. You are reaching the end of your pkg and sending mail with a DTS sendmail task, but if you enable notification in the job then the job should email you that it is failing. Also the job in the Enterprise Mgr console should show a red X next to it denoting failure of the last run of the job. Every executed step in a DTS pkg needs to have successful status for the pkg execution status to be successful, so you probabably have a step that appears to run/work but the status of it reported back to the job is failure. (In my case I was programmatically skipping a step but I set the execution status of the step to completed rather than inactive to disable the step execution, and execution of that step was reported as failure to the job.)
Hope this helps,
Jeff
April 21, 2004 at 3:52 pm
Kevin;
I know this one!
You made a change to your existing DTS package. That changed the job. Delete your job and reschedule it with the changes already made. Had this same problem a ways back. Modified the DTS package and, suddenly, the scheduled job fails. Deleted the job and made a new JOB and the DTS package works fine now.
Hope this helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply