Postgres ODBC, Linked server, SQl server Reporting Services connection

Connec to Postgres using ODBC connection follow these steps


1) Download Postgres ODBC driver from http://www.postgresql.org/ftp/odbc/versions/msi/

or a 64-bit postgres ODBC driver from http://code.google.com/p/visionmap/downloads/detail?name=setup_psqlODBC_x64_8.3.4.0.exe&can=2&q=

2) Install above ODBC drivers

3) Create system DSN ... On windows XP > Control Panel > Administrative Tools > Data Sources (ODBC)

4) Go to System DSN Tab > Add > Select PostgresSQL Unicode sriver > Finish

5) On Prompted page add

Data Source --- Whatever you want to name the DSN

Database - Name of postgres database

Server - Name of Postgres server

Port:5432 (or whaever you database port is..)
Username and Password: Enter postgres database username /password you wish to use

Test the System DSN by clicking on Test

6) Create Linked Server on SQL Server 2008/2005 to connect to postgres database

Start SQL server management studio
Connect to SQL server instance where who wish to create linked server
Go to ServerObject > linked Servers > New Linked Server
Fill the Linked server properties

Select Provider: Microsoft OLEDB Provider for ODBC Drivers
Product name: postgres (you can put whatever u like)
Data Source: Name of System DSN created in last step

OK

Right click Linked Server and Test Connection
Now you query postgres from SSMS by something like
select * from linkedserver_name.database_name.schema_name.Table

7) Creating Connection to Postgres from SQL Server Reporting Services (SSRS)

Create a New data Source
Name : Test
Data Source Type: ODBC
Connection String: Driver={PostgreSQL UNICODE};Server=testserver;Port=5432;Database=Test;Schema=public;
Connect using: Credentials stored securily in report server, eneter user_name, password and Test Connection

If it is 64-bit driver then
Connection string is something like
Driver={PostgreSQL 64-Bit ODBC Drivers};Server=testserver;Port=5432;Database=test;Schema=public;


Using above techniques you can also connect from SSIS (SQL server Integration Services)


Hope it is helpful...