"Changed from "Microsoft.ACE.OLEDB.12.0" to, "Microsoft.Jet.OLEDB.4.0" and all is well."
Where and how do u change it?
Actually, I just created a New Connection Manager by doing the following:
1. Right clicked in the, "Connection Manager" space.
2. Selected, "New OLE DB Connection..."
3. Clicked the, "New..." button.
4. Selected, "Microsoft Jet 4.0 OLE DB Provider" in the "Provider:" box at top and clicked OK.
5. Under, "Database file name:" I clicked the, "Browse..." button and navigated to a network share that contains the desired Access DB (making sure that the SQL Agent on the server that will schedule and execute this package has permissions to the network share).
6. In my case, the Access DB isn't password protected or anything so I just left the, "Admin" User Name and blank Password checkbox unchecked. Clicked the, "Test Connection" button (though this really isn't checking anything more than the fact that the Access DB had no User Name or Password.
7. "Ok" my way out of the dialogs and viola...new Connection Manager created.
8. Now I changed everything in my SSIS package to use the NEW Connection Manager I just created.
9. Finally, I built the package, copied the dtsx file from my SSIS project's "bin" folder to a location accessible by the SQL Agent scheduled to execute the package, created a new SQL Agent job on the scheduling server, pointed the job to the dtsx file, scheduled it, and done.
I guess I could have just edited the, "Provider" portion of the "ConnectionString" property of the existing Connection Manager (the one that wasn't working because it was set to use the ACE provider.) If I had done it this way...
1. Right click on the existing Connection Manager
2. Select, "Properties"
3. In the Properties pane, changed the ConnectionString property from something like this:
I didn't do it that way because I wanted to keep the original Connection Manager around in case my assumption about the Provider being the culprit was wrong.
Hope that helps.