Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

SQL Server IMPORT and Export wizard fails to import ~100+ tables from MS Access 2003 database

I want to import ~100 tables from MS Access database but I get "Too many sessions" error message.
I want to use "Optimize for many tables" check box on "Select Source Tables And Views" dialog of the wizard but it is greyed out.
What can I do to get things done?
0
midfde
Asked:
midfde
  • 4
  • 3
1 Solution
 
PedroCGDCommented:
could you clean the tables, leave only few records and attach the database access here to try help you?
Regards!
0
 
midfdeAuthor Commented:
Yes, I am attaching the database,
However I'd like to make a couple of notes:
  • I've gotten thinks done already by means of MS Access "Upsize Database" facility.
  • The question "Why cannot I just import tables into SQL Server database?" remains unanswered and is illustrated on the attached image
DataDock.mdb
-.bmp
0
 
PedroCGDCommented:
Which version you have? The Express?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
midfdeAuthor Commented:
select @@version
--------------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.2040.00 (Intel X86)
Mar 13 2006 11:20:51
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

(1 row(s) affected)
0
 
PedroCGDCommented:
try to install SQL 2005 SP2 and test again
0
 
midfdeAuthor Commented:
Sorry, I cannot "try" it without long bureaucratic procedures. It's enterprise edition. Remember?
0
 
PedroCGDCommented:
but you must install SP2, fix a lot of bugs!
0
 
HoggZillaCommented:
Optimize for Many Tables and Run in a Transaction are not options available with MSAccess import, but this should not stop you from importing the tables.
Save the import SSIS package, do not Execute Immediately.
Open the package and edit the concurrent processes to a small number. What is probably happening is SSIS is opening a connection to the .mdb, one for every table. Save the package and change the process. This is a MSAccess constraint, not SSIS.
I will attach the Package. Change the properties in DestinationConnection and SourceConnection to meet your reqs. Change the package property of MaxConcurrentExecutables to a reasonable number. This will limit open sessions in MSAccess.
I am having trouble attaching the file, but you can probably build it yourself.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now