After my recent post on where to find the 32-bit ODBC Administrator on a 64-bit SQL Server, a new question was asked about how to get SSIS to run with the 32-bit ODBC instead of the 64-bit ODBC. You need to make a simple configuration change to the properties of your BIDS solution. Here I have a solution called 32bitODBC and it needs to run in 32-bit mode, not 64-bit mode. Since I have a 64-bit SQL Server, BIDS defaults to using the 64-bit runtime. To override this setting, go to the property pages for the solution. Select the Debugging node. Select Run64BitRuntime and set it to False. Save your changes.
What about when you finish your work in BIDS and you want to use the 32-bit runtime outside of BIDS? It depends on where you execute your package from.
If you double-click a dtsx file from Windows Explorer, it is executed by the SQL Server 2008 Integration Services Package Execution Utility. The default fully qualified path for that tool is C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe. In other words, it is a 32-bit product, so it doesn’t have any trouble running things in 32-bit mode by definition.
Here’s part of the error message generated from executing the package from a SQL Server Agent job using the default settings:
Description: System.Data.Odbc.OdbcException: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
The clue is the part about the architecture mismatch. Using the 64-bit runtime is the default behavior. Fortunately this is easy to change within the job definition by checking the Use 32 bit runtime checkbox.
No comments:
Post a Comment