Thanks: First, a big thanks to Jim Benton who did the research to find the solution discussed here.

Overview:  An update to Oracle connection string, when retrieving data using SSIS, to avoid missing records when there’s a constraint on a date field.

Level:  Familiarity with the Oracle data providers in SSIS is presumed.

The Problem – aka What?  Where’s All The Data?

Recently we were working on an SSIS package.  Its source data was a view in Oracle (9.x).  The source query within SSIS wasn’t complex, but there was a constraint on the Last Update Date in Oracle.  Hold that thought.

As usual, I worked on the source query in SQL Developer first, to become acquainted with the data & finalize the query before it went into SSIS.  Let’s say SQL Developer returned 400 rows of data.  I put the source query into SSIS, with the standard Oracle OLE DB connector we use for dozens of packages, and ran it.  It succeeded all right, but only returned 250 rows of data.  After several Oracle developers looked into the view itself, they deemed there was nothing unusual about it.

At this point I admit my brain went through the stages:  denial, a smidgen of anger, and finally acceptance that we have a problem to solve.  Before we jump to the solution, let’s chat a bit about the OLE DB Oracle connectors…

The Two OLE DB Options for Connecting to Oracle

When you create a new connection in SQL Server Integration Services, you’ll see two Oracle options in the standard list.   The one we use is the Oracle Provider for OLE DB because it’s 64-bit and fully supported.

    

The connection string for Oracle Provider for OLE DB looks like this: 

Data Source=DataSourceNameHere;User ID=UserIDHere;Password=PasswordHere;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;

The other option in the list is the Microsoft OLE DB Provider for Oracle, which is deprecated and only works in 32-bit mode.  The connection string for it would be like this:

Data Source=DataSourceNameHere;User ID=UserIDHere;Password=PasswordHere;Provider=MSDAORA.1;

Just  quick sidenote – since the Microsoft provider is 32-bit, if you want to run it inside of BIDS, the “Run64BitRuntime” property in the SSIS Project Properties needs to be updated to False.

We ended up trying the Microsoft provider & it returned all 400 rows we were looking for.  That’s great.  It actually worked for a short while as an interim solution.  However, the problem with saying that we’d just use the Microsoft provider for this one package is that it’s only 32-bit.  Every other package in the entire environment was 64-bit including the Masterload, so running this troublesome package via the normal Masterload & via the normal SQL Server Agent job wasn’t going to happen.  Thankfully we found a final resolution…

The Solution – Specifying “UseSessionFormat” in Connection String

The final resolution turned out to be specifying one additional attribute in the connection string called “UseSessionFormat.”  The connection string for the connection now looks like this:

Data Source=DataSourceNameHere;User ID=UserIDHere;Password=PasswordHere;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;UseSessionFormat=True;

The UseSessionFormat=True allows OraOLEDB to override the default NLS (National Language Support) session format for the duration of the session.  The default value for this attribute is False.  The NLS settings affect not only languages, but dates & times as well.  If you recall, the source query had a constraint on the Last Update Date.  So there was something a little different about the Last Update Date in the source view which caused SQL to to change its behavior.

Finally, worth noting is we didn’t want to affect the dozens of other packages already in the same environment.  So for this solution we set up a second connection string entry in the configurations table – just to be on the safe side.

Finding More Information

MSDN Data Access Technologies Blog:  Every Bug is a Microsoft bug until proven otherwise

Technet Article:  SSIS with Oracle Connectors

Oracle Developer’s Guide:  What’s New in Oracle Provider for OLE DB?

Oracle National Language Support Guide:  Understanding Oracle NLS