DTS Error

[George1111]

I am trying to import an Access 2000 database to SQL Server 2005 Express using DTS

I am using the DTS Wizard manually to do the import to SQLExpress

Keep getting the following error

Appreciate any ideas, thanks

[George1111]

The problem above was resolved with a registration issue

regsvr32 SQLTaskConnections.dll


HOWEVER

I then went on to try to Import the Data via DTS and kept getting an error on a specific table name

(All the tables were now created in the SQL Database though - with no data transferred)

Tried everything but kept stopping at the table name and no data would transfer

Finally discovered that by limiting the number of tables to 64, the DTS wizard happily imported the data

This is clearly a design limitation in DTS

1) It will create the table structures of the 150 tables I have in the SQL Database

2) DTS will not copy the rows to the tables UNLESS you try it with 64 or less tables

I certainly did not expect that with SQL Server and its associated tools

So how about that ???

This is where I found the solution after searching for a couple of hours

http://sqljunkies.com/Forums/ShowPost.aspx?PostID=10717

[KrisSimonis]

My 'Wild' guess would be that beyond 64 tables, DTS can't keep proper track anymore of relations in the database properly and gets confused as to which table to import first.
It could also be that the designers decided that beyond 64 tables, performance would degrade beyond anything usefull if DTS had to keep track of everything. Or it's more along the lines of.. nobody in their right mind would build a database where there are more than 64 tables that are all interlinked in some manner and have to be imported together for relational integrity to be preserved. ( 640K ought to be enough for everybody... )

( and no, I didn't read what's on that post you linked, can't access it due to restrictions on my internet activities at work to limit my internet addiction )

[George1111]

I have an Access database with 150 tables

This is not exactly large for an Accounting / CRM / ERP system

NONE of the tables are interlinked so there are no relational integrity issues.

Anyway, if this would be the case then the limit would be on the number of relationships, not number of tables

All I want to do is create a SQL Server Database from my Access database.

Its just that the designers at Microsoft have built in a restriction of 64 tables

Why 64 ? Why not 32 ? Why not 66 ? Why not 128 ?

Main thing is that I found the solution and am passing it on so that some other unsuspecting soul wont be hunting for a ghost as I was

Why didn't they just say "Dont import more than 64 Tables"

There was a warning at the beginning of the import that it "may" be advisable to restrict the number of tables being imported but NO WAY did it specify a limit of 64.

That would have been just too easy