From: | "Jamie Lawrence-Jenner" <jamie(dot)jenner(at)autovhc(dot)co(dot)uk> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | SSIS and Postgres |
Date: | 2009-10-07 08:26:19 |
Message-ID: | !&!AAAAAAAAAAAYAAAAAAAAACSjyZCDEbJLs7GIuOJ8tGbCgAAAEAAAADhvk+ReBrVDq8W+TqLFFsABAAAAAA==@autovhc.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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>
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> http://tiny.cc/hOb6L and
<http://tiny.cc/uRF1f> 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> 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> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Karina Guardado | 2009-10-07 09:44:14 | problems with encoding |
Previous Message | Stuart Bishop | 2009-10-07 06:53:21 | Re: attempted to lock invisible tuple - PG 8.4.1 |