SSIS Error code DTS_E_OLEDBError 0X8004005 ...

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.

Solution:
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

Prerequisite:
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.

Migration from VS 2005 to VS 2008

I recently got a opportunity to migrate a VS2005 project to VS2008. I was reading all the pros and cons of migration from the different sites. The simple thing to migrate the solution is open the 2005 solution from the VS 2008 IDE, it opens a wizard and take you through each step and convert the whole project. After migration it creates a Migration report which tell the status of migration. 99% of the project gets migrated in one click. Some of the solution will have a problem. If you get a chance to compare the sln file before and after migration the only change you can find is mentioned below:

VS2005 sln file: Microsoft Visual Studio Solution File, Format Version 9.00
VS2008 sln file: Microsoft Visual Studio Solution File, Format Version 10.00

The project files (*.vbproj or *.csproj) will not be affected or modified during the migration.

One more way to do this is create a Blank solution in VS2008 and start adding vs2005 projects to it. If we have multiple projects, then it will be little tedious to add each and every project.

We have a constraint here. If you solution has SSRS or SSIS projects associated to that then it will not get migrated on its own. We need to migrate it separately. While doing this we should make sure the SQL versions is also upgrated to SQL Server 2008. It is simple in development machine, but while deploying if the environment does not support then we will be in trouble. But for Window and Web projects, we will not find any problem until we change the framework. VS2008 supports multi-targeting, I mean it supports to compile and run your project in different framework starting from Net Framework 2.0.

One more funniest thing is we can have two sln files for same project to work in two different VS versions. This will not affect the project until you do not change the target framework. This is not a best practice, but helps in critical situations.

I referred the below link to know more information before migrating the project. This is really worth reading even for knowledge sake.

Business Intelligence Development Studio 2005 (BIDS)

I recently want to update visual studio 2005 with service pack. The moment i installed in my machine, my studio started behaving very funny. IDE is having repeated menu items with repeated sub menu. I tried repair the studio with reinstalling the service pack and the problem is still more worse. Menus got doubled than before. I tried setting the IDE using Import and Export setting and that does not help. All I need is BIDS and not full version of VS2005 as my projects are migrated to higher versions. So I uninstall the VS2005 from my machine. I uninstall Visual Studio Premier Partner Edition also (by mistake), Please do not uninstall that as it is required for BIDS.

I have SQL Server 2005 database services installed already in my machine, and I downloaded the latest version of service pack for Microsoft SQL Server 2005 (Service Pack 4 RTM) and installed in my machine. During the installation wizard there is a option to select the tool which you want to upgrade, select all and proceed.

Installation went well with one fix failed, and it opened the provisioning tool to set the access for my SQL server database engine. Once the installation is done, IDE started configuring on its own and it ready. I got this information in one of the site mentioned below. But still I want to post the full history to understand the history behind this.

Info I found in that site:

You should make sure that Visual Studio is still installed. If you didn’t previously have VS installed, the BI Dev Studio installation will install a VS shell called Visual Studio Premier Partner Edition. Look in Add or Remove Programs for an entry like this. If you don’t find any entry for Visual Studio go to the location for SQL Server setup and run .\Tools\Setup\vs_setup.exe. This will install the VS Shell. After this is installed repair the BI Studio installation by running the following from the command line from the .\Tools directory: start /wait setup.exe /qb REINSTALL=SQL_WarehouseDevWorkbench REINSTALLMODE=OMUS