SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
sometimes we get as "Login failed for user" or "User might not have permission to do the activity".
I was getting this error for quite sometime when I execute the package using "Execute Package utility" I tried looking this msdn site for , but that does not help. Even though i changed the settings and try to execute it is prompting the same error.
Reason: At last i got the solution from this msdn blog (which also contains solution for different kinds of SSIS errors). While setting the DB connection, I check the save password check box to avoid entering multiple times. That is reason for it. Normally if we have to set the password permanently for the connection then we are suppose to set the password for package also. And also the package encryption level must be set to "EncryptSensitiveWithPassword" or "EncryptAllWithPassword". This both go in pair. If you set the encryption level and not setting the package password it will throw an error.
1. While setting the connection details, we should not check the Save Password option and package encryption can be default. As a best practice, we can set the package configurations through config file.
Reference for package configurations in msdn site
1. Make sure you have the latest version of BIDS.
2. Update the SQL server with all available service packs.
3. While setting the connection string using config file, please make sure you are setting the connection provider also.