2

We're using MS SQL Server 2005 at work and I want to export our data to PostgreSQL for testing.

MS SQL Server has an export function but the only one that looks like it would work would be to export to Excel files. I'd rather use a SQL script or function that can export to a file and import those files into a PostgreSQL server for testing. I've searched a number of sites for a SQL script but I can't find one that will work.

What would be a good approach to accomplish this?

Improve this question
4
  • Automated options are limited. wiki.postgresql.org/wiki/… . Often you land up exporting and hand-converting the DDL, then using an ETL tool or just doing table CSV dumps for the data. Commented Jul 4, 2014 at 4:33
  • I've seen most of those. The conversion programs are all pay programs so they only export part of the database and all of the explanations on the Postgres site are for older versions of SQL Server. Commented Jul 4, 2014 at 5:08
  • I agree with Craig: you need to adjust your existing DDL scripts to Postgres syntax, create the tables, export from SQL Server as text (csv), then import. You might also want to try sqlserver2pg. I never used it, so I don't know if it works well: github.com/dalibo/sqlserver2pgsql Commented Jul 4, 2014 at 5:23
  • 2
    The wiki is all user-created information and helper scripts. You're not likely to find a point-and-click free tool. If I'd done the work to do an automated migration from a $lots commercial database I'd be charging for the right to use it too. Commented Jul 4, 2014 at 5:46

1 Answer 1

Reset to default
2

Please refer to below thread. SSIS would help you in transferring data from SQL server to postgres Micrsoft forum thread for similar issue

I have written article on how to transfer data from SQL server to postgres but this is applicable to SQL server 2008 and onwards you can read it though

How to transfer records from sql server to postgres

Improve this answer
7
  • Thanks, but I have a question. I've been Googling for a while and still can't find an answer. In the second link, the author says "open SSIS connection manager" after creating the ODBC link. I have NO IDEA where SSIS Connection manager is. I have opened BIDS and created the OBDC connection but can't find the connection manager. Commented Jul 9, 2014 at 6:02
  • Connection manager is not present in ODBC but present in SSIS designer canvas. Can you please refer below link on how to create a simple SSIS package mssqltips.com/sqlservertutorial/209/… Commented Jul 9, 2014 at 8:29
  • Shanky, that example asks me to open a file called "samples.sln" that exists in a "Samples" directory (which they don't provide the path for). I don't have a "Samples" directory or a "samples.sln" file on this server (I searched both partitions). I do have a "blank.sln", I guess I can use that. Commented Jul 10, 2014 at 6:22
  • Shanky, I created a new project and used it to create the connection. But I don't know how to copy tables from SQL server to Postgres. The tutorial just says you're "good to go", but I don't get how to do the copy from one DB to the other. Commented Jul 10, 2014 at 9:45
  • How much competeancy you have with SSIS ? I guess you are totally new to SSIS. I would like you to read on Net about simple SSIS package creation.Did you read line by line to my article social.technet.microsoft.com/wiki/contents/articles/… there is Social msdn link in article with GUI did you reffered to that ? Commented Jul 10, 2014 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.