MS SQL - export tables to format compatible with PostgreSQL

Question

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?

• Craig Ringer: 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.
• Tensigh: 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.
• user1822: 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
• Craig Ringer: 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.

Answer 1

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

• Tensigh: 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.
• Shanky: 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/…
• Tensigh: 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.
• Tensigh: 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.
• Shanky: 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 ?