how to convert Oracle geometry to SQL GEOMETRY

Question

Ihave some geometry datatype entries in one of my table in a Oracle database.I would need to import those data into a sql server table to then use them for something else. I am aware that I cannot do that with an ssis package.There might also be some conversion problems.Could you point me in the right direction for how to do this correctly?

Thanks.

Answer 1

I'd bet dollars to donuts that you can use SSIS for this, with a little coercion. Specifically, well-known text (WKT) is the standard representation for geospatial data. On the Oracle side, you can use Get_WKT() against your data to return the WKT. Then on the SQL side, you can use STGeomFromText() and use the results of it to insert it. Use SSIS to stitch it all together and you've got it.