March 31, 2003 at 11:17 am
I have the following code in an ActiveX task in SQL Server 2000 DTS. This code sets the cube processing filter correctly when I execute it manually from within DTS Designer but when the package runs as a scheduled job, it seems to set the filter to the beginning and ending cdr_id values of the next to the last entry in the batch import table. When I run the sql statement in QA, the correct result values are returned. Weird, eh? Any ideas of what could be going on here?
Thank you,
Michael
Function Main()
On Error Resume Next
Dim pkg
Dim task
Dim props
Dim firstrec
Dim lastrec
Dim ibid
Dim cn
Dim rs
Dim strSQL
firstrec = 0
lastrec = 0
Set cn = CreateObject("ADODB.Connection")
cn.open "Provider=sqloledb;" & _
"Data Source=myserver;" & _
"Initial Catalog=mydatabase;" & _
"Integrated Security=SSPI"
Set rs = CreateObject("ADODB.Recordset")
strSQL = "SELECT Max(import_batch_id) AS import_batch_id, "
strSQL = strSQL & "Max(batch_start_num) AS batch_start_num, "
strSQL = strSQL & "Max(batch_end_num) AS batch_end_num "
strSQL = strSQL & "FROM tblCDR_Import_Batch WHERE "
strSQL = strSQL & "batch_fact_table = 'aged'"
rs.open strSQL, cn
If NOT rs.EOF Then
'ibid = rs("import_batch_id").value
firstrec = rs("batch_start_num").value
lastrec = rs("batch_end_num").value
End If
rs.close
Set rs = Nothing
cn.close
Set cn = Nothing
'MsgBox "FirstRec: " & firstrec & vbCrLf & "LastRec: " & lastrec & vbCrLf & "import batch id: " & ibid
Set pkg = DTSGlobalVariables.parent
If Err <> 0 Then
Main = DTSTaskExecResult_Failure
Exit Function
End If
Set task = pkg.Tasks("Switch_Management_Aged_Partition_Incr_Update")
If Err <> 0 Then
Main = DTSTaskExecResult_Failure
Exit Function
End If
Set props = task.Properties
If Err <> 0 Then
Main = DTSTaskExecResult_Failure
Exit Function
End If
props("Filter").Value = """tblCDRFact""" & "." & """cdr_id""" & " BETWEEN " & firstrec & " AND " & lastrec
If Err <> 0 Then
Main = DTSTaskExecResult_Failure
Exit Function
Else
Main = DTSTaskExecResult_Success
End If
Set pkg = Nothing
Set task = Nothing
Set props = Nothing
End Function
Michael Weiss
Michael Weiss
April 2, 2003 at 12:31 pm
Hi mhweiss,
Two options to setup:
1.Execute Meain on pkg threat and
2.Close connection on completion.
These options are in the worflow properties.
Test again and let me know.
Johnny
April 2, 2003 at 12:49 pm
Thank you, Johnny...I had the execute on main thread option checked but had never enabled the close connection on completion option...I will try that and post the results.
Thanks again,
Michael Weiss
Michael Weiss
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply