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.
