OLEDB error while trying to import MDB data into SQL 2005

Hello,

I am getting an error while trying to import data into SQL 2005 from an existing Access MDB database. When I try this on another workstation there are no problems and the data is imported successfully.

Could someone tell me what to look for or what setting I have incorrect. On the workstation with the problem I had removed SQL 2000 Personal Edition and installed SQL 2005 Standard Edition. Also, I have both Visual 2003 and 2005 installed on this workstation as well.

Thanks

TITLE: SQL Server Import and Export Wizard

Could not set up data flow connections.

The connection type "OLEDB" specified for connection manager "{DAE0A984-862B-4E4A-890E-8DB4860C9F6B}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

ADDITIONAL INFORMATION:

The connection type "OLEDB" specified for connection manager "{DAE0A984-862B-4E4A-890E-8DB4860C9F6B}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({55FFB2C2-E461-4FDB-B3C4-EE4677B80C77})

BUTTONS:

OK

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Error)

Messages

The connection "SourceConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.
({3DDD68E3-30A4-4513-B0AB-45F340BAB4C0})

- Setting Destination Connection (Stopped)

- Validating (Stopped)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Stopped)

- Copying to [EJTest].[dbo].[Journal] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

Did you ever figure out how to fix this. I had the same problem on my laptop, and I had to rebuild my laptop from scratch to fix it. I have another person with the same problem. I would like to fix this with out rebuilding.

Thanks

Michael Robey
mrobey@.worldinsco.com

|||This is a SSIS issue.|||

Yes it is a SSIS issue. Open SQL Server Configuration Manager. Right click on SQL Server Integration Services. Select properties and in the Log On tab change the built-in account.

It should be the same account you are using to run SQL Server. In my case is a standalone server for test/dev purpuse so I don't use Network account. I changed both to Local System. That should fix the issue.

|||

I had the same problem too.

Thanks Carlos, fixed it perfectly.

|||

I had the problem in general. Couldnt import tables from sql2000 / 2005 same and different databases.

Trough Your remark (Thank You) i fiddeld a little with the properties of SSIS and MSSQL Services. if both log on as the built in local system user,it works too. or local system and a Windows user of the machine, thats ok too.

I had posted this problem in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=344363&SiteID=1

but never got a reaction from ms

With some combinations you can optionally save the package, with some the wizard throws an error.

A definite clarification from ms about the conditions would be very helpful.

Michael

|||It really solved the problem.
Thank you very much!!!
|||

Better late than never!

Your solution solved my issue as well.

Thank you very much!

|||

It worked for me as well,

I wanted to import a visual foxpro Database to SQL2005, I installed Visual Foxpro oledb (9.0) . but I got the Error message. I tried importing a MSAccess db and it gave me the same error. following up Carlos Instruction, It worked both for msAccess db and Foxpro OleDB

Thanks

|||

Please read the KB article http://support.microsoft.com/kb/913817

The SSIS service does not have to run as the same account as SQL Server instance. What helped you is that you've changed it to run as account with Administrator rights. While this does fixes the problem, it exposes you to unnecessary risks as you are granting the service more priviledges than it really needs.

It is better to fix the real cause as described in the article.

|||

It is working now!!!

THanks

|||Bingo! That did it. Thanks!