In a simplified way, the SSIS connects to the databases via two parts, the data provider models and the database drivers. The data provider models are .Net Provider, OLE DB Provider, ODBC, etc. The database drivers are Jet engine, Oracle driver, etc.
Since we mostly use OLE DB, I will only discuss one type of provider, OLE DB. Both Microsoft and Oracle offer data provider for OLE DB. Microsoft has 32 bit “Microsoft OLE DB for Oracle”. Oracle has both 32 bit and 64 bit “Oracle provider for OLE DB”. Both data providers use the same Oracle driver on the machine.
For database driver, there are only Oracle drivers. The only different is driver versions and OS versions. For example, our laptops are usually has 32 bit Oracle driver. The server usually has 64 bit Oracle driver.
Since Microsoft does not offer data provider for Oracle in 64 bit, the “Oracle provider for OLE DB” automatically become the only simple way to go.
For importing data into the Oracle, the most efficient way is still the Oracle SQL*Loader. But it requires writing data to a text file first. Another way is use Script destination to consume data in Oracle transitions.
Executing the SSIS package in 32 bit mode is another way to solve the problem.
There is an issue in using the “Oracle provider for OLE DB”. Oracle data type Number is a variable-length data type. It is similar to the Numeric data type in SQL Server but it saves space at the table. The SSIS failed to retrieve the correct precision and scale when the precision and scale of the Number fields are not specified in the Oracle database. (This is only a 2005 problem.)
In this case, it is not a good idea to use “Oracle provider for OLE DB” as data source in the SSIS “Data Flow”. So it is better to use the .Net Provider,“OracleClient Data Provider”, instead of OLE DB providers.
Refere to the “OLE DB VS ADO.NET” for more information.
Oracle data type Number
Just a reminder! The “Oracle provider for OLE DB” does not process the comment characters “–” correctly.