Re: moving data to from ms sql server via SSIS

On 08/15/2018 11:03 AM, cosmin ioan wrote:
> hi folks,
> we're trying to phase out MS SQL Server and we're trying to understand
> the easiest way to move data from MS SQL Server to postgres, via SSIS,
> say via ODBC, OleDB or .Net drivers, and basically create new tables &
> move data, into the target postgres db.  What drivers would be the
> best/most flexible?
>
> thanks much for any info or white papers on the topic,

A *lot* of this is: it depends

However, a pretty simple way is to use either FDWs from PostgreSQL or
linked tables in MSSQL. It would be easy enough for example to have
table ms.foo which has data and then table postgres.foo that is empty.
If ms.foo is linked (or and FDW table) you can use standard SQL to do
things like INSERT INTO (SELECT * FROM).

Of course if you can take the outage and you prep PostgreSQL
appropriately you could also just dump out to csv or tab delimited files
and load them with COPY.

JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
*** A fault and talent of mine is to tell it exactly how it is. ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
***** Unless otherwise stated, opinions are my own. *****