Can I replicate from Postgres to MS SQL?

Question

I will have a Postgres database in production but want to use MS SQL (whatever edition) for reporting. So, I would like to have replication set up where MS SQL subscribes from postgres. Is this possible?

Answer 1

All heterogeneous replication scenarios are deprecated by Microsoft, and they now recommend building solutions using SSIS and CDC instead.

We load data from PostgreSQL into our SQL Server reporting database using SSIS and it works well, although we had to use a commercial OLE DB provider because of limitations (at that time) in the open-source one.

Actually copying the data is usually the easy part; most of the work comes in gathering requirements, understanding the data, transforming it, implementing logging and error handling etc. SSIS can do some things for you right away (e.g. logging) but my general advice would be to use it primarily as a workflow tool and for simple data copying with minimal transformation logic (e.g. data type conversion). If something seems seems too difficult or clumsy in SSIS then you can put it into a stored procedure or script and call that from SSIS instead.