Re: PostgreSQL and SSIS

On 05/23/11 1:38 PM, Bailey, Rick wrote:
>
> I have an Microsoft SQL Server IS job that has been running in
> production for several years. It pulls data from a PostgreSQL 8.4.2
> database on Linux into an SQL Server 2005 installation on Windows
> Server 2003 (all 32-bit). I am re-writing this in a test environment
> that consists of the same PostgreSQL database and an SQL Server 2008R2
> installation on Windows Server 2008R2 (all 64-bit).
>
> On the Windows Server 2008R2 machine, I installed 64 bit Postgres ODBC
> drivers and found that I could not see them when creating an ADO.net
> connection manager in BIDS. A bit of googling later, I removed the 64
> bit drivers and installed 32-bit Postgres ODBC drivers and set up DSNs
> usning windows\SysWOW64\odbcad32.exe. When setting up the DSNs,
> clicking the test button returned 'Connection successful'.
>
> Back to BIDS, create a new ADO.net connection manager, ODBC Data
> Provider, select the DSN name in 'Use user or system data source
> name', hit the test connection button. It returns 'Test connection
> succeeded'. Create a data flow task, edit, add an ADO.NET source,
> edit, select the new connection manager, Data access mode is set to
> 'Table or view', click the drop down for 'Name of the table or view:',
> it says loading and will sit there like that forever. If I click it
> again, it returns the following error message:
>
> 'Could not retrieve the table information for the connection manager
> 'PostgreSQL30'. Object reference not set to an instance of an object.
> (Microsoft.DataWarehouse)'
>
> If I select 'SQL Command' as the Data Access mode, and enter any SQL
> Command (eg select * from PostgresTable) and hit the Preview button,
> the expected data is returned.
>
> My question is why can it not return the list of tables, but it can
> return data.
>
> Any help would be appreciated.
>

usually everything has to be the same bit-ness, the app (BIDS?), the
ODBC provider, and the database runtime libraries at least.

just curious, have you tried this with npgsql instead of ODBC ? that
should in theory provide a direct ado.db kind of connection to postgres.