SSIS can't find 32-bit ODBC Driver in Debug mode

Question

I am on a 64-bit Windows 2008 server. In SSIS I have an ADO.NET Data Flow Item connected to an ODBC connection. Preview works fine. When I execute in DEBUG mode the following exception is thrown:

[ADO NET Source [53]] Error: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)

The best I can find is that SSIS can't find the Data Source because it is looking in the 64 location, which is different than the 32 bit one. Is there a way to override this default to point it to the 32 bit ODBC?

Answer 1

The problem is solved by setting Run64BitRuntime to false in the Project Debugging Options. This causes an error:

[ADO NET Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "ADO NET Source" (1)" failed because error code 0x80131937 occurred, and the error row disposition on "output column "Z_ID" (38)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Which is a defect between the 3rd party ODBC driver and .NET. This is resolved by following the steps here: http://blogs.msdn.com/sqlblog/archive/2009/04/09/after-installation-of-net-framework-3-5-sp1-or-net-framework-2-0-sp2-ssis-packages-using-odbc-3rd-party-drivers-may-fail.aspx

Answer 2

Install the 64 bit version mysql-connector-odbc-5.1.6-winx64.msi 32 bit clients (XP/Vista 32 bit) use ODBC 3.51 64 bit clinets (Vista 64 bit) use ODBC 5.1

conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};" + "SERVER=nnn.nnn.nn.nnn;" + "DATABASE=my_sql_database;" + "UID=my_uid;" + "PASSWORD=my_pwd;";

conn.ConnectionString = "DSN=my_odbc_dsn";