Hi,

I am trying to use a DTS package for horizontal partitioning in a
transactional replication. I want to use a lookup from another connection to
determine replicated row is in the partition or not. Distribution agent says
a VBScript run time error occured "type mismatch, DTSLookups". I debugged
the dts package and saw DTSLookups object has not been created. DTSSource
and DTSDestination objects could be accessed but DTSLookups not.
If I execute DTS package manually without "SQL server replication OLE DB
Provider for DTS" connections, DTSLookups object is created but if I execute
package with distrib.exe from command line, there is no DTSLookups object .
Transform vbscript code is below.

What am I doing wrong ?. Could Lookups not used within replication DTS
package ?

TIA

Askin

'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the destination column
'************************************************* ***********************
Function Main()
' If the current source data is the old values of the row before an
update, verify if the old row is in the partition or not.
' ReplicationChangeType values:
' 1 = Insert. Source data is from a row that was inserted at the
source.
' 2 = Update. Source data is from a row that was updated at the source.
The data are values before the update.
' 3 = Delete. Source data is from a row that was deleted at the source.
' 4 = Before Update. Source data is from a row that was updated at the
source. The data are values before the update.

If DTSGlobalVariables("ReplicationChangeType").Value = 4 Then
OldRowInPartition = IsInPartition()
Main = DTSTransformStat_SkipRow
Else 'Error check to prevent users from forgetting to enable the
article for DTS horizontal partition.
If DTSGlobalVariables("ReplicationChangeType").Value= 2 and
IsEmpty(OldRowInPartition)=True Then
Err.Raise 1, "Replication DTS ActiveX Script", "The article does
not support DTS horizontal partitions."
End If
If IsInPartition() Then
'Set default return status, which means using the query type set by the
replication process.
Main = DTSTransformStat_OK
Transform

' If it is an update, test to see if the row has just moved into the
partition. If so, overwrite the query type to insert from update.
If DTSGlobalVariables("ReplicationChangeType").Value = 2 And Not
OldRowInPartition Then
Main = DTSTransformStat_InsertQuery
End If
Else

' If it is an update, test to see if the row has just moved out of the
partition.
' If so, overwrite the query type to insert from update.
If DTSGlobalVariables("ReplicationChangeType").Value = 2 And
OldRowInPartition Then
Transform
Main = DTSTransformStat_DeleteQuery
Else
Main = DTSTransformStat_SkipRow
End If
End If
End If
End Function

Function Transform()
DTSDestination("ANumber") = DTSSource("ANumber")
DTSDestination("JobRefNo") = DTSSource("JobRefNo")
....
....
End Function

Function IsInPartition()
If DTSLookups("clientlookup").Execute(DTSSource("clie ntno")) = 2 Then
IsInPartition = True
Else
IsInPartition = False
End If
End Function