Hi:

This is driving me crazy-I can see no reason for this ActiveX sript
task to timeout. The code for the task is below, first, here is a
description of what I am trying to accomplish:

1. I have a begin loop that precedes the task in question. It takes
logon and server parameters for a Sybase server, so each loop it sets
this pump to pull from a different server. The data structure on each
server is identical-the job here is to consolidate data from mulitple
servers. This initial task works fine.

2. I then have this task that is having problems. It's job is to query
the Data Mart on the SQL Server side for the maximum date it currently
is storing, and then set the query on the Sybase tables so it will only
get the data it needs-anything greater than that date. (If there is no
data in the database then it sets the maximum date to Jan 1, 1970). It
does this individually for each table. Once this ActiveX task has set
the query for each data pump task (there are 15 tables i.e. 15 data
pump tasks), it moves on to executing each data pump in sequential
order and then loops again to the next Sybase Server until done.

The problem is that, if I start from a blank database on the SQL Server
side, it runs through the pump loop fine the first time, and then the
second time it gets to the problem task and this time it chokes on the
line Set objRS1 = objconn.execute (strSQL) with a "Timeout Expired"
error (it fails the first time it hits this line). You can see I put
msgBox code on each side to make sure it was passing the correct query.
It was passing the proper query, and the query is basically the same as
the first time around. The table after the first time around only has
2448 records-and this is a MAX query, so it should run in a small
fraction of a second. Because this time we are looking for data from a
second site, it should come up blank again, since the first time we
pumped data from the first site into the database. (Although I've
tested in situations where it doesn't come up blank and the same issue
occurs.)

Any idea what is happening here?

Thanks,
Kayda

Here is the code:

Function Main()
Main = DTSTaskExecResult_Success
End Function

Function SetSQLStatement(TableName,TaskName)
Dim oPkg, oDataPump, sSQLStatement, strSQL, objRS1, objconn, SiteName,
testSQL

SiteName = DTSGlobalVariables("gvSiteName").Value
' Build new SQL Statement

Dim varMaxDate, tempdate
Set objconn = CreateObject("ADODB.Connection")
objconn.open = ("provider = sqloledb; data source = RASA-WIN2003;user
id = sa;password = password")
objconn.DefaultDatabase = "DM_Database"
strSQL = "SELECT MAX([Timestamp]) AS MaxDate FROM " & TableName & "
WHERE Site = '" & SiteName & "'"

msgBox "MaxDate Query is: " & strSQL

Set objRS1 = objconn.execute (strSQL)

msgBox "Executed Max Date Query"

If isnull(objRS1.Fields("MaxDate")) Then
varMaxDate = #1/1/1970#

Else
tempdate = CDate(objRS1.Fields("MaxDate"))
varMaxDate = FormatDateTime(tempdate,2)
End If

objRS1.Close
objconn.Close
Set objRS1 = nothing
Set objconn = nothing


sSQLStatement = "SELECT * FROM " & TableName & " WHERE Timestamp >
'" & _
varMaxDate & "' AND Site = '" & SiteName & "'"


' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks(Cstr(TaskName)).CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

SetSQLStatement = DTSTaskExecResult_Success
End Function

SetSQLStatement "Table1", "DTSTask_DTSDataPumpTask_1"
SetSQLStatement "Table2", "DTSTask_DTSDataPumpTask_2"
SetSQLStatement "Table3", "DTSTask_DTSDataPumpTask_3"
SetSQLStatement "Table4", "DTSTask_DTSDataPumpTask_4"
SetSQLStatement "Table5", "DTSTask_DTSDataPumpTask_16"
SetSQLStatement "Table6", "DTSTask_DTSDataPumpTask_5"
SetSQLStatement "Table7", "DTSTask_DTSDataPumpTask_10"
SetSQLStatement "Table8", "DTSTask_DTSDataPumpTask_8"
SetSQLStatement "Table9", "DTSTask_DTSDataPumpTask_6"
SetSQLStatement "Table10", "DTSTask_DTSDataPumpTask_7"
SetSQLStatement "Table11", "DTSTask_DTSDataPumpTask_9"
SetSQLStatement "Table12", "DTSTask_DTSDataPumpTask_11"
SetSQLStatement "Table13", "DTSTask_DTSDataPumpTask_12"
SetSQLStatement "Table14", "DTSTask_DTSDataPumpTask_13"
SetSQLStatement "Table15", "DTSTask_DTSDataPumpTask_14"

Main()