Hi there

we are having some problems using OLEDB PGNP and SSIS, this is a post we have added to experts exchange, but we were wondering whether anyone here could shed some light on this. We are also interested how others manage ETL

Cheers

Jamie

Data Warehousing Postgres

We're considering using SSIS to maintain a PostgreSql data warehouse. I've used it before between SQL Servers with no problems, but am having a lot of difficulty getting it to play nicely with Postgres. Im using the evaluation version of the OLEDB PGNP data provider (http://tiny.cc/qLoS2).

I wanted to start with something simple like UPSERT on the fact table (10k-15k rows are updated/inserted daily), but this is proving very difficult (not to mention Ill want to use surrogate keys in the future).

Ive attempted http://tiny.cc/hOb6L and http://tiny.cc/uRF1f which are effectively the same (except I dont really understand the union all at the end when Im trying to upsert) But I run into the same problem with parameters when doing the update using a OLEDb command  which I tried to overcome using http://tiny.cc/8EmyM but that just doesnt seem to work, I get a validation error  
The external columns for complent.... are out of sync with the datasource columns... external column Param_2 needs to be removed from the external columns.
(this error is repeated for the first two parameters as well  never came across this using the sql connection as it supports named parameters)

Has anyone come across this?

AND:

The fact that this simple task is apparently so difficult to do in SSIS suggests Im using the wrong tool for the job - is there a better (and still flexible) way of doing this? Or would another ETL package be better for use between two Postgres database? -Other options include any listed on http://tiny.cc/PbIO4. I could just go and write a load of SQL to do this for me, but I wanted a neat and easily maintainable solution.