It seems like a déjà vue, migrating Oracle data to Sql Server.
This time, some Oracle 10g tables (running on an Itanium server) to be migrated to Sql Server 2005 (temporary running on the same Itanium).
I thought, piece of cake, creating a linked server using "Oracle Provider for OLE DB" (the only provider currently available on the system) with the following statement:
EXEC master.dbo.sp_addlinkedserver @server = N'RX3600', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'Ora2Sql'
followed by creation of a credential to login in Oracle.
Remembering a previous post, we have to set the option "Allow inprocess" on the provider OraOLEDEB.Oracle.
And to finalize the statement: SELECT * INTO destinationdb.schema.tablename FROM RX3600..schema.tablename
The result is that all the rows are transfered (no indexes, nor foreign keys are needed). After analysing the content however, data is correctly copied ... for character-based data-types, unfortunately not for the Oracle decimal data(-type). Once arrived in Sql Server, the decimal columns where filled with 0 (zero) ! :-(
According to "Installation of Visual Studio 2005 on the Intel Itanium (IA64) is not supported", I did not find the Visual Studio Manager to create a full SSIS package. Therefor I used "\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe".
When running this wizard, I selected:
- as origin data source: ".Net Framework Data Provider for Oracle"
- as "query": "SELECT * FROM tablename"
- changed the definition of the decimal datatype with correct precision and scale.
I got the correct data.
For your info, the driver was allready installed. If you would need it, you find the provider here, probably the 32-bit version.
CU, pds