Tabular: Error while using ODBC data source for Importing Data

  • Article

Issue

When using ODBC as a Data Source in the Table Import Wizard to add data to a Tabular Server, we are getting the following  error; however, preview shows the data correctly:

Error Message:

OLE DB or ODBC error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application; IM014.

A connection could not be made to the data source with the DataSourceID of '7300a711-299c-4367-98eb-b2e01b7e4ba5', Name of 'Custom 4'.

An error occurred while processing the partition 'DimAccount 1_e8a5233f-406a-450d-8b2e-8bf86a321d2f' in table 'DimAccount 1_e8a5233f-406a-450d-8b2e-8bf86a321d2f'.

The current operation was cancelled because another operation in the transaction failed.

Environment

SSAS Tabular Server 2012 x64

Assessment

The problem occurred because SQL Server Data Tools is 32-bit and the DSN that we used initially to import data (in Data Link Properties) is 32-bit ODBC, but the server is 64-bit.

Therefore you are able to use Test Connection successfully and are even able to select tables. However, when you try to import data, an error occurs.

Because the server build is x64, the Tabular Engine will look for a 64-bit  DSN when attempting to import data, and in the absence of a 64-bit DSN, import will fail with the following error:

clip_image002

Solution:

The Preview feature in SSDT uses a 32-bit ODBC DSN, so you can view the data but cannot import data.

To fix this issue, you need to configure two ODBC DSNs, one 32-bit and one 64-bit, and give them the same name.

How to Create Matching 32-bit and 64-bit ODBC DSNs:

A 64-bit version of the Microsoft Windows operating system includes the following versions of the Microsoft Open Database Connectivity (ODBC) Data Source Administrator tool (Odbcad32.exe):

o The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.

o The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

Refer this link for full reference https://support.microsoft.com/kb/942976