I've setup a simple connection from SSIS to a Postgres db hosted in the cloud on Heroku. Reads are great, very fast, but when I try and insert data it doesn't seem to be batching, and that's very slow. MS documentation states that if the provider doesn't support batching then SSIS will automatically shift into row by row inserts which is what I'm seeing. Apparently windows/ssis doesn't think the driver supports batching.
My question is if anyone on the support team or in the community can point me in the right direction. Is there some configuration that I'm missing? Is there another Postgres driver I should be using?
Any help would be greatly appreciated, thanks.
Postgres ODBC Insert not Batching ...
Re: Postgres ODBC Insert not Batching ...
Well, it seems I might have half an answer. I found the SSIS flow components and they seemed to work much better as destinations. The only problem there is that the Devart PostgreSql connection that the destination uses doesn't seem to support SQL tasks on the control flow?
Does that mean that I need two separate products to insert and execute SQL statements? Also, if there is a solution to my original problem I'd be interested to hear it.
Thanks
Does that mean that I need two separate products to insert and execute SQL statements? Also, if there is a solution to my original problem I'd be interested to hear it.
Thanks
Re: Postgres ODBC Insert not Batching ...
We are always improving our products. ODBC Driver for PostgreSQL is not an exception. Its performance increase on execution of Batch operations is our high-priority task, that we will resolve soon. To make your issue clearer, please describe the SSIS flow components you have chosen in more details. What exact behavior do you expect when using ODBC Driver for PostgreSQL with control flow ?