DTS Transformations in SQL Server

Hi all,

Please can anyone help me with this? When I run the following DTS script in SQL Server I get the error "Microsoft VBScript compilation error, expected 'Function', line 35."

Line 35 is the line with the IF statement, so that is obviously the problem, but I have done exactly the same thing before with another script and it works. The datatypes of the source and destination are the same, the lengths are different obviously. Any ideas?

'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************
Function Main()
DTSDestination("Prac_Code") = DTSSource("Practice Code")
DTSDestination("Name") = DTSSource("Practice")
DTSDestination("List_Size") = DTSSource("List Size")
DTSDestination("PCG") = DTSSource("PCG")
DTSDestination("Add1") = DTSSource("Address1")
DTSDestination("Add2") = DTSSource("Address2")
DTSDestination("Add3") = DTSSource("Town")
DTSDestination("Add4") = DTSSource("BOROUGH")
DTSDestination("Pcode") = DTSSource("Post Code")
DTSDestination("Tel") = DTSSource("Tel")
DTSDestination("Fax") = DTSSource("Fax")
DTSDestination("Premises_Note") = DTSSource("PremisesNote")
DTSDestination("PMS") = DTSSource("PMS")
DTSDestination("PMS_Notes") = DTSSource("PMS Notes")
DTSDestination("Note") = DTSSource("Note")
DTSDestination("Num_Prncpls") = DTSSource("No of Prncpls")
DTSDestination("Rent") = DTSSource("Rent£")
DTSDestination("Ave_per_WTE") = DTSSource("Ave per WTE")
DTSDestination("Num_Sal_GP") = DTSSource("No Sal GPs")
DTSDestination("Vacancies_WTE") = DTSSource("VacanciesWTEs")
DTSDestination("Type_Rent") = DTSSource("Type of Rent")
DTSDestination("B_Type_Rent") = DTSSource("BType of Rent")
DTSDestination("Branch_Rent") = DTSSource("Brent£")
DTSDestination("Out_of_Hours") = DTSSource("OOH")
DTSDestination("WTE_Detail") = DTSSource("WTE Detail")
DTSDestination("WTE_Date") = DTSSource("WTEDate")
DTSDestination("Train_Practice") = DTSSource("TrgPract")
If DTSSource("Branch") = "Yes" then DTSDestination("Branch") = "Y"
End If
If DTSSource("Branch") = "No" then DTSDestination("Branch") = "N"
End If
DTSDestination("Branch_Add1") = DTSSource("Baddress1")
DTSDestination("Branch_Add2") = DTSSource("Baddress2")
DTSDestination("Branch_Add3") = DTSSource("Btown")
DTSDestination("Branch_Pcode") = DTSSource("Bpost code")
DTSDestination("Branch_Tel") = DTSSource("BTel")
Main = DTSTransformStat_OK
End Function


Thank you !

If DTSSource("Branch") = "Yes" then DTSDestination("Branch") = "Y"
End If
If DTSSource("Branch") = "No" then DTSDestination("Branch") = "N"
End If

Single line IF blocks dont Need End IF

Either Use
1.
If DTSSource("Branch") = "Yes" then DTSDestination("Branch") = "Y"
If DTSSource("Branch") = "No" then DTSDestination("Branch") = "N"

2.

If DTSSource("Branch") = "Yes" then
DTSDestination("Branch") = "Y"
End If
If DTSSource("Branch") = "No" then
DTSDestination("Branch") = "N"
End If

REGARDS,
SINDHU

Thankyou. I have heard that before but I thought it was possible either way (with or without the end if). It works now.

Thanks!