Greetings,

I am a newbie in using DTS.

I would like to load data from 1 single excel sheet[tab] into multiple
fields of different tables in database.


EXAMPLE:
DATABASE TABLE


Table 1: COURSE
course_code
course_name
faculty_code(FK)

Table2: FACULTY
faculty_code(PK)
faculty_name
faculty_add
faculty_postcode
faculty_city
faculty_state
faculty_country



EXCEL SHEET
(In 1 *.xls file i have 1 sheet containing the "combined" field of the 2
table shown as below
course_code course_name faculty_code faculty_name faculty_add faculty_postcode faculty_city faculty_state faculty_country



I am a student, ive tried to load data *. xls to single table(eg: course
only) using the DTS function without any problem.
However, when i combined the table in excel(shown above) to load data, i
encounter an error saying:

"Error at Destination for Row number 1. Errors encountered so far in this
task: 1."

My lecturer said it is possible to load data into different field from
different table using only 1 worksheet[tab] from excel file(as shown above)
and require me to show it to her as a graded assignment.

She emphasize on using just "1 sheet", not using multiple sheet[tap] to load
data into the field of different table. (which is connected thru the FK)

I need to show it soon. Can any experts out there provide me some guideline
how it is done?
Your help is very much appreciated!

Thanks in advance!

Hi Benjamin,

Create a DTS package with the steps below

Load the data into a temp table
Copy the required field values into the specific table by using SQL
queries
Drop the temp table.

Cheers,
Kris

Hi Kris,

I appreciate your help and reply.
Your method can works but is there such thing as "mapping table" ?
because its not what she is looking for....

Regards,
ben


"Kris" wrote:

Hi Ben,

Use VBScript to load the data. And run it as DTS package.

Cheers,
Kris