Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007
    Posts
    33

    Exclamation Unanswered: DTS Active X - Check if recrords exist before sending output

    I've written a DTS transaction that I would like to schedule as a job. I don't want it to run if there is nothing to send.

    I this example below on another website and have changed the connection string to reflect my settings. The password has been stared out for security reasons. It's not the way it actually appears in the script. This keeps erroring out on line 21 saying expected end of statement.

    I'm sure I'm missing something small and would really like to get this done tomorrow before I leave for the weekend. Any help would be greatly appreciated.
    Code:
    Function Main()
    dim myRecordset
    dim iRowCount
    
    dim sConnection
    
    sConnection ="Provider=sqloledb;" & _ 
             "Data Source=GLPT-SQL\CSI_SQL;" & _
             "Initial Catalog=GLPTWEB;" & _
             "User Id=sa;" & _
             "Password=*************"
    
    Dim oOleDbConnection
    oOleDbConnection = New OleDb.OleDbConnection(sConnection)
    oOleDbConnection.Open()
    
    mySQLCmdText = "Select Count(*) FROM [GLPTWEB].[dbo].[RunTicketDataView]"
    
    Dim myConn As New OleDbCommand
    myConn(mySQLCmdText , oOleDbConnection)
    
    
    set iRowCount = myConn.ExecuteReader
    
    If iRowCount.Value = 0 then
       Main = DTSTaskExecResult_Failure
    Else
      
       Main = DTSTaskExecResult_Success
    End If
    
    End Function

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can do this in T-SQL and it's a lot easier
    Code:
    IF NOT EXISTS (SELECT 1 FROM myTable) BEGIN
      RAISERROR('Oh dear, no records, 10, 10)
    END
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2007
    Posts
    33
    RAISERROR doesn't send a failed status to DTS stopping the next transaction. I've tried this with no records and it still send a blank file.

  4. #4
    Join Date
    Feb 2007
    Posts
    33
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76251

    RAISERROR only fails a DTS step if the severity is raised. I set mine to 18 like the article I've linked to above says and it worked. Thanks George for your help. I hate using ActiveX in my DTS transactions.

Posting Permissions

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