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 one .In this blog i would be discussing about how to transfer records from SQL Server 2008 R2 to Postgres DB using SSIS. I have never worked with Postgre DB before but as heard they say its most advanced open source in market.As read from Postgre 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 Postgres in 2hr 48 mins with just 8 G of  physical RAM.

DETAILS

I faced a situation where I had to transfer records from SQL Server to Postgres, 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 Postgres 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 Postgre eaisly and transfer records.

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 destinatin(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 worked for small data but when data size increased I stated 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 here

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3629f163-0cd4-4b92-960c-011d28f4c9ff/how-to-transfer-data-using-ssis-from-sql-server-2005-to-postgress92-when-oledb-for-odbc-driver

My search was now for inbuild 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 Postgre.

For transferring data to Postgre you need to install Postgre ODBC provider(driver) which you can download from http://www.postgresql.org/ftp/odbc/versions/msi/ .My Postgre DB was 9.2 so i downloaded last installable from the link .Now point here to note is should I download 32 bit or 64 Bit ,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 in my scenarioS

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 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 genereally 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 RK and select New ADO.NET connection.

connection manager box appears in provider select .Net Provider\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 Windows..All programs...Administrative tools...Data source(ODBC).Not in C:\windows\syswow63\ODBCAD32.exe as 32 bit drivers are listed here and 64 bit in former one.Thats 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 dropdown as SSIS operates in 32 bit and will see 32 bit drivers and and DSN created using that 32 bit driver.Below is fig for ur ref

Now you have successfully created Connection for your destination Postgre. Create OLEDB connection from connection manager or your source.

Use OLEDB as source and .Net Destination as destination.

RK on OLEDB source and go to advanced editior tab add connection manager to it. See for columns which come as output.

Now RK on ADO.net destination go to advananced editor tab provide ur 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 type.

In my case my server was 64 bit but when i installed 64 bit Postgre ODBC driver, DSN which i added in Allprog..Administrative tools..data Source ODBC was not visible while creating .net connection manager as SSIS being 32 bit compatible it was seraching for DSN in C:\Windows\Syswow64\ODBCAD32.exe.So please install 32bit driver if you are using ADO.NET conection manager and source.

Please provide your feedback in the comments below.