DTS/SSIS too many tables warning and subsequent error

I need to bring over a large number of tables' records (200+ tables) with the Import/Export Wizard. The tables are being imported from MS Access. A separate script run previously will create the tables, so the DTS wizard is only to bring over the data from the Access tables into the empty SQL ones.

First, I get the warning that indicates "a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".

Well, I proceed with the DTS and it tries to validate and takes a fair bit, but then it errors indicating:

"Error 0xc0202009: {2F0FABA0-5F4B-4310-97C0-76EA19893547}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)"

Can anyone shed any light on why I receive an "unspecified error" when tring to DTS a larger number of tables. It does not error, if I import 40 or so tables.

This was never an issue with SQL 2000 DTS.

Thanks

"a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".

The wizard told you that it may not be able to do it.

|||

Hey,

Hah! Nice man. Well I hear you on that one, but why is this an issue with the SQL 2005 DTS and not the older SQL 2000 DTS, and is there any way around it?

Thanks