SQL Server 2005 - OLEDB is not recognized as a valid connection manager type
I have had this error twice now with SQL 2005. Each time has been after I had to reload windows (laptop is getting flaky. . . ). I am basically posting this for my own future reference, but if anyone ever gets an error similar to the one below which indicates that OLEDB is not a valid connection manager type, there is a fix for it:
Error 1 Error loading ETL - MyPackage.dtsx: The connection type "OLEDB" specified for connection manager "My DB" 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.
It is all explained in this SQL 2005 forum post (scroll down and find the post made Dec 24 2005 at 3:58 am by Micahel Entin from the SSIS team). Essentially what happens is that some program other than SQL 05 changes permissions on registry keys that SQL depends on being able to access internally from a low-privelege account. Michael provides a script that can be compiled as a command line program in .NET that will identify the keys for which the permissions have changed so that you can change them back.