How to:
Connect to an ODBC Source on a x64 Machine

The Problem: 

Business Intelligence Development Studio (BIDS) always runs in 32 bit mode, even on 64 bit OS. When creating a SSIS package in design time in BIDS the application connects to ODBC data sources using the 32 bit version of ODBC. However when the package is run in 64 bit mode in DTExec.exe then the package tries to connect to the ODBC data source through the 64 bit ODBC and the connection fails because the data source does not exist in the 64 bit ODBC.

Solution 1: Run in 32 bit mode

The easiest solution is to always run the package in 32 bit mode.

Debugging in 32 bit mode: To debug the package in 32 bit mode on a 64 bit OS, open the project properties (right click on the project name in the Solution Explorer in BIDS and choose Properties) and in the Debugging section of the Properties dialog set the Run64BitRuntime property to False.

Executing packages in 32 bit mode: To execute a package in 32 bit mode on a 64 bit OS then you need to execute the package with the 32 bit version of the DTExec utility. You will typically find the 32 bit version in the following folder: C:\Program Files (x86)\Microsoft SQL Server\90\DTS

Solution 2: Use two identically named ODBC data sources in the two ODBC versions

Sometimes you need your package to run in 64 bit mode. To ensure that both BIDS (which always runs in 32 bit mode) and the 64 bit DTExec (which always runs in 64 bit mode) have access to the same ODBC data source, you can define the data source in both versions of ODBC, using the same name and connection properties for the two data source.

1. To define an ODBC data source in the 32 bit ODBC use the user interface of BIDS to create a new ODBC connection manager and to build a new connection string. That user interface uses the 32 bit ODBC and will store the data source in the 32 bit version of ODBC.

2. To define an ODBC data source in the 64 bit ODBC use the Administrative Tools/Data Sources (ODBC) item in ControlPanel to define a data source with identical properties and name as the data source you defined in BIDS.

Now when BIDS tries to connect to the data source in design time it uses the data source in the 32 bit ODBC, and when the 64 bit DTExec connects to ODBC it uses the data source defined in the 64 bit ODBC.

More Information:

For more information please see the following article: Connectivity and SQL Server 2005 Integration Services