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.

Use a OLE DB ConnectionManager.

What is yourmigrated package using?

Why is it illegal? What error message do you get?

-Jamie

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

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

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.

You can't.  You may want to try the Import/Export Wizard in SSMS, or the upsizing wizard in Access.

Hello Sir, 

i have used import and export wizard to import data from a .mdb file and the wizard made me to create a package. 

Now how to access that package?

Sorry if my doubt looks silly i have never used ssis or import export before.