May 11, 2009 at 2:41 pm
The job step records this message:
Message
Executed as user: FREDSNET\sqlservice. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:05:00 AM Error: 2009-05-04 04:05:02.06 Code: 0xC0048006 Source: ActiveX Script Task ActiveX Script Task Description: Retrieving the file name for a component failed with error code 0x03EFBD9C. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 4:05:00 AM Finished: 4:05:02 AM Elapsed: 1.203 seconds. The package executed successfully. The step succeeded.
NOTE: the ActiveX Script task fails. Then the package returns a success status. The job has an email task if the job fails - no email. How can one depend on this method of identifying failed jobs when we run 50 or so jobs every night? What is wrong with my job?
Any ideas?
May 11, 2009 at 6:06 pm
The last part of the error message actually states that the step succeeded too - it's just that there was an error in it.
Can you post the code for the ActiveX script? Maybe task 'success' is being set in there, regardless of the execution outcome.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 12, 2009 at 6:23 am
The sDestination file does not exist. The sSourceFile does not exist.
Function Main()
Dim oFSO
Dim sSourceFile
Dim sDestinationFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile = "\\memFS01\ftpdata\datawhse\DWF145.csv"
sDestinationFile = "G:\DataWarehouse\archive\DWF145_import.csv"
oFSO.MoveFile sSourceFile, sDestinationFile
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
May 12, 2009 at 6:45 am
jnichols (5/12/2009)
Main = DTSTaskExecResult_Success
I think that might be your problem 😎
May 12, 2009 at 6:56 am
When a line of code fails, the remainder of the code is not executed. That line is the same in all ActiveX scripts. If I tested for the presence of the sSourceFile and did not find it I could code:
If not FSO.FileExists(sSourceFile) then
Main - DTSTaskExecResult_Failure
end if
also
If FSO.FileExists(sDestinationFile) then
Main = DTSTaskExecResult_Failure
end if
These two IF statements are not supposed to be necessary because the FSo.CopyFile statement should and does fail if either of the conditions coded above are true. The Precedence Constraint prevents the next step from executing and should cause the package to fail if the ActiveX script does not return the DTSTaskExecResult_Success, which it should not be doing if the copy statement fails. And the message clearly states the only executable statement other than setting the FSO object to nothing in that script failed. ???????
May 12, 2009 at 5:58 pm
Please check the following task properties for your ActiveX script:
Fail Package on Failure
Force Execution Value - if true, what is Forced Execution Value
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 13, 2009 at 7:25 am
Ah ha!
Under forced execution value:
ForcedExecutionValue = 0
ForcedExecutionValueType = Int32
ForceExecutionValue = False
Under Execution:
FailPackageOnFailure = False
FailParentOnFailure = False
MaximumErrorCount = 1
I think you have struck on something here. Shouldn't the FailPackageOnFailure be set to True and FailParentOnFailure set to True?
I'll do some research, but you seem to have knowledge that could short circuit my research efforts. Busy guy as I am sure you are as well. Thanks for the help.
May 13, 2009 at 11:01 pm
You are right - set Fail package on Failure to True and you should get the result you desire.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply