SSIS + PostgreSQL via ODBC: the fetch options that avoid Out of memory — and the -- comment trap

Every PostgreSQL-to-SSIS extraction via ODBC sooner or later hits two walls: a package that blows up memory when reading a large table, and a generic error — Error while executing the query — that says absolutely nothing about what went wrong. Both have the same practical origin: the default behavior of the psqlODBC driver and the thin layer SSIS puts on top of it. This article is the complete script, in the order the investigation actually happens.

Note: the examples use placeholders (server=...;uid=...). Never version connection strings with real host, user and password.

Step 0 — Why memory blows up

By default, psqlODBC fetches the entire result into the client's memory before handing the first row to the application. For a query returning a few thousand records, nobody notices. For a table of millions of rows, SSIS tries to materialize everything in the Data Flow buffer and hits the classic "Out of memory" — often without even starting to write to the destination.

The cause isn't SSIS "being weak"; it's the driver handing over one giant block at once. The solution is to make the driver stream in batches instead of buffering everything.

Step 1 — Streaming with a server-side cursor: UseDeclareFetch + Fetch

This is the setting that solves 90% of memory cases. In the connection string (or the DSN options):

Driver={PostgreSQL Unicode};server=...;port=5432;database=...;uid=...;
UseDeclareFetch=1;Fetch=10000

What each option does:

• UseDeclareFetch=1 — the driver switches to using a server-side cursor (DECLARE ... CURSOR) in PostgreSQL. Instead of bringing everything, it keeps only one batch of rows in memory at a time. It's exactly what prevents "Out of memory".
• Fetch=N — how many rows the driver fetches per round trip to the server (per FETCH). It's the batch size. A practical starting point is Fetch=5000 to Fetch=10000; go up for narrow rows and a fast network, down for very wide rows.

With that, the client's memory stays constant, regardless of table size. The price is fair: Postgres keeps exactly Fetch rows cached at a time.

A complementary tweak that helps SSIS: psqlODBC reports large widths for unbounded text columns, and SSIS sizes the per-row buffer from those widths. Options like MaxVarcharSize control this — but be careful: too low a value truncates data. Adjust with knowledge of the schema, not by guessing.

Step 2 — When the cursor errors: UseServerSidePrepare=0

There's a known incompatibility: UseDeclareFetch=1 can conflict with UseServerSidePrepare, which is on by default in many driver installations. When both are active, the driver sometimes fails to build the DECLARE CURSOR internally. If turning on declare/fetch makes the extraction start failing, try turning it off explicitly:

...;UseDeclareFetch=1;Fetch=10000;UseServerSidePrepare=0

UseDeclareFetch is also not compatible with some scenarios — SELECT ... FOR UPDATE, scrollable cursors and batches with multiple commands separated by ;. A simple SELECT shouldn't fall into this, but it's good to know the list exists.

Step 3 — The generic error hides the truth: turn on CommLog

Here's the point that costs hours. SSIS shows:

Error while executing the query

…and swallows the real message PostgreSQL returned. Before you start guessing, force the driver log. Temporarily add to the connection string:

...;CommLog=1

(or Debug=1). This generates a C:\psqlodbc_xxxx.log file with the whole driver ↔ Postgres conversation, including the full error message. Run it again, open the end of the file, and the real cause will be there — instead of SSIS's generic text.

The real case: a -- comment that swallowed the FETCH

It was CommLog that revealed the problem in a concrete case. The log showed something like:

declare "SQL_CUR..." cursor with hold for
  SELECT ... FROM public.<table>
  WHERE extract(year from data) = 2025 --where (id_importacao > 0 ...
  ;fetch 10000 in "SQL_CUR..."

The culprit was a line comment -- left over at the end of the query — old commented-out code.

Why did it go unnoticed? When the query runs on its own (normal mode, no cursor), the -- is harmless: Postgres ignores the rest of that line and runs the WHERE normally. It worked.

But with UseDeclareFetch=1, the driver builds a composite command in a single string, with no line break between the parts:

BEGIN; declare "cursor" ... for SELECT ... WHERE ... 2025 --comment; fetch 10000 in "cursor"

Since there's no real \n between the --comment and the ;fetch 10000 in ... the driver appends, the comment swallows the entire FETCH. The log confirmed it: only two oks appeared — BEGIN and DECLARE CURSOR. The FETCH never ran; it became part of the comment. The driver kept waiting for a response that never came, got lost and sent a ROLLBACK — and SSIS translated all of that into the generic "Error while executing the query".

The fix wasn't in the connection string — it was in the query. Just remove the commented-out part:

SELECT "data", id, id_importacao, garagem, numerorps, serierps, cpf_cnpj, valor
FROM public.<table>
WHERE extract(year from data) IN (2025, 2026)

Keeping UseDeclareFetch=1;Fetch=10000, the extraction started working — the cursor mechanism itself was right (DECLARE CURSOR returned ok); only the FETCH was being masked.

The rule that sticks: a line comment (--) and SQL assembled on a single line don't mix. If you need to keep old logic, take it out of the query that goes to SSIS, or use /* ... */ with the closing guaranteed.

When ODBC isn't worth the fight: a server-side cursor in Python

If, even with everything tuned, the ODBC driver stays unstable for very large volumes — and in practice it tends to be more fragile in those cases — it's worth moving that specific extraction to a server-side cursor in Python (psycopg2):

import psycopg2

conn = psycopg2.connect(host="...", dbname="...", user="...", password="...")
# named cursor = server-side; fetches in batches, constant memory
with conn.cursor(name="extraction") as cur:
    cur.itersize = 10000  # equivalent to Fetch
    cur.execute("""
        SELECT "data", id, id_importacao, garagem, numerorps,
               serierps, cpf_cnpj, valor
        FROM public.recibos
        WHERE extract(year from data) IN (2025, 2026)
    """)
    for row in cur:      # streaming, one row at a time
        ...

It solves the same memory problem (server-side cursor, batch controlled by itersize), without depending on the ODBC driver's quirks inside SSIS.

Quick checklist

1. Memory blowing up? UseDeclareFetch=1;Fetch=10000.
2. Cursor erroring? UseServerSidePrepare=0.
3. Generic "Error while executing the query"? CommLog=1 and read C:\psqlodbc_xxxx.log.
4. In the log, the FETCH disappears after a --? Delete the line comment from the query (or use /* */).
5. Very large volume and unstable ODBC? A server-side cursor in Python (psycopg2, cursor(name=...) + itersize).