>What about using a CASE expression. Maybe something like :
>
>EXECUTE storeprocedure (CASE WHEN DTSSource(column) = '19900101' THEN NULL ELSE DTSSource(Column))
>
>??
>
>-Mike
Thanks.
Here is an example of my code.
If Isnull(DTSDestination("otm_appointment_date")) or Isempty(DTSDestination("otm_appointment_date")) Then
DTSDestination("otm_appointment_date") = CHR(34) & "(NULL)" & CHR(34)
Else
DTSDestination("otm_appointment_date") = CHR(34) & DTSDestination("otm_appointment_date") & CHR(34)
End if
'*******************************************************************************Sub InsertTourVac _
( _
PersonID, _
otm_appointment_date, _
otm_tour_date, _
otm_tour_site, _
otm_tour_salesctr, _
otm_package_purch_dt, _
otm_status_code, _
otm_status_date _
)
cmd.ActiveConnection = cn
cmd.CommandText = "sp_InsertTourVac " & _
personid & "," & _
otm_appointment_date & "," & _
otm_tour_date & "," & _
otm_tour_site & "," & _
otm_tour_salesctr & "," & _
otm_package_purch_dt & "," & _
otm_status_code & "," & _
otm_status_date
' Execute the sp_InsertTourVac stored procedure
cmd.execute
End Sub
'*******************************************************************************
Note the date parameter. If the raw date is empty or null I have to make it CHR(34) & (NULL & CHR(34) or CHR(34) & " " & CHR(34) because DTS will not pass an empty value. Either way it converts to 01/01/1900 in the stored procedure.
I thought of fixing the date inside the stored procedure, but I am transforming 20,000,000 records and I would like to minimize the processing by getting the null date in on the first try.
Hope this helps.
Bob
Database Development Team