am trying to migrate PostGIS geometry to SQL server Spatial data. which ETL i have to use. it's the first time i'll work with SQL server Spatial. is there any Data ETL model that do this migration. thanks for help
You can try
Details click here
try to use program brigantine to migrate PostGIS geometry to SQL server Spatial data
Microsoft SQL Server (and other ODBC) to PostgreSQL
OGR has a driver that supports ODBC DSN connections. Unfortunately this will only work on Windows. Below is an example that imports all tables from a SQL Server database to PostgreSQL. In this example, the DSN I have is using NT Authentication or has the name and password encoded in the System DSN. For this you need to setup a DSN connection to the SQL Server database in ODBC Manager (we described setting up a DSN in Using MS Access with PostgreSQL, except the ODBC driver seems to only work with System DSNs not File DSNs. For more details on referencing the ODBC DSN - check http://www.gdal.org/ogr/drv_odbc.html
As noted, geometry fields seem to be created and right field types are not necessarily created. SQL Server text come in as VARCHARS and VARCHARS come in as CHARS etc. One way to get around this is to create your table structures before hand and use OGR2OGR to just load the data.
--This will append to a table called orders reading from the SQL Server (ODBC) table orders and customers. It will only append like-named fields.
Or correct the structures afterword by dropping unnecessary fields using a script generation technique similar to what we described in DML to Generate DDL and DCL. So your script builder SQL to drop all the ogc_fid and wkb_geometry fields created by OGR2OGR would look something like this.