Using LEFT function in a DTS ActiveX script

[PRMiller]

Hello,

I am importing 9 text files to a staging table. My text files each come from a different source system (each at a different facility), although they have identical formats. As I load each row into the the staging table, I add a foreign key value and dump the staging table into production -- thus, ten source systems become one table.

Recently, we added a 10th location. Unfortunately, that source is piggybacking on one of my existing source text files. When I import to staging, I need to find a way to differentiate between those two locations.

Here's a sample of my data:

"Extension","Name","Allow VDN Override","COR","TN","Vector Number"
"227048","MBR INFLW W4","n","20","1","698"
"227049","MBR INFLW S3","n","20","1","698"
"452000","LSV IVR IN","y","20","1","199""452001","LSV","n","20","1","199"
"452002","HMF IVR IN","y","20","1","199"

Every row from the source file in which Extension begins with a 22 needs to be tagged with a numSite_fk of 5. Everything that begins with a 45 should be numSite_fk = 10.

Here's what I set up in my DTS package, under ActiveX scripting:

'  Copy each source column to the destination column
Function Main()

	if Left(DTSSource("Extension"),2) = 45 then
		DTSDestination("numSite_fk") = 10
'		Main = DTSTransformStat_OK
	else
		DTSDestination("numSite_fk") = 5
'		Main = DTSTransformStat_OK
	end if

End Function

I'm not sure where I went wrong. It parses fine, and I've used the LEFT function quite often in VBA. It's listed as a valid function in VBScript. Here's the error I get:

"ActiveX Scripting Transform 'DTSTransformation__1': Function 'left' was not found in the script."

I'm pretty new at this, so I'm sure I've missed something basic. Can someone steer me in the right direction?

Thanks,
Paul

 

[PRMiller]

Ok, now I'm even more confused... and getting rather irritated. Decided to get rid of this and go back to my earlier transform, as follows:

'  Copy each source column to the destination column
Function Main()
	DTSDestination("numSite_fk") = 5
	Main = DTSTransformStat_OK
End Function

Saved, attempted to execute. Same. Error.

What?

"Left" isn't in there anywhere! So NOW what am I missing??