Skip to main content

How to easily import data from SQL Server into PostgreSQL


I was looking for an ETL software similar to SSIS that would allow me to easily move data from SQL Server to PostgreSQL. I tried using SSIS, but I was never able to make BIDS generated the schema of the destination table automatically like it would do with a SQL server destination table.
I downloaded Pentaho’s Spoon data integrator and it surpised how easy it was to do it there. The designer is somewhat similar to BIDS. You drop your input tables (from SQL Server) and then you drop your destination tables (PostgreSQL tables).
The good thing is that you do not need to create the tables in PostgreSQL beforehand. You just need to create the destination database. When you open the properties of the Table Output, you can enter the name of the new table and then do the following to create the table based on the data input:

  1. Click “Database Fields”
  2. Click “SQL”
  3. Click “Execute”

That’s it. Your table will be created in PostgreSQL using the correct data types.
Pentaho’s Spoon data integrator also have other nice features, such as Json input/output, Google Analytics input, Hadoop and SalesForce connectors, and more. The software is free and can be found at http://www.pentaho.com/explore/pentaho-data-integration

Comments

James Zicrov said…
Thank you so much for finding information and exploring in-depths about how SQL and SSIS combine to solve out and churn more and more database operations or rather solutions for it.

SSIS Postgresql Read

6:02 AM
Post a Comment

Popular posts from this blog

Mail labels and letter templates for jasperreports

The following are free (MIT license) mailing labels and letter templates for jasperreports that you can download and use in jasperserver and/or ireport: Update 3/15/2011 : I moved the Mail templates zip file here . Please consider making a small donation if the templates are of help to you, Thank you! If you need more information on how to use those templates please leave a comment in the blog.
14 comments
Read more

How to create online multiplayer HTML5 games in Contruct2

  Construct2 can use websockets to send and receive messages between games. By using socket-io , we can use a Node.js script as the server and my modification to the socket-io plugin for Construct2 to allow the games to synchronize data between them in real-time. There are two parts to this design: the Node.js server and the Construct2 clients (the games playing). The main part of building an online multiplayer HTML5 game is to plan: how the clients will communicate how often and what to communicate how much of the logic will go into the server and how much to the client. In my sample game, I chose to have each client own a player and have the server just relay messages: Use string messages in the form TypeOfMessage, Parameter1, Paremeter2, Parater3, etc to communicate. Have the clients send their player position about 16 times a second. Whenever their player shoots, the client needs to send a message immediately. Almost all of the game logic will...
9 comments
Read more

How to get the raw data posted to a Java Servlet

While working in porting GeneCMS to Google App Engine and Silverlight , I stumble at one annoying problem: how to get the raw data that was sent by the browser via a POST. In this GeneCMS port, the silverlight client actually generates all the HTML that the server used to generate. The raw generated HTML is then sent to the GAE application, with some metadata about the generated pages. The GAE servlet then reads the posted data as a string and extract the individual generated pages. Each generated page is then saved into the GAE database to allow the CMS to server the pages. After tweking around in the wrong places, I finally found out that HttpServletRequest’s getInputStream method is an input stream to the raw data posted. So I modified my servlet to get the POST data into a string like this: String posted_data = ""; BufferedReader in = new BufferedReader(new InputStreamReader( request.getInputStream())); String line = in.readLine(); while (line != null) { posted...
2 comments
Read more