How to Transfer Records from SQL Server to Postgres
Introduction
With growing need of BI and ETL, firms are always looking for easy means to load data from one RDBMS to other and SSIS is the best feature provided by Microsoft to achieve this. In this post I would be discussing about how to transfer records from SQL Server 2008 R2 to PostgreSql database using SSIS. I have never worked with PostgreSql before but as heard they say its most advanced open source in market. As read from PostgreSql blog I came to know that now EnterpriseDB is providing support for it.
Using ADO.NET Destination component I transferred 3.5 million records from SQL Server to PostgreSql in 2hrs and 48 minutes with just 8 GB of physical RAM.
Problem
I faced a situation where I had to transfer records from SQL Server to PostgreSql, I had to do this task using SSIS feature provided by SQL Server. I will not go deep into how to create simple package to transfer records from SQL Server to PostgreSql but I will surely tell you what connection string, destination component to use in connection manager and in package so that you can connect to PostgreSql easily and transfer records.
Solution
From MS forum I came to know about CozyRoc proviers (http://www.cozyroc.com/download), this will surely help you when you are working on SQL Server 2005, as your data destination (ODBC destination) because destination component ADO.NET destination is not present in SQL Server 2005. But I had SQL Server 2008 R2. Initially CozyRoc was good, it worked for small data but when data size increased I started facing error. And also my management was not ready to use this tool due to some license policy even if it says its free. More discussion below:
My search was now for inbuilt providers in SSIS which I can take help for this activity and I used ADO.NET as destination and it solved the issue. I will shortly tell you about this. But first lets move to PostgreSql.
For transferring data to PostgreSql you need to install PostgreSql ODBC provider (driver) which you can download from http://www.postgresql.org/ftp/odbc/versions/msi/.
My PostgreSql was 9.2, So I downloaded last installable from the link. Now the question here is, Should you download 32 bit or 64 Bit version of software , because your SQL server can be 32 bit or 64 bit, So here is the answer whatever be your SQL Server version download 32 bit ODBC driver (at least this holds true for my scenario, I had 64 bit SQL Server running)
**
**Download the latest .MSI file for Windows from this location: http://www.postgresql.org/ftp/odbc/versions/msi/
Run the MSI file as Administrator on your SQL Server instance and click Finish to install it completely (it doesn't requires a restart).
Go to location C:\Windows\SysWow64 in this folder search for ODBCAD32.exe. Launch this ODBC Administrator Utility
Choose the type of Data Source you need, I generally use SYSTEM DSN. Click Add. The ODBC Administrator will present a list of drivers. Scroll to the bottom and you will see two options for PostreSQL; ANSI and Unicode. Select the version you need and click Finish.
The Administrator will present a screen on which you must supply a database name, server name, user name and password.
After you have supplied values for these fields, click the Datasource button,
Make sure the Use Declare/Fetch box is checked. The driver will fail to retrieve larger datasets and will hang for long time comsuming memory.
Now you are ready to build a new connection in connection manager of SSIS and tag it to the Postgres data source you just created. Enter the User name and Password in the fields provided. Test the connection and you should be ready to go.
Now go to SSIS connection manager right-click and select New ADO.NET connection.
Connection manager box appears in provider select ".Net Providers\Odbc Data Provider".
Click radio button 'Use user or system datasource name' in drop down select system DSN you just created. Now if you would have installed 64 bit driver that driver would be available on Start Menu > All Programs > Administrative Tools > Data Source (ODBC). Not in C:\Windows\SysWow64\ODBCAD32.exe as 32 bit drivers are listed here and 64 bit in former one. That is why I asked you to install 32 bit driver also in 'Use user or system datasource name' if you would have created DSN in 64 bit it will not be seen in that drop down as SSIS operates in 32 bit and will see 32 bit drivers and and DSN created using that 32 bit driver. Below is a screenshot for your reference:
http://social.msdn.microsoft.com/Forums/getfile/257714
Now you have successfully created Connection for your destination PostgreSql. Create OLEDB connection from connection manager or your source.
Use OLEDB as source and .Net Destination as destination.
Right-click on OLEDB source and go to Advanced Editor tab add connection manager to it. See for columns which come as output.
Now right-click on ADO.net destination go to Advanced Editor tab provide your connection manager and you are good to go.
You can add data conversion if required. If you get error like cannot covert between Unicode and Non-Unicode data types.
Note
In my case my server was 64 bit but when I installed 64 bit PostgreSql ODBC driver, DSN which I added in Start Menu > All Programs > Administrative Tools > Data Source > ODBC was not visible while creating .net connection manager as SSIS being 32 bit compatible it was searching for DSN in C:\Windows\SysWow64\ODBCAD32.exe. So please install 32bit driver if you are using ADO.NET connection manager and source.
Please provide your feedback in the comments below.
See Also
List of articles by Shanky
SQL Server Integration Services Portal
This Article participated in Technet Guru Competition for July 2013 and won Silver Medal.