Clever Geek Handbook

Postgres for data storage

We are considering using SSIS to support the PostgreSql datastore. I've used it before between SQL Server without problems, but I have a lot of difficulty getting it to play well with Postgres. I'm using the evaluation version of the OLEDB PGNP data provider ( http://www.postgresql.org/about/news.1004 ).

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

Ive tried ( http://blogs.msdn.com/b/mattm/archive/2008/11/22/lookup-pattern-upsert.aspx ) and ( http://consultingblogs.emc.com/jamiethomson/archive/2006 /09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx ) which are actually the same (except that I don't really understand the union at the end when I'm trying to level up). But I am facing the same parameters issue when doing update with OLEDb command - which I tried ( http://technet.microsoft.com/en-us/library/ms141773.aspx ) but it just doesn't work, I getting validation error -

The outer columns for complent .... are out of sync with the data source columns ... the outer column "Param_2" needs to be removed from the outer columns. (this error repeats for the first two parameters too - never came across this using a SQL connection as it supports named parameters)

Has anyone come across this?

and

The fact that this simple task is seemingly so difficult to do in SSIS suggests that I am using the wrong tool for the job - is there a better (yet flexible) way to do this? Or would a different ETL package be better used between the two Postgres databases? -Other options include any listed in ( http://en.wikipedia.org/wiki/Extract,_transform,_load#Open-source_ETL_frameworks ). I could just go and write a SQL download to do this for me, but I wanted a neat and easily maintainable solution.

+2


source to share


2 answers


SCD is too slow for what I want. I need to use set based sql.

It turned out that I had a lot of problems with errors in the provider. I opened a forum thread ( http://www.pgoledb.com/forum/viewtopic.php?f=4&t=49 ) and had a helpful discussion with a moderator / support / developer.

Also Postgres doesn't allow cross-db queries, so I solved the problem like this:

  • Data source from production database to temporary archive table
  • Executing a given set of queries between a temporary table and an archive table
  • Temporary cutoff table


Note that temp is not usually a temporary table, but a copy of the archive table schema for temporarily stored data.

It took a while, but I got there at the end.

This simple task will work anyway. SSIS is by no means an enterprise-class ETL product, but it does give you some quick and easy functionality and is sufficient for most ETL work. I think this also applies to your comfort level and with it.

What enterprise ETL solution would you suggest?

0


source


I have used the Slow Change Wizard for this with great success. He can give you what you are looking for, especially with a wizard

http://msdn.microsoft.com/en-us/library/ms141715.aspx



Outer Columns Out of Synchronization: SSIS is case sensitive - I ran into this issue multiple times and it makes me want to pull my hair out.

This simple task will work anyway. SSIS is by no means an enterprise-class ETL product, but it does give you some quick and easy functionality and is sufficient for most ETL work. I think this also applies to your comfort level and with it.

0


source






More articles:


All Articles