Hi,

very new to DTS and would appreciate some help.

I would like to import an excel s/s to a number of tables. Unfortunately the s/s has gaps where the creator of the s/s has left out the ID field, where it is a repeat of the field above i.e. row one will have an ID entry in field1, if row 2,3,and 4 are entries for the same ID, then the field is left blank.

Would the best way be to audit the s/s beforehand to ensure that all fields are filled or is there a way fill these in during the DTS process? if so, any example code would be appreciated.

many thanks

Does the ID field in your excel spreadsheet have any significance?

If not, you can add your own identity column on your database and omit id key field during during the transformation process.

Hi,
thanks for replying ...
the ID field is the field required for the lookup i.e. Row 1 will have an entry in the ID field, say customer number; the following 4 rows will have nothing in this field as all entries are for the same customer number. The customer number would be needed to look up the relevant code to allow the data to be insertd into tables - giving a problem with those rows where the field is empty.

many thanks

How many records do you have in the excel sheet?
Is there any other field in your excel sheet that can uniquely identify the customer. If there is, import your data using dts and in the data transformation build your table from the excel sheet on make sure identity insert is not selected. make sure the field which has the customer number allows nulls

for example this your database table

pkkey int identity
cust_no int null
cust_name varchar null

when you move your data update your table with the customer number value corresponding to the matching null value records.

If you do not have many records, update your excel sheet first.

you may also consider creating a linkedserver to your excel work book and then using open query update the excel sheets.

Information on how to setup a linkedserver is available in Books Online

many thanks .. I will give it a go