Resolve Flat File Source transform read issue with EBCIDC characters in Sagent

Product Feature: Flat file source transform



On reading input file from Flat file source, the user is getting error "Error in source record xx, field 'RZBK': Unable to Convert to Requested Type."


The input file is of type Binary format and with EBCIDC character format. On execution of the plan 5-6 times, the plan gives the error.
The issue is reproducible locally.


UPDATED: December 5, 2017
Here is the recommendations from Microsoft:
1. The current issue of access violation in oleaut32!VariantClear api is due to a known issue of detouring conflicts with Microsoft’s MSO dll component which gets invoked by ACE (in this case Microsoft Access Text Driver ACEODBC.DLL which is being used to create the DSN NBA-Lookup).

The flow is like this:
Sagent-> odbc32 -> aceodbc -> mso

Sagent calls into odbc apis to access the DSN NBA-Lookup. Microsoft odbc.dll internally calls into aceodbc.dll which in turn calls into the mso.dll. Assembly instructions of oleaut32!VariantClear are overwritten because of detouring from MSO.DLL, but MSO.DLL should be undoing its detour of the function, but it doesn't happen correctly. Hence the code segment corruption.

If this issue happens in any other code segment then there will be corruption there also. Hence, there cannot be a generic fix for handling this access violation in the victim process (Sagent Data Flow Service process in this case) because it can affect any other code segment as well.

2. Microsoft has suggested to use SQL (like saving the data in MS SQL ) and avoid using Microsoft Access Text Driver ACEODBC.DLL to create the DSN. However, the alternative to the Microsoft Access Text Driver ACEODBC.DLL is the Microsoft Text Driver which is deprecated and not supported by Microsoft anymore and only available as 32-bit driver currently. Hence the only viable alternative in this case is to use SQL to avoid this issue.