How to create PostGres connection manager in SSIS?

[screenplay_alanp]

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

[Moderator]

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.