I don't do much DTS but his should be real simple. I'm using the data import wizard to import an
Excel spreadsheet into a table. I need to concatinate the first and last name fields in the source
into the NAME field in the destination. So, chose to use an ActiveX script transformation and here
it is:


Function Main()
dim fname
dim lname
dim fullname

fname = DTSSource("FIRST NAME")
lname = DTSSource("LAST NAME")
fullname = fname & " " & lname

DTSDestination("State") = DTSSource("STATE")
DTSDestination("Company") = DTSSource("LAST NAME")
DTSDestination("Name") = fullname
DTSDestination("Address1") = DTSSource("ADDRESS")
DTSDestination("Address2") = DTSSource("ADDRESS 2")
DTSDestination("Phone Number") = DTSSource("PHONE")
DTSDestination("City") = DTSSource("CITY")
DTSDestination("ZIP Code") = DTSSource("ZIP")
Main = DTSTransformStat_OK
End Function


I keep getting the error "Invalid procedure call or argument: 'DTSSource'"

WTF!?!?!


--Buddy

Perhaps you could use DTSDestination("Name") = DTSSource("FIRST NAME") & " " &
DTSSource("LAST NAME")

Not sure as I haven't tried this myself, but it may work.

-Pete

Buddy Ackerman <buddy_nospam (AT) buddyackerman (DOT) com> wrote:

What happens if you simply concatenate the two source fields?

DTSDestination("Name") = DTSSource("First Name") & " " & DTSSource("Last
Name")


Does Excel not allow you to do this in the SourceSQLStatement. If it does
then you will circumvent the need to do things Row * Row



"Buddy Ackerman" <buddy_nospam (AT) buddyackerman (DOT) com> wrote

Yeah I tried that first and got the same error message, that why I tried to assign the to local
variables first then concatenate them and assign it to the destination. I don't understand why it's
not working. It seeems too simple.


--Buddy


Peter A. Schott wrote:

That's what I tried first but got the same error message.


Well, I tried you suggestion of selecting the Excel data by query and I created a field that is the
concatenated first and last name ahnd that worked. Good suggestion.


I still don't understand why the original code didn't work.


--Buddy



Allan Mitchell wrote:

Hey Buddy,

Did you ever figure this out. It happened to me and it was SQL declareitive
claws stuck int the source and destination columns.

"Buddy Ackerman" wrote: