How to create PostGres connection manager in SSIS?

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

How to create PostGres connection manager in SSIS?

Postby screenplay_alanp » Thu Jan 05, 2012 5:49 pm

Hi,

I have a need to migrate some data on a regular basis from a postgres db to sql server. I have the trial version DLL installed and can view it as a provider in Management Studio (so I can successfully create a linked server).

Here's how I'm attempting to create the connection in SSIS 2008 connection manager:

1. In the Data Flow tab, right-click in the Connection Managers section and select "New ADO.NET Connection"
2. In the Configure ADO.NET Connection Manager popup, click "New" (for new Data conenction)
3. In the Connection Manager popup, click on the "Provider" dropdown
- No PGNP provider is in the expanded ".Net providers" folder
- No PGNP provider is in the ".Net Providers for OleDb" folder either

I tried the same thing for a new OLE DB Connection Manager (Right-click Connection Managers section, "New", Provider: "Native OLE DB\SQL Server Native Client 10.0) and there is no PGNP or postgres provider under the "Native OLE DB" folder.

Can someone help me please?

Thank you very much!

alan
screenplay_alanp
 
Posts: 1
Joined: Thu Jan 05, 2012 5:39 pm

Re: How to create PostGres connection manager in SSIS?

Postby Moderator » Tue Jan 10, 2012 12:15 am

Steps when adding new connection in "ADO.Net Connection Manager":
1. Click on "Provider:" dropdown box on top of the dialog.
2. Expand ".Net Providers for OleDb".
3. Select "PostgreSQL Native Provider". It is a name for the PGNP Native OleDb Provider.

Steps when adding new connection in "OLE DB Connection Manager":
1. Click on "Provider:" dropdown box on top of the dialog.
2. Select "PostgreSQL Native Provider".

Other:
"Server or file name:" is used for PostgreSQL Server name or IP address.
"Location:" is not used.
"User name:" and "Password:" are for the PostgreSQL user name and password.
"Initial catalog:" - click on the dropdown to obtain list of available databases.
User avatar
Moderator
Site Admin
 
Posts: 310
Joined: Wed Oct 29, 2008 11:27 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 1 guest

cron