Connection Manager to connect ot Access mdb
How do you setup a conntection in ssis to connect to an access mdb? I have used DTS to upload access tables to sql and want to do the same in the new ssis services. I migrated the DTS package but the connection in created for the access mdb does not work in ssis and is shown to be be illegal. The migration did not post any errors when it migrated the package.
February 8th, 2006 8:41pm

Use a OLE DB ConnectionManager. What is yourmigrated package using? Why is it illegal? What error message do you get? -Jamie
Free Windows Admin Tool Kit Click here and download it now
February 8th, 2006 9:40pm

When you double click on the connection you get the error: The specified provider is not supported. Please choose different provider in connection manager. When you do get in it is all greyed out and when you test connection you get the follow error in this screen shot: http://216.247.79.34/public/ssiserror.jpg Here is the DTS 2000 input setup for the access file using DSN http://216.247.79.34/public/ssiserror1.jpg
February 8th, 2006 11:11pm

Try using the "Native OLE DB\microsoft jet 4.0 OLE DB Provider" Provider. I've just installed SSIS on a clean O/S and this is working fine. -Jamie
Free Windows Admin Tool Kit Click here and download it now
February 9th, 2006 1:06am

This connection type enables me to connect to the mdb access file alright (so thanks) but then it breaks down the mdb source into it's individual tables. ie. When I am in the OLE DB source editor I am using the Data Access mode: Table or View. I would like to import the mdb as a whole database instead of importing each table within it one at a time. How does one import all the tables at once? Thank you.
February 15th, 2011 12:10pm

You can't. You may want to try the Import/Export Wizard in SSMS, or the upsizing wizard in Access. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
February 15th, 2011 12:53pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics