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?

IF {ELSE IF} Construct

Hi

Since there is no IF {ELSE IF} constructs in TSQL, I assume the following will do the equivalent of ELSE IF, please verify. Thanx :)

IF condition
BEGIN
-- some TSQL
END
ELSE IF condition
BEGIN
-- some TSQL
END
ELSE
BEGIN
-- some TSQL
END

JamesPlease comment on any deviation from standard programming that this IF ELSE IF construct may introduce. I am too novice to see it.

Cheers

James|||This should work, but your code will be more readable if you can use a CASE statement instead.

blindman|||true
but isn't IF and ELSE more efficient than CASE
I assume that because what i said is true in general programming

cheers
james|||For a single criteria IF ELSE is probably more efficient, but when you start nesting IF statements I doubt there is any difference. The db engine has to make the same logical comparisons in either case.

Evaluation of a CASE statement completes as soon as a match is found, and further criteria are not considered. I'm not sure if this is true of nested IF/ELSE statements; ie, the optimizer may evaluate the entire statement. Perhaps someone else on the forum knows how the optimizer handles this scenario.

Truth is, neither of these is a very fast operation when performed against large tables. You gotta do what you gotta do.

blindman|||Originally posted by nano_electronix
Hi

Since there is no IF {ELSE IF} constructs in TSQL, I assume the following will do the equivalent of ELSE IF, please verify. Thanx :)

IF condition
BEGIN
-- some TSQL
END
ELSE IF condition
BEGIN
-- some TSQL
END
ELSE
BEGIN
-- some TSQL
END

James

I think this is the correct solution. Lets say "condition" refers to weekend day, a holiday or a week day flag and "TSQL" refers to three totaly diffrent queries. Your code would be resonable.

Now lets say "TSQL" is identical except for the treatment of a date column and all you want is the words "Holiday", "Weekend" or "Weekday" returned in your query, a CASE statment might be the better choice.

Clear as mud?|||agree :)