I recently started a job where I work with some Oracle databases in addition to SQL Server. We use the IBM Cast Iron tool to move data in between Oracle, SQL Server and Salesforce. Recently, I tried to import data from Oracle into SQL Server using the Import/Export Wizard, and ran into several difficulties.
I’m using SQL Server 2012 Developer edition and Oracle 11g.
From the wizard, I select ‘Oracle Provider For OLE DB’ as the Data Source, and then click on ‘Properties’ to specify the connection information.
So here was the first hurdle. To connect to Oracle, you need to supply a hostname, port number and either a service name or a SID (System Identifier, to uniquely identify a database), in addition to a username and password. The Data Link Properties dialog allows input for a username and password, but only one entry for the Data Source.
A co-worker is able to tell me that I needed to create a file to hold the connection information for each Oracle instance that I want to use. I needed to create a ‘tnsnames.ora’ file in this location: C:\oracle\product\11.2.0\client_32\network\admin (Obviously this is specific to the version of Oracle that I’m using). Each record followed this format:
ORA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = test.server.com)(Port = 1521))
)
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = TestService)
)
)
where ORA is an alias for the data source. I used a service name to connect. For the SERVER option, we can use either DEDICATED or SHARED, depending on what sort of connection that we want. So now I can use ‘ORA’ for the Data Source entry in the Data Link dialog. I also found that I needed to select the ‘Allow Saving Password’ option to move on, otherwise I get a ‘ORA-01005: Null password given; logon denied’ error. Once I supply my username and password, I’m able to connect.
In this case, I choose to copy the entire contents of a table. I select the table and click ‘Edit Mappings’ to see what my destination table will look like. The types seem to match up until I get to a column with the float datatype in the Oracle table. For it, the type comes up as ‘. I notice that the Source column shows 5(15) as the data type, which looks awfully strange. I select ‘float’ for the destination column and move on.
However, when I get to the ‘Review Data Type Mapping’ screen, I’m informed that there is an unknown column type conversion, and that I’ll only be able to save the package, not execute it.
After a lot of searching, I found a Microsoft post that describes this issue.
I need to add entries to some mapping files so that the wizard will know to map the Oracle float to the SQL Server float. Seems like the wizard would already know this, although after a little research it turns out Oracle allows precision to 126 digits, while SQL Server allows up to 53 digits, so I’m guessing that this difference contributes to the issue.
For my version, I find the SQL Server mapping files at:
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\MappingFiles\
After some experimenting, it turns out that I need to edit two files:
For OracleToMSSql10.xml, I map the ‘5’ data type to a float:

<!-- Custom entry for float -->
<dtm:DataTypeMapping >
	<dtm:SourceDataType>
		<dtm:DataTypeName>5</dtm:DataTypeName>
	</dtm:SourceDataType>
	<dtm:DestinationDataType>
		<dtm:SimpleType>
			<dtm:DataTypeName>float</dtm:DataTypeName>
		</dtm:SimpleType>
	</dtm:DestinationDataType>
</dtm:DataTypeMapping>

After making this mapping, I still have the same issue. I can double-click the entry in the Data Type Mapping grid and see the Source and Destination information. I see the Source is mapped from 5 to float, but under the Destination Information section, I see that SQL Server float is mapped to a double-precision float type in SSIS. I do see a OracleToSSIS10.xml mapping file, so I try editing that file as well.
For the OracleToSSIS10.xml file, I copy the float entry from the MSSQLToSSIS10.xml file.

<!-- Custom entry for float -->
	<dtm:DataTypeMapping >
		<dtm:SourceDataType>
			<dtm:DataTypeName>5</dtm:DataTypeName>
		</dtm:SourceDataType>
		<dtm:DestinationDataType>
			<dtm:CharacterStringType>
				<dtm:DataTypeName>DT_R8</dtm:DataTypeName>
				<dtm:Length>24</dtm:Length>
			</dtm:CharacterStringType>
		</dtm:DestinationDataType>
	</dtm:DataTypeMapping>

So I run through the process again (I don’t need to restart SSMS) and this time when I go to ‘Edit Mappings’ I see that the destination column that caused issues before is now mapped to the float type. I’m also able to execute the package and import my data.
So out of curiosity, I try a 2nd import, only I want to try writing a query instead of importing the entire table. I try a query using select * and just want the first 5 records:
select * from table where rownum < 6;
So this time when I check out the column mappings, all of the string columns (varchar2 in Oracle) comes up as in the Column Mappings. These records come up as data type 202. This time, I go back to the query and specify only certain columns instead of using select *. I select 5 columns, all the varchar2 type. Again, these come up as . This time, I go through the column mappings and set each one as a varchar, with the wizard setting each one to Max as a default. Interestingly, even if I set the string size to a different value, the wizard resets everything to Max. I’m guessing it sees this as safer, since it can’t interpret the length of each source column. Again, I’m not able to execute the package until I add mappings for source type 202 to a varchar.
I assumed it would be straightforward to import some data from Oracle to SQL Server, but it turned out to be anything but that. I found it interesting that I got different issues depending on how I specified to retrieve the data. Once I got these mappings set up, I was able to import everything I needed.