Hi, I’m using DTS to transfer data between 2 sql server 2000 databases.

In one of the old tables there is a Boolean field (blnContactable) which
stores true or false values.

The new table equivalent has an integer field (intContactable) which stores
values between 1 and 5.

How do I map the old data to the new, with the rule, If blnContactable =
true then intContactable = 2.

In my DTS package I have this VB script which maps the other fields ok.

Function Main()
DTSDestination("strLogonName") = DTSSource("strLogonName")
DTSDestination("intYear") = "2004"
DTSDestination("strAbsRsnCode") = DTSSource("strAbsRsnCode")
DTSDestination("dtmAbsStart") = DTSSource("dtmAbsStart")
DTSDestination("dtmAbsEnd") = DTSSource("dtmAbsEnd")
DTSDestination("decDuration") = DTSSource("decDuration")
DTSDestination("strAMPMStart") = DTSSource("strAMPMStart")
DTSDestination("strAMPMEnd") = DTSSource("strAMPMEnd")
DTSDestination("strNotes") = DTSSource("strNotes")
DTSDestination("strSelfMedical") = DTSSource("strSelfMedical")
DTSDestination("blnPhonedIn") = DTSSource("blnPhonedIn")
DTSDestination("intParadigmAbsID") = DTSSource("intParadigmAbsID")
DTSDestination("blnDeleted") = DTSSource("blnDeleted")
DTSDestination("blnModified") = DTSSource("blnModified")
Main = DTSTransformStat_OK
End Function


But it fails when I try to add the following line.

DTSDestination("intContactableID") = IIf(DTSSource("blnContactable") = 1, 2,
0)

You need to use If...Else logic for these and you need to
check the value of the source first to determine the value
that is going in the destination column, e.g.
If DTSSource("blnContactable") = 1 Then
DTSDestination("intContactableID") = 2
Else
DTSDestination("intContactableID") = 0
End If

-Sue

On Mon, 13 Sep 2004 02:21:05 -0700, jez123456
<jez123456 (AT) discussions (DOT) microsoft.com> wrote: