Re: [GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL and SSIS
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Craig Ringer
> Sent: Monday, May 23, 2011 11:00 PM
> To: Bailey, Rick
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] PostgreSQL and SSIS
>
> On 24/05/11 04:38, Bailey, Rick wrote:
>
> > 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.
>
> "BIDS" appears to be the Microsoft "Business Intelligence Development
> Studio," an add-on to Visual Studio 2008. Correct?
>
> http://msdn.microsoft.com/en-us/library/ms173767.aspx
>
> > 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'.
>
> Yep, that's the right approach if it's a 32-bit client program. I doubt
> you need to uninstall the 64-bit drivers but it probably won't hurt.
>
> > My question is why can it not return the list of tables, but it can
> > return data.
>
> Honestly, I haven't the foggiest. Without access to BIDS it's kind of
> hard to test.
>
> You're saying this all worked before on a 2k3 (32-bit) server with SQL
> Server 2005 where all the other software was the same. Correct? So the
> _only_ changes are win2k3 -> win2k8 and ms-sql 2005 -> ms-sql 2008?
>
> I'd like to be able to help you, but have never used ADO or ADO.NET in
> my life, and only have a minimal knowledge of ODBC. If I had to debug
> this, I'd be using process monitor to track the low-level activity,
> wireshark to track network chat, and the postgresql server logs (set to
> log all queries) to compare the queries from the old and new clients
> and
> see if anything was different. I'd also be comparing the ODBC DSN
> settings.
You can turn on ODBC tracing, and get a total conversation of all the ODBC
calls between BIDS and PostgreSQL's ODBC driver.
Using the 32 bit ODBC administrator tool, select the tab "Tracing", browse to
the log file path that you want to use, and press the "Start Tracing" button.
After the session completes, press the "Stop Tracing Now" button {the "Start
Tracing Now" button gets relabeled as "Stop Tracing Now" after you press it}.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL and SSIS
On 24/05/11 04:38, Bailey, Rick wrote: > 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. "BIDS" appears to be the Microsoft "Business Intelligence Development Studio," an add-on to Visual Studio 2008. Correct? http://msdn.microsoft.com/en-us/library/ms173767.aspx > 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'. Yep, that's the right approach if it's a 32-bit client program. I doubt you need to uninstall the 64-bit drivers but it probably won't hurt. > My question is why can it not return the list of tables, but it can > return data. Honestly, I haven't the foggiest. Without access to BIDS it's kind of hard to test. You're saying this all worked before on a 2k3 (32-bit) server with SQL Server 2005 where all the other software was the same. Correct? So the _only_ changes are win2k3 -> win2k8 and ms-sql 2005 -> ms-sql 2008? I'd like to be able to help you, but have never used ADO or ADO.NET in my life, and only have a minimal knowledge of ODBC. If I had to debug this, I'd be using process monitor to track the low-level activity, wireshark to track network chat, and the postgresql server logs (set to log all queries) to compare the queries from the old and new clients and see if anything was different. I'd also be comparing the ODBC DSN settings. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL and SSIS
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. Rick Bailey Database Specialist Materials Research Institute 123 Land & Water Building University Park, PA 16802 814-863-1294
