Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Import/Export (DTS) and Replication (2000)  Importing data using dts

Author  Topic  hameed Starting Member 40 Posts Posted - 2001-11-04 : 09:34:01 I am importing a table from Access into SQL Server using the DTS wizard. One of the columns in the Access table is a name field that has it's data like so: LastName, FirstName MiddleName In my SQL server table it has three columns equivalent to this one column. Now using DTS, this is the code that it generates to do the import: '********************************************************************** ' Visual Basic Transformation Script ' Copy each source column to the ' destination column '************************************************************************ Function Main() DTSDestination("case_pid") = DTSSource("PID") DTSDestination("case_SSN") = DTSSource("cases_SSN") DTSDestination("case_CaseNumber") = DTSSource("cases_casenumber") DTSDestination("case_FirstName") = DTSSource("cases_name") DTSDestination("case_Address") = DTSSource("cases_address") DTSDestination("case_City") = DTSSource("cases_City") DTSDestination("case_State") = DTSSource("cases_State") DTSDestination("case_Zip") = DTSSource("cases-zip") DTSDestination("case_BirthDate") = DTSSource("cases_address") DTSDestination("employer_Employer") = DTSSource("employer_Employer") DTSDestination("probation_OrderedPay") = DTSSource("Ordered Payment") DTSDestination("probation_OrderedPayBalance") = DTSSource("Balance") DTSDestination("probation_LastPmtDate") = DTSSource("Last payment Date") DTSDestination("probation_LastPmtAmt") = DTSSource("Last payment Amount") Main = DTSTransformStat_OK End Function ************************************************ How can I change this line: DTSDestination("case_FirstName") = DTSSource("cases_name") so that i can split the data from the source table column to destination table three columns firstname, lastname and middlename? Or perhaps there is a better way to do this ... Please help