Basic Questions About DTS Import Wizard

[pnjones]

I am a newcomer to SQL and have been using the DTS Import Wizard to import data from CSV files and Excel worksheets into existing SQL database tables. For the most part it works well, but I have a few questions:

1. When there is an error in the incoming data, is there a way to get DTS to display the row number or key of the record causing the error?

2. Is there a way to tell DTS to replace records in the SQL table with incoming records having the same key?

3. Is there an easier way to import data into a SQL table, a third party utility perhaps?

Any suggestions would be appreciated.

Thanks,

Paul Jones

 

[MaffewW]

Paul,
1) Sometimes it displays the line number, sometimes it doesnt. When the DTS import fails double click the little red error icon on the left hand side of the window and it gives you more info about the failure reason

2) I dont think so easily from .csv or Excel files. you might be able to do it with active x scripts but that would be more than my level of knowledge. What you could do as a workaround is to build a package where the first step is to import your csv or excel file into a table called say 'importtemp', then on success, the next step would be an 'Execute SQL' task where you delete the records from your main table if they exist in your importtemp table. eg

delete from maintable where primarykeycol in (select primarykeycol from importtemp)

then the final step would be to import the data from importtemp to your main table. Pretty straightforward to setup. Maybe someone else has got another suggestion

3) I dont think so. DTS is very powerful and IMO quite easy to use for fairly basic stuff like this. I've heard of people buying SQL Server just for DTS alone and not even using the database engine

HTH

Matt

Brighton, UK

 

[SQLSister]

YOu can use the Execute SQL task to run an update statement for the records which match existing keys and an insert statement for the records which are new.

Usually if I need to do something like this I import to a staging table first and then run the execute SQL tasks.