Irrespective of whether your Microsoft SQL Server instance is 64-bit, the Visual Studio IDE is a 32-bit application and as a result all OLE DB and .Net Providers must be 32-bit, along with any needed components such as ODBC drivers.

This means that when you are developing and debugging SSIS, SSAS, & SSRS applications you will require a 32-bit Provider and if applicable a 32-bit ODBC driver. When run your final SSIS,  package,  if it is deployed against a 64-bit Microsoft SQL Server instance, it will require a 64-bit Provider and if applicable a 64-bit ODBC driver. The same is true when you deploy your SSRS reports to a SharePoint x64 Integrated environment…

Therefore, when creating applications on for a 64-bit instance with Visual Studio  and 64-bit version of Microsoft SQL Server you will require both 32-bit and 64-bit components to allow ease of use in taking advantage of the available features.

Note Ensure your DSNs are identical in DEV and PROD accept for the version of the Driver used (32-bit DEV, 64-bit PROD)

It was worse with Visual Studio 2005 on a 32-bit OS, the 64-bit compilers were not installed by default, you will need to go to custom setup and check the option. My question is when will Visual Studio be a 64-Bit Application???

Visual Studio 2010 is a 32-Bit Application..

Cheers,

-Ivan