Handling a DTS packages errors
-
I read a couple of posts and I'm not too sure this is possible, but I will try asking.
We have several cases where a DTS package is used to check for the existence of a file on an FTP server. This is being accomplished by an VBScript task that uses the FSO. If the file is found, the VBScript task returns DTSTaskExecResult_Success. If the file is not found, the VBScript task returns DTSTaskExecResult_Failure. If the file is not found, the DTS package basically ends. No reason to do anything if there is no file.
The problem is, when this package is scheduled, the job always shows that it failed when there is no file. This is not true, but I understand why. There was a task in the package that did have a failure and therefore the package failed
Is there a way to tell DTS within the package that the package indeed succeeded. This way, the job won't show as failed. Right now, we have DBA's looking at jobs saying the jobs failed, but they behaved the way they should.
Thanks
-
I would look into building an error trap within your VB Script routine. This way it will handle the success or failure of finding the file but not report the failure at the package step level.
-
Thanks Osoba for replying. I thought of this, but most times there are several tasks on the "failed" side of the VBScript task. In order to have the package execute a different set of tasks if the file is not found, the only way I see is to report a failure in the VBScript task for DTS continues down the "file not found path". I could take all the tasks that on the fail side and implement them into the VBScript in the task that checks for the file, but then my question is how to hault exectuion of the package because I don't want the VBScript task to succesfully complete and continue down the path of success, which to to process a file that is not there.
-
This may not work for you but..
How about putting in a line "On error" that will step to another successful piece of script.
Very simple example:
Function Main()
'Below is all your code
On Error Resume Next
Fail_on_Design
msgbox "hello"
Main = DTSTaskExecResult_Success
End Function
...just another idea. Good luck.
-
I guess I'm not illustrating my problem very well. Consider the following:
The second task is to check for a file. To the right of it is the execution path I want to take if the file is there (import data into DB, process it, etc). If the file is not found, then I want to bail out of the DTS package (execution path going up). When I schedule this package, the package runs as it is suppose to, however the SQL Job recognizes the package had an error. Even though each Task that did execute was successful.
Here is the code in the second task.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
if DTSGlobalVariables("gvPackageFail").Value = "True" then
Main = DTSTaskExecResult_Failure 'causes Job to see package as failed
else
Main = DTSTaskExecResult_Success
End If
End Function
Thanks!
-
If I remember correctly it is only because that is what happens at the end of the process that it get's thrown to the Job status. Try having an ON FAIL piece occurr maybe have it write a notice to a log table that the file wasn't found that run. Which should return a success and the final point of the package will return success to the Job.
-
Well, that is what I thought too. That is why i have the second task (Do Successful Task) on the failure side of my sample. It still comes out as a failed package in the job.
-
EMail me as I know we have a package on one of our servers that doesn't this and does return a failure when file not found. I just didn't have time to find last night.
-
Oops I remember now, this is how we do it.
1) Create an Integer variable named varFlCtrl with an initial value of 0.
2) In you VBScript change the If statement that outputs Success/Failure to Set the value of the varFlCtrl variable to 1 for Failure and 0 for Success then have Main return Success.
3) Remove the OnSuccess workflow item from between your ActiveScript object and your next step, then click and highlight the VBScript item first and while holding CTRL key click and highlite that next step item. Now right click and choose Workflow->Workflow Properties, then select the Options Tab on the dialog. There you will find an item at the bottom labeled "ActiveX Script", check Use and press properties button. There do this:
'************************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
If DTSGlobalVariables("varFlCtrl").Value = 0 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End Function
Press ok out to design window and save package.
4) Remove any on error paths you have from the VBScript code and if you are want you can do the same thing to those if you keep and use the same code but change .value = 1 to process that path.
Now when it runs it should get a success even and just doesn't run the success path if not found or the fail path if is found.
Hope that helps.
-
Excellent!!! Thank you so much. I will give this a shot.
-
Unfortunately I have a similar case that seems more mysterious.
I am a local non-admin user of a WIN XP PC on a novell network and I am also a lone adminstrator of a local SQL Server 2000 DE SP3. My DTS package uses a FSO object within ActiveX script to look for a network file's existence. While the package runs fine manually, it fails miserably as a scheduled job.
A msgbox check reveals the scheduled job simply does not find the file regardless of whether I use UNC path or not. However, running this locally with both the file and its file check on my hard drive instead of the network works.
And to top it off - using xp_cmdshell in query analyzer fails to show any file on the network either by 'dir' or 'dir <fullfile>'. The same commands at the DOS prompt works however.
I appreciate any help here.
-
That is because the system account nor any installed account can see those mapped drives in any situation I could come up with when it comes to Novell. I finally ended up creating a VB app to log me into the Novell server, run the package and disconnect when complete. Unfortunately I cannot find a copy of the code and it was removed from SourceSafe about a year ago since we no longer used it. I do have a store of code at the house and it may be in that but I will be leaving as son as I get home for a week. PM me and when I get back I will check. Might could convert to ActiveScript code and get it to work but I never tried that, of course I have to find it.
-
I may have a workaround for now. It's not great but it seems to work. Since DOS commands work at the prompt I decided to create a batch file containing the file existence checks and copy, rename commands. It's responsible for checking and if it finds it - it then copies the network file onto the SQL Server's hard drive and then it makes archived files. The batch file has to be scheduled outside of SQL Server - so using something like Windows Scheduled task can work.
Then the scheduled SQL Server job which has its own finer schedule only checks the local drive for the file. It goes about its merry way once it finds the file. Yet, I am still curious how you coded your fix.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply