Transferring Data from OLTP to OLAP databases

With some offline conversations, Zane was able to determine that if they could keep a connection open, it would hasten the validation.

To that end, I suggested they change the property on the Connection Manager to flip the RetainSameConnection property to True from its default.

OK - I'm going to slightly revise my advice in the previous thread.

If you have only 1 physical server, you're always going to have problems with OLTP conflicting with OLAP - i.e. disk bottleneck. It would be far easier to use PostgreSQL as the server for both than the other way round - see my previous comments on the richer PostgreSQL SQL functionality.

We also don't know what sort of activity your app has, nor do we know your CPU, RAM and disk config (RAID?).

In the light of what you have written above, my advice is to use PostgreSQL for both - maybe two separate schemas on the same instance of PostgreSQL - or possibly two instances of PostgreSQL on the same machine. This is more or less what @Mat and @CraigRinger said. The logic behind separating the schemas is that when and if you get a second server (which you should), parititioning the functionality between the two machines will be trivial.

If most of the functionality of your app to date is of a CRUD nature, then porting it to PostgreSQL should be relatively painless, but, again, YMMV.