Solved

Activex Job step Constants

Posted on 2004-08-12
7
379 Views
Last Modified: 2008-02-01
I am running a step in a Server agent job that checks for the presence of a file. I want the step to fail if the file does not exist. Easy enough, but I CANNOT find the right constant to pass back to the SQL server agent to give the step a status of failure. Here is my script:
Function Main()
    Dim FileSys
    Dim strFileRoot
    Dim strISAFile
    Dim strToken
    Dim intCount
    Dim FileFound
       
    intCount = 1
    strISAFile = "ISA_IN.TXT"
    strToken = "token.txt"
    Set FileSys = CreateObject("Scripting.FileSystemObject")
    strFileRoot = "C:\isis\dbData\EDI_Admin\"
    FileFound = "False"

    Do While intCount < 500000 and FileFound = "False"
          If Not FileSys.FileExists(strFileRoot & strToken) Then
              intCount = intCount + 1
          Else
              FileFound = "True"            
          End If      
    Loop
                
    If FileFound = "False" Then      
       Main = DTSTaskExecResult_Failure
       Set FileSys = Nothing      
       Exit Function
    End If    

    If Not FileSys.FileExists(strFileRoot & strISAFile) Then
        Main = DTSTaskExecResult_Failure
        Set FileSys = Nothing
        Exit Function
    Else
        Main = DTSTaskExecResult_Success
    End If
    Set FileSys = Nothing
End Function

This was my first try, I know that  DTSTaskExecResult_Success is a datapump constant, but I have tried every job step constant I can find and all still report back success.
0
Comment
Question by:nwfisher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11795316
your task is fine. make sure you set your package to fail on first error.

package properties..logging.. fail package on first error.
this will send the error back to sql agent.
0
 

Author Comment

by:nwfisher
ID: 11795432
I actually figured this out about an hour ago. The tale goes like this. There is no way to send back a code from a server agent job step that will cause the step to be logged as "failed". The only way around this problem is to throw up a runtime error using Err.Raise. This script WAS NOT in a DTS package. It is a step in a SQL server agent job.
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11797347
yep. good catch. ask for a points refund
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

Author Comment

by:nwfisher
ID: 11797357
This is my first question, how do I ask? If it's here, I'm asking.
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11797418
I answered my question myself. What do I do?

 
Post a question in the Community Support topic area asking for a refund, and asking the Moderators to close the question. You'll be required to post your solution in your original question. A Moderator will post a notice of your request which will give the participants 96 hours to object to the refund. Note that if it resembles one of the suggested comments, the likelihood is that your request will not be granted, but rather, the points will be awarded to the Expert who makes the suggestion. In your Community Support request, remember to post a link to the original question.
 
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 12098323
PAQed, with points refunded (125)

GhostMod
Community Support Moderator
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question