SSIS 2008 can't connect MDB via OLE DB JET 4
I'm using SSIS SQL 2008 connect to MS-Access MDB via OLE DB Jet 4. I used to work but now I couldn't connect any more. I got "connection failed because of an error in initializing provider. unspecified error." I still can connect the via SSIS SQL 2005. Really need help.. Thanks,
September 26th, 2009 12:53am
Thanks Da Lin, I have 32bit SQL server on my desktop. This error I got when I tried to connect mdb file via BIDs and click test connection. Sometime when I created new SSIS solution, selected Data Flow control, added new OLE DB Datasource, selected OLEDB Jet 4, selected the same MDB, click Test Connection andgot connection successfuly. ThenI added existing package theone didn't work to this solution and execute. itwork ok. If I hosted the workingpackageonSQL Server and executed the package it also works fine. Just today,I tried to do the same process as above for another package and it didn't work at all.Anyone had the same issues? Thank you,
September 26th, 2009 4:07am
No, I copied MDB file to my local drive and just tried to create new data flow.Thanks Phil,Charlie.
September 26th, 2009 12:34pm
Is your machine 64-bit? Maybe 64-bit DTExec is running when you run the package even if your SQL Server is installed as 32-bit. Testing the connection would always be 32-bit in BIDS, but depending on the solution settings. On a 64-bit machine, packages run in 64-bit mode by default. Use the Run64BitRuntime setting on the Debugging page of Project Properties to change this setting. The default value is True, which is simply ignored on a 32-bit computer.Sorry Jet's "unspecified error" is very vague. Jet 4.0 is not supported any more. The new ACE provider is supported still. Jet needs access to the temp folder, so under some accounts if the Temp folder (whatever folder your environment variable points to). You can see the temp folder by running "set temp" at the command prompt. Then make sure the account which is running the SSIS package can see that folder and read & write files.Also check the version of Msjet40.dll. Sometimes the version of the dll is replaced with an old version and trouble happens. http://support.microsoft.com/kb/818182Thanks, JasonDidn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
September 29th, 2009 8:17am
My local machine is running XP SP3. I checked all Msjet40.dll and all are up to date. I reinstall XP SP3 and hope this will fix, but still didn't work. I do have SSIS (SQL 2005) on the same machine. Whe I created new package and using ole db jet 4 in VS 2005 version is fine. I went back to do the same in SSIS (SQL 2008) and still having the issue. Not sure which one cause the issue between SQL and Visual Studio. really appreciate for all suggestions. Thanks, Charlie.
October 5th, 2009 8:15pm
I have repair both VS2008 and SQL 2008 on my workstation and hope to fix this issue. But still unslove. I still have VS2005 and it work fine with the same mdb file. Which mean mdb file is ok. I think VS2005 use the same oledb jet as VS2008. I'm not sure if this is really VS2008 issue??
October 29th, 2009 3:22pm