ODP.NET support for SQL Server Import/Export Wizard (SSIS)
I'm attempting to transfer data from an Oracle 11g server to SQL Server 2008 R2 using the Import/Export Wizard (which uses the same moving parts as SSIS).
Symptoms:
- When I use the "Oracle Data Provider for .Net" (Oracle.DataAccess) driver as the data source, the wizard does not enumerate the list of source tables available in the schema. Using a custom query (the only option available) fails since there is no data type mapping XML available for this provider.
- When I use the ".Net Data Provider for Oracle" (System.Data.Oracle) driver as the data source, I am able to get a full list of tables and the data types map correctly as well
Diganosis (so far):
- Microsoft's help indicates that the ODP.Net provider must add their ProviderDescriptor node to ProviderDescriptors.XML in Program Files\Microsoft SQL Server\100\DTS\ProviderDescriptors\ProviderDescriptors.XML
- In addition the ODP.Net install must also have added the necessary data type mapping XMLs
Resolution:
- How do I obtain the correct ProviderDescriptor an data type mapping XML files for ODP.Net
Thank you
Lawrence