In this post we will cover how to read/write PotgreSQL Geometry Data Type in SSIS (e.g. Point, Line, Polygon). We will use SSIS PostgreSQL Source and SSIS Upsert Destination provided by ZappySys.
Requirements
How to read PotgreSQL in SSIS (Geometry Type Example)
Lets look at how to read data from PostgreSQL (Table mode or Query mode)
- Double click on the Data Flow task to see Data Flow designer surface.
- Here, In Visual Studio, drag and drop the PostgreSQL Source in the design surface.
- Double-click to configure, Click New connection, and setup that
- On Source UI, change to Query mode and type SQL as shown below. Notice we have converted Geometry column into WKT (Well-Known Text) using ST_AsText function. This will allow the SSIS engine to output data as a string rather than a binary type. Without this, you will get an error.
SELECT id,name,ST_AsText(geom) as geom from "public"."geometries"
How to Write PostgreSQL data in SSIS (INSERT only)
If you have usecase for INSERT into Target PostgreSQL table then you can use ZappySys PostgreSQL Destination.
Map Text column (WKT format) to Geometry Column and it will work fine.
- Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
- Double click on the Data Flow task to see Data Flow designer surface.
- Here, In Visual Studio, drag and drop the DummyData Source and ZS PostgreSQL Destination in the design surface and join the components with the blue arrow.
How to Upsert Geometry Type in SSIS (INSERT or UPDATE or DELETE)
Now lets look at how to Upsert data into PostgreSQL Table with Geometry Type. If you try to write data into the Geometry column, it may fail unless you use the staging setup as below (use TEXT type).
To write data to target table with Geometry Type you have to prepare a staging table with the same structure as target but use Text type rather than Geometry.
Create a Stage Table to handle Geometry Type
Here is how to create a staging table with the same structure as the target.
Notice we used LIMIT 0 to create an empty structure.
select * into geometries_stage from geometries limit 0
Confirm Stage Table is created.
Configure SSIS Upsert Destination
Here is how you can configure Upsert Destination.
Conclusion
In this post we saw how easy it is to read/write PostgreSQL data with advanced types like Geometry. You can Download SSIS PowerPack from here and try these steps yourself.