IF construct in DTS

Hi All,

I am new to DTS. I have to pick data from a excel sheet and put it in SQL Server table. If the column value is N/A in the excel then I should load NULL in the target table. Else the value. This is being the case I wrote this construct and it seems to load only NULLs into the target and the values are not getting loaded.

If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then DTSDestination("ABC_transport") = NULL Else DTSDestination("ABC_transport") = DTSSource("ABC transport")
Main = DTSTransformStat_OK

Thanks in advance.

Regards,
Sathishtry the code below, use the line breaks I have...

If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
DTSDestination("ABC_transport") = NULL
Else
DTSDestination("ABC_transport") = DTSSource("ABC transport")
End If
Main = DTSTransformStat_OK|||Hi,

Thanks for the reply.

I tried it, still it loads only the NULL and not the values.

Regards,
Sathish|||Well the syntax is correct.

I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

Can you post a sample of the excel spreadsheet?|||Originally posted by rokslide
Well the syntax is correct.

I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

Can you post a sample of the excel spreadsheet?|||Okie,... well there are 1000 non N/A records, 5159 N/A records and 1 null,... how many non N/A records are you getting loaded in your db table?? 6159 or 5159?|||Hi,

That was pretty fast. I am getting loaded with 6159 NULLs.

Regards,
Sathish|||using the dts wizard to import the spreadsheet to a temp ABC transport table and using the following code...

'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************

Function Main()
DTSDestination("Renewal Month") = DTSSource("Renewal Month")
If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
DTSDestination("ABC transport") = NULL
Else
DTSDestination("ABC transport") = DTSSource("ABC transport")
End If
Main = DTSTransformStat_OK
End Function

I get it with values where they are meant to be...

Wanna try this as a test to try and locate your problem?|||Not much luck. Still its not working for me. Let me try for some time to see whether I can get it.

Thank you very much for the help.

Regards,
Sathish|||No worries. Let me know if you need more help, sorry I couldn't solve your problem.|||The problem is with the excel driver - the excel driver attempts to guess at the datatype based on the first X number of entries. Save the excel file as a csv file and do the same dts script using the csv file and it will work.|||By if he is using VBScript to do the transformation then it would be using it's typing (eg. everything is a string) to do the comparison...

Also, wouldn't that also mean that my test transformation wouldn't work where it did work?