I am dts to import an access db. The first time I ran it using 2000
everything worked flawlessly. Then when 2005 came out I ran it and
immediately found problems. The first was I had to figure out to clear the
user setting “Admin” in the advanced section of the source.

Next I ran into the second problem “enable insert identity”, this made sense
after looking at it but being inexperienced at it I did not realize it. Then
I had to go though all of the tables and select the edit button for each
table and enable the identity insert. This got tedious after about twenty
tables, but there are no “global settings” for the import so you are forced
to select the edit button for each. After this I made sure to save the
import as a dts package so I could redo the results.

I had "run immediate" selected so I could run the import. Some statistics
were updated and the import failed about three quarters of the way on the
pre-execute. Import reported an error number, but no error text, and a table
name. It was not
a table I was concerned with so I went back through and disabled the table
and repeated with the same error on another table and got the same results. I
repeated this again and got the results again so I stopped. Somehow in 2000
this
whole process worked with no problem. I had beta 2 of vs installed with sql
server 2005 express when I ran the import on sql server 2000 but I never used
it.

I am wonder if in the 2000 version I selected “sql native client” using the
2005 express and everything just worked. I could kick myself now for not
saving what I did in 2000 but “those are the breaks”. I did examine the dtsx
packages in 2000 and 2005 and to ms’s credit the 2005 is an xml file but I
did not take the time to examine the whole thing.

Right now I am wondering how to make the import work like it did in 2000. Does
anyone have any suggestions.

Also, when I had to rerun the import I tried going back and selecting the
“drop table and recreate” but this did work and there were errors with the
create being selected. It surprised me that the create and drop and recreate
do not work in tandem. This meant I had to drop the tables manually so I used
management studio to drop them though the GUI. When I reran the import and
looked for the tables in management studio it did not show them. I tried the
refresh button and switching views but I ended up closing management studio
and re-opening to see what tables had been created.



The whole process has reminded me to take notes and save the information.
Anyway I appreciate your timely response and any information you can provide.