Using 64-bit ODBC Driver

Question

I need to pull data from a 64-bit Progress database using a 64-bit ODBC driver (at this time it seems there is no 32-bit driver which can connect to this DB). I have created a 64-bit ODBC DSN (user dsn) pointing to this database and it tests successfully. But when I create a data source in BIDS pointing to this ODBC DSN, get the following error:

Test connection failed because of an error initializing the provider. ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

I am using SSIS 2005 and "Run64BitRuntime" property for the project is 'True'.

Any insight into how to successfully use 64-bit ODBC driver with BIDS/SSIS will be greatly appreciated.

Thanks,
Manish

Answer 1

BIDS is 32-bit.

from http://msdn.microsoft.com/en-us/library/ms140349%28v=sql.90%29.aspx:

Many of the providers that connection managers use are available in 32-bit and 64-bit versions. The Integration Services design environment is a 32-bit environment and you see only 32-bit providers while you are designing a package. Therefore, you can only configure a connection manager to use a specific 64-bit provider if the 32-bit version of the same provider is also installed.

Try to use delayed validation or change data source at run time using configurations...

Answer 2

Hello,

Follow the thread below. It might help you.

http://forums.asp.net/t/1688288.aspx/1?Strange+Behavior+with+ODBC+ERROR+IM014+Microsoft+ODBC+Driver+Manager+The+specified+DSN+contains+an+architecture+mismatch+between+the+Driver+and+Application

Answer 3

Hi,

we also migrated SSIS from 32 bit server to 64 bit server.

To run packages successfully we changed Run64BitRuntime property to False.

And we cannot run the jobs from SQL server agent using SSIS package store. for this you have to select job type as Operating System (CmdExec) and the command will be the following

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "G:\packagepath\packagename.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

Srikrishna

Answer 4

Business Intelligence Design Studio (BIDS) is a 32-bit application. When you're designing your package, you're using 32-bit facilities - and have no choice in the BIDS, matter When you execute your package using DTExec, you have the option of 32-bit or 64-bit operation - but the default on a 64-bit installation is to use 64-bit mode (obviously).  However, some commonly used objects in SSIS don't have 64-bit counterparts, and will therefore cause your packages to fail.

At run time, the correct version is used, and it does not matter that you specified the 32-bit version of the provider at design time. The 64-bit version of the provider can be run even if the package is run in Business Intelligence Development Studio.

Both versions of the provider have the same ID. To specify whether the Integration Services runtime uses an available 64-bit version of the provider, you set the Run64BitRuntime property of the Integration Services project. If the Run64BitRuntime property is set to true, the runtime finds and uses the 64-bit provider; if Run64BitRuntime is false, the runtime finds and uses the 32-bit provider. For more information about properties you can set on Integration Services projects, see Integration Services Considerations on 64-bit Computers and Integration Services in Business Intelligence Development Studio.

So in brief you have to select the 32 bit driver at run time and set the Run64BitRuntime to true which select the 64 bit version of driver at run time due to both 32 bit and 64 bit version have the same id.

thanks,

Zaim Raza.

Answer 5

It would be nice if it really worked that way ......

Answer 6

I'm not a big fan of linked servers, but you *could* create a linked server within SQL Server (which would use the 64-bit drivers as the OS will use them fine) and then read from SQL Server using that linked server in SSIS by pointing at the SQL Server and then using four-part naming - Server.Database.Schema.Object.

Kind of goes against the grain a little, but it provided me a solution as I only needed a one-off extraction.