Thread: Best tool to pull from mssql
Hi .. we’re on a hunt to locate either pgloader or the tds_fdw extension to assist us with pulling data FROM a Microsoft sql server (we’re a windows-based postgres installation).
Is this the proper way to handle the process?
On 11/10/2015 10:21 AM, Mammarelli, Joanne T wrote: > Hi .. we’re on a hunt to locate either pgloader or the tds_fdw extension > to assist us with pulling data FROM a Microsoft sql server (we’re a > windows-based postgres installation). So are we taking about a one time operation or continuously syncing or something in between? > > Is this the proper way to handle the process? > Depends on the answer to the above. -- Adrian Klaver adrian.klaver@aklaver.com
Continuously syncing .. we have database in mssql. Ideally we'd like to connect to the mssql databases and pull out thefields/content we required and populate that in a postgres database. -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Tuesday, November 10, 2015 1:26 PM To: Mammarelli, Joanne T; pgsql-general@postgresql.org Subject: Re: [GENERAL] Best tool to pull from mssql On 11/10/2015 10:21 AM, Mammarelli, Joanne T wrote: > Hi .. we're on a hunt to locate either pgloader or the tds_fdw > extension to assist us with pulling data FROM a Microsoft sql server > (we're a windows-based postgres installation). So are we taking about a one time operation or continuously syncing or something in between? > > Is this the proper way to handle the process? > Depends on the answer to the above. -- Adrian Klaver adrian.klaver@aklaver.com
On 11/10/2015 10:26 AM, Mammarelli, Joanne T wrote: > Continuously syncing .. we have database in mssql. Ideally we'd like to connect to the mssql databases and pull out thefields/content we required and populate that in a postgres database. When all else fails you can go the ODBC route. I have done that through Access to create a linked Postgres table that I can populate from other data sources. Here are some articles that seem to be doing something similar using SQL Server and Postgres. In the article Postgres is installed on a Linux box, but that does matter for the ODBC usage. : https://www.mssqltips.com/sqlservertip/3661/creating-twoway-data-access-between-sql-server-and-postgresql--part-1/ https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql-linked-server-configuration--part-2/ > > > -----Original Message----- > From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] > Sent: Tuesday, November 10, 2015 1:26 PM > To: Mammarelli, Joanne T; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Best tool to pull from mssql > > On 11/10/2015 10:21 AM, Mammarelli, Joanne T wrote: >> Hi .. we're on a hunt to locate either pgloader or the tds_fdw >> extension to assist us with pulling data FROM a Microsoft sql server >> (we're a windows-based postgres installation). > > So are we taking about a one time operation or continuously syncing or something in between? > >> >> Is this the proper way to handle the process? >> > > Depends on the answer to the above. > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
Thanks - I've been dying trying to find the software (on the windows side) for part 3 .. https://www.mssqltips.com/sqlservertip/3663/sql-server-and-postgresql-foreign-data-wrapper-configuration--part-3/ -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Tuesday, November 10, 2015 1:49 PM To: Mammarelli, Joanne T; pgsql-general@postgresql.org Subject: Re: [GENERAL] Best tool to pull from mssql On 11/10/2015 10:26 AM, Mammarelli, Joanne T wrote: > Continuously syncing .. we have database in mssql. Ideally we'd like to connect to the mssql databases and pull out thefields/content we required and populate that in a postgres database. When all else fails you can go the ODBC route. I have done that through Access to create a linked Postgres table that I canpopulate from other data sources. Here are some articles that seem to be doing something similar using SQL Server and Postgres. In the article Postgres isinstalled on a Linux box, but that does matter for the ODBC usage. : https://www.mssqltips.com/sqlservertip/3661/creating-twoway-data-access-between-sql-server-and-postgresql--part-1/ https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql-linked-server-configuration--part-2/ > > > -----Original Message----- > From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] > Sent: Tuesday, November 10, 2015 1:26 PM > To: Mammarelli, Joanne T; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Best tool to pull from mssql > > On 11/10/2015 10:21 AM, Mammarelli, Joanne T wrote: >> Hi .. we're on a hunt to locate either pgloader or the tds_fdw >> extension to assist us with pulling data FROM a Microsoft sql server >> (we're a windows-based postgres installation). > > So are we taking about a one time operation or continuously syncing or something in between? > >> >> Is this the proper way to handle the process? >> > > Depends on the answer to the above. > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/10/2015 11:06 AM, Mammarelli, Joanne T wrote: > Thanks - I've been dying trying to find the software (on the windows side) for part 3 .. > > https://www.mssqltips.com/sqlservertip/3663/sql-server-and-postgresql-foreign-data-wrapper-configuration--part-3/ Another option would be to install the Postgres server in a Linux VM on your Windows machine. > > -----Original Message----- > From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] > Sent: Tuesday, November 10, 2015 1:49 PM > To: Mammarelli, Joanne T; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Best tool to pull from mssql > > On 11/10/2015 10:26 AM, Mammarelli, Joanne T wrote: >> Continuously syncing .. we have database in mssql. Ideally we'd like to connect to the mssql databases and pull outthe fields/content we required and populate that in a postgres database. > > When all else fails you can go the ODBC route. I have done that through Access to create a linked Postgres table that Ican populate from other data sources. > > Here are some articles that seem to be doing something similar using SQL Server and Postgres. In the article Postgres isinstalled on a Linux box, but that does matter for the ODBC usage. : > > https://www.mssqltips.com/sqlservertip/3661/creating-twoway-data-access-between-sql-server-and-postgresql--part-1/ > > https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql-linked-server-configuration--part-2/ >> >> >> -----Original Message----- >> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] >> Sent: Tuesday, November 10, 2015 1:26 PM >> To: Mammarelli, Joanne T; pgsql-general@postgresql.org >> Subject: Re: [GENERAL] Best tool to pull from mssql >> >> On 11/10/2015 10:21 AM, Mammarelli, Joanne T wrote: >>> Hi .. we're on a hunt to locate either pgloader or the tds_fdw >>> extension to assist us with pulling data FROM a Microsoft sql server >>> (we're a windows-based postgres installation). >> >> So are we taking about a one time operation or continuously syncing or something in between? >> >>> >>> Is this the proper way to handle the process? >>> >> >> Depends on the answer to the above. >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
Are there any type of 'pgadmin' like interfaces for the linux side? -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] Sent: Tuesday, November 10, 2015 2:12 PM To: Mammarelli, Joanne T; pgsql-general@postgresql.org Subject: Re: [GENERAL] Best tool to pull from mssql On 11/10/2015 11:06 AM, Mammarelli, Joanne T wrote: > Thanks - I've been dying trying to find the software (on the windows side) for part 3 .. > > https://www.mssqltips.com/sqlservertip/3663/sql-server-and-postgresql- > foreign-data-wrapper-configuration--part-3/ Another option would be to install the Postgres server in a Linux VM on your Windows machine. > > -----Original Message----- > From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] > Sent: Tuesday, November 10, 2015 1:49 PM > To: Mammarelli, Joanne T; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Best tool to pull from mssql > > On 11/10/2015 10:26 AM, Mammarelli, Joanne T wrote: >> Continuously syncing .. we have database in mssql. Ideally we'd like to connect to the mssql databases and pull outthe fields/content we required and populate that in a postgres database. > > When all else fails you can go the ODBC route. I have done that through Access to create a linked Postgres table that Ican populate from other data sources. > > Here are some articles that seem to be doing something similar using SQL Server and Postgres. In the article Postgres isinstalled on a Linux box, but that does matter for the ODBC usage. : > > https://www.mssqltips.com/sqlservertip/3661/creating-twoway-data-acces > s-between-sql-server-and-postgresql--part-1/ > > https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql- > linked-server-configuration--part-2/ >> >> >> -----Original Message----- >> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] >> Sent: Tuesday, November 10, 2015 1:26 PM >> To: Mammarelli, Joanne T; pgsql-general@postgresql.org >> Subject: Re: [GENERAL] Best tool to pull from mssql >> >> On 11/10/2015 10:21 AM, Mammarelli, Joanne T wrote: >>> Hi .. we're on a hunt to locate either pgloader or the tds_fdw >>> extension to assist us with pulling data FROM a Microsoft sql server >>> (we're a windows-based postgres installation). >> >> So are we taking about a one time operation or continuously syncing or something in between? >> >>> >>> Is this the proper way to handle the process? >>> >> >> Depends on the answer to the above. >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
On 11/10/2015 11:13 AM, Mammarelli, Joanne T wrote: > Are there any type of 'pgadmin' like interfaces for the linux side? You can use pgAdmin on Windows to look at/manage Postgres servers on any platform as long as it is reachable. You can also install pgAdmin on Linux if you desire, but that would be redundant if you are using VM. > > > > -----Original Message----- > From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] > Sent: Tuesday, November 10, 2015 2:12 PM > To: Mammarelli, Joanne T; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Best tool to pull from mssql > > On 11/10/2015 11:06 AM, Mammarelli, Joanne T wrote: >> Thanks - I've been dying trying to find the software (on the windows side) for part 3 .. >> >> https://www.mssqltips.com/sqlservertip/3663/sql-server-and-postgresql- >> foreign-data-wrapper-configuration--part-3/ > > Another option would be to install the Postgres server in a Linux VM on your Windows machine. > >> >> -----Original Message----- >> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] >> Sent: Tuesday, November 10, 2015 1:49 PM >> To: Mammarelli, Joanne T; pgsql-general@postgresql.org >> Subject: Re: [GENERAL] Best tool to pull from mssql >> >> On 11/10/2015 10:26 AM, Mammarelli, Joanne T wrote: >>> Continuously syncing .. we have database in mssql. Ideally we'd like to connect to the mssql databases and pull outthe fields/content we required and populate that in a postgres database. >> >> When all else fails you can go the ODBC route. I have done that through Access to create a linked Postgres table thatI can populate from other data sources. >> >> Here are some articles that seem to be doing something similar using SQL Server and Postgres. In the article Postgresis installed on a Linux box, but that does matter for the ODBC usage. : >> >> https://www.mssqltips.com/sqlservertip/3661/creating-twoway-data-acces >> s-between-sql-server-and-postgresql--part-1/ >> >> https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql- >> linked-server-configuration--part-2/ >>> >>> >>> -----Original Message----- >>> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com] >>> Sent: Tuesday, November 10, 2015 1:26 PM >>> To: Mammarelli, Joanne T; pgsql-general@postgresql.org >>> Subject: Re: [GENERAL] Best tool to pull from mssql >>> >>> On 11/10/2015 10:21 AM, Mammarelli, Joanne T wrote: >>>> Hi .. we're on a hunt to locate either pgloader or the tds_fdw >>>> extension to assist us with pulling data FROM a Microsoft sql server >>>> (we're a windows-based postgres installation). >>> >>> So are we taking about a one time operation or continuously syncing or something in between? >>> >>>> >>>> Is this the proper way to handle the process? >>>> >>> >>> Depends on the answer to the above. >>> >>> >>> -- >>> Adrian Klaver >>> adrian.klaver@aklaver.com >>> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
Take a look at Pentaho Kettle <http://community.pentaho.com/projects/data-integration/> . They have a free community edition. I use it frequently to migrate data from MSSQL to Postgres. It has a nice GUI for setting everything up and the transforms/jobs can be scheduled. -- View this message in context: http://postgresql.nabble.com/Best-tool-to-pull-from-mssql-tp5873415p5873509.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wed, Nov 11, 2015 at 9:37 AM, taspotts <t_spotts@tcdi.com> wrote:
Take a look at Pentaho Kettle
<http://community.pentaho.com/projects/data-integration/> . They have a
free community edition.
+10
I use it frequently to migrate data from MSSQL to Postgres. It has a nice
GUI for setting everything up and the transforms/jobs can be scheduled.
--
View this message in context: http://postgresql.nabble.com/Best-tool-to-pull-from-mssql-tp5873415p5873509.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
SSIS can be used to migrate data from MSSQL to Postgres and back.
It is native for MS SQL
You can use PG ODBC driver
or Devart ODBC driver
On Wed, Nov 11, 2015 at 9:27 AM, Scott Mead <scottm@openscg.com> wrote:
On Wed, Nov 11, 2015 at 9:37 AM, taspotts <t_spotts@tcdi.com> wrote:Take a look at Pentaho Kettle
<http://community.pentaho.com/projects/data-integration/> . They have a
free community edition.+10I use it frequently to migrate data from MSSQL to Postgres. It has a nice
GUI for setting everything up and the transforms/jobs can be scheduled.
--
View this message in context: http://postgresql.nabble.com/Best-tool-to-pull-from-mssql-tp5873415p5873509.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Nov 10, 2015 at 12:26 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 11/10/2015 10:21 AM, Mammarelli, Joanne T wrote: >> >> Hi .. we’re on a hunt to locate either pgloader or the tds_fdw extension >> to assist us with pulling data FROM a Microsoft sql server (we’re a >> windows-based postgres installation). > > So are we taking about a one time operation or continuously syncing or > something in between? > >> Is this the proper way to handle the process? > > Depends on the answer to the above. My strategy is a little unconventional. I use pl/sh to call into sqsh -- from there I export the data to .csv with 'go -m csv' and then load it via 'COPY'. This gives great performance and a dblink-ish ability to lateral data. If I wasn't doing that, I'd probably be using one of the fdw approaches -- tds, jdbc, or odbc. Any ETL tool that involves a point/click GUI or editing .xml files is a total non-starter for me. merlin