I'm trying to use SSIS to transfer point data from a Geography column in SQL Server 2008 R2 to a geometry column in a Greenplum (Postgres 8.2.15) database with PostGIS. I know there's a way to do the conversion outside of SSIS because I did it once a while back, unfortunately I don't remember what I did.
I figured it out. I used a query for the SQL Server source that converts to POINT text using:
That was all it took. Now whether the point will be represented in PostGIS the way I intend it to I can't say at this point, but I'm hoping so.