Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: Help with DTS Package

    Hey guys,
    I have a wierd problem.
    I have a DTS Package that is scheduled to run every hour. My second step in the package is to check if a table has records in it. If it doesn't find records I made the task fail. When task is done I have 2 procedences coming out, on fail and on success. So if success it goes one way and does the rest of the tasks, if fail it goes the other way and does the rest of the tasks on that side.
    PROBLEM: When it is scheduled to run, It fails all the time, but when I execute it manualy with the same data in the table it is succesfull. Why is this happening?
    Here is the code for the check task that decides which way to go:

    Const adOpenKeyset = 1
    Const adLockOptimistic = 3

    Function FindRec()

    Dim objConn
    Dim sql
    Dim rs

    Dim Success

    Success = 0

    sqlconn = "Provider=SQLOLEDB; Data Source=mysource; Initial Catalog=SA; User ID=user; password=userpass;"

    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open sqlconn

    sql = "SELECT NAM FROM tblUnassignedNAM "

    Set rs = CreateObject("ADODB.RecordSet")
    rs.Open sql, objConn, adOpenKeyset, adLockOptimistic

    If rs.EOF = False Then

    Success = 1
    rs.Close
    Set rs = Nothing

    Else
    'rs.MoveFirst
    Success = 0
    Set rs = Nothing
    End If

    objConn.Close
    Set objConn = Nothing

    If Success = 1 Then
    FindRec = DTSStepExecResult_Success
    ElseIf Success = 0 Then
    FindRec = DTSStepExecResult_Failure
    End If


    End Function

  2. #2
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Does no one know what could be wrong here?

  3. #3
    Join Date
    Jan 2004
    Location
    Bermuda
    Posts
    40
    Hey bpolunin:

    FIRST - I think the Conditional should be something like this...

    If rs.EOF = False Then
    Success = 1
    Else
    Success = 0
    End If
    rs.Close
    Set rs = Nothing

    NEXT - Try using DTSTASK~ instead of DTSSTEP

    RobbieD
    Sunny Times...

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •