by text unfortunately. Related with providers. Current providers doesnt not know spatial data. Here is a section from "Guide to Migrating from Oracle to SQL Server 2008" SQL Server technical article. Oracle Spatial is an Oracle subsystem which provides SQL functions to facilitate the handling of spatial features in an Oracle database. The geometric description of a spatial object is stored in a single row, in a column of dedicated object type MDSYS.SDO_GEOMETRY. SQL Server 2008 also supports spatial data. They are implemented as SQL CLR types named geography and geometry. The geography type allows you to store objects defined by coordinates on Earth's surface, and the geometry type is used for planar objects. SQL Server 2008 spatial data types implement methods for importing and exporting data in Well Known Text (WKT) and Well Known Binary (WKB) formats that are defined by Open Geospatial Consortium (OGC) specification. Spatial functionality is supported in all editions of SQL Server 2008, including Express. SSMA for Oracle V4.0 does not support migration of table columns that have SDO_GEOMETRY type. Straightforward use of SQL Server Integration Services (SSIS) does not help much, because the Oracle Spatial types are not recognized by existing OLE DB, ADO.NET or ODBC providers. The proposed solution is based on the fact that both Oracle Spatial and SQL Server 2008 support conversion to WKT format. Next, we are assuming that the source SDO_GEOMETRY column is mapped to SQL Server column of the geography type. Before transferring the data, we should create a SQL Server linked server pointing at the source Oracle instance. To perform the migration, we need to convert the source column value into WKT format, which makes it a plain text, and insert the result into the target geography column using OPENQUERY statement. Example: Suppose we have an Oracle table defined as: CREATE TABLE geoinfo (id NUMBER(10) NOT NULL, geo MDSYS.SDO_GEOMETRY); Its SQL Server counterpart will be: CREATE TABLE geoinfo (id NUMERIC(10) NOT NULL, geo geography); In this case, the following INSERT statement will correctly copy the spatial data. INSERT INTO geoinfo (id, geo) SELECT id, geography::STGeomFromText(CAST(geo as nvarchar(max)), srid) FROM OPENQUERY(ORACLE_LS, SELECT id, SDO_UTIL.TO_WKTGEOMETRY(g.geo) geo, g.geo.sdo_srid srid FROM geoinfo g) Here ORACLE_LS is the name of linked server referencing the source Oracle instance. The Oracle function TO_WKTGEOMETRY returns a Well Known Text representation of the Spatial geometry object. The spatial reference ID (srid) is necessary to define the way the WKT string is interpreted by SQL Server. Senior BI Consultant & PM @ Bicentrix If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

Hi All, how will I load geometry data type column from oracle database to SQL server using SSIS. Any help? Thanks, Anil