SSIS Data Source Shows String Column as DT_WSTR When it is Single Byte in Oracle Database
<p>I'm developing an SSIS package to extract data from a table in an Oracle 11g database and load it to a table in a SQL Server 2014 database.</p>

<p>The table in the Oracle database has string columns defined as VARCHAR2 (single byte / codepage 8859-1);  the table in the SQL Server database has string columns defined as VARCHAR (single byte).</p>

<p>However, when I add an OLE DB source / ADO .NET source to the SSIS package and configure it to select columns from the table in the Oracle source, the External Columns (Advanced Editor) shows the string columns as DT_WSTR (Unicode).</p>

<p>This <a href="https://docs.oracle.com/cd/E11882_01/win.112/e17726/using.htm#OLEDB210" rel="nofollow">Oracle article</a> (see section 'How Oracle Unicode Support Works') advises that "When the client character set is not a superset of the server character set or the database character set is a multibyte character set, OraOLEDB automatically enables the Unicode mode."</p>

<p>How do I get the OraOLEDB provider / SSIS OLE DB data source to expose the string columns in the source as single byte?</p>

<h1>What I've tried</h1>

<ul>
<li>Setting the AlwaysUseDefaultCodePage property (of the OLE DB Source) to true and setting the DefaultCodePage property to 1252 (8859-1 is a sub set of this).</li>
<li>Setting the AlwaysUseDefaultCodePage property (of the OLE DB Source) to true and setting the DefaultCodePage property to 28591 (the <a href="https://msdn.microsoft.com/en-gb/library/windows/desktop/dd317756%28v=vs.85%29.aspx?f=255&amp;MSPPError=-2147217396" rel="nofollow" title="Windows equivalent">Windows equivalent</a> of 8859-1).</li>
<li>Reinstalled the Oracle client.</li>
</ul>

<h1>Constraints</h1>

<ul>
<li>I'm transferring 2M+ rows and the duration the package takes to execute is an issue.  Ideally, I don't want to have to add a Unicode > Non-Unicode Data Conversion transformation to the data flow that could negatively impact performance.</li>
<li>I have no control of the Oracle server / database configuration.</li>
</ul>

<h1>Environment Configurations</h1>

<h2>Windows Client</h2>

<ul>
<li>Windows 7 Pro</li>
<li>BIDS / Visual Studio 2013</li>
<li>Oracle client 11g Revision 5 (32 bit only - 64 bit client not installed)</li>
<li>Oracle NLS_LANG = ENGLISH_UNITED KINGDOM.WE8MSWIN1252 (Set in the Wow6432Node registry - no overriding environment variable)</li>
</ul>

<h2>SSIS Package / Data Flow Task / OLE DB Data Source</h2>

<p>LocaleID = English (United Kingdom)</p>

<h2>Client Session Connection Info</h2>

<pre><code>SELECT * FROM V$SESSION_CONNECT_INFO
</code></pre>

<ul>
<li>CLIENT_CHARSET = UTF8 (When query called from SSIS using Full Instant Client)</li>
<li>CLIENT_CHARSET = Unknown (When query called from SQL Developer using jdbcthin)</li>
</ul>

<h2>Oracle Server</h2>

<pre><code>SELECT * FROM SYS.NLS_DATABASE_PARAMETERS
</code></pre>

<ul>
<li>NLS_LANGUAGE = ENGLISH</li>
<li>NLS_TERRITORY = UNITED KINGDOM</li>
<li>NLS_CHARACTERSET = WE8ISO8859P1</li>
</ul>