SSIS + PostgreSQL via ODBC : les options de fetch qui évitent l'Out of memory — et le piège du commentaire --

Toute extraction de PostgreSQL vers SSIS via ODBC finit tôt ou tard par heurter deux murs : un package qui fait exploser la mémoire en lisant une grande table, et une erreur générique — Error while executing the query — qui ne dit absolument rien sur ce qui a échoué. Les deux ont la même origine pratique : le comportement par défaut du driver psqlODBC et la fine couche que SSIS pose par-dessus. Cet article est le mode d'emploi complet, dans l'ordre où l'investigation se déroule vraiment.

Note : les exemples utilisent des placeholders (server=...;uid=...). Ne versionnez jamais des connection strings avec hôte, utilisateur et mot de passe réels.

Étape 0 — Pourquoi la mémoire explose

Par défaut, psqlODBC récupère le résultat entier dans la mémoire du client avant de livrer la première ligne à l'application. Pour une requête qui renvoie quelques milliers d'enregistrements, personne ne le remarque. Pour une table de millions de lignes, SSIS tente de tout matérialiser dans le buffer du Data Flow et tombe sur le classique « Out of memory » — souvent sans même commencer à écrire vers la destination.

La cause n'est pas que SSIS « soit faible » ; c'est le driver qui livre un bloc géant d'un coup. La solution est de faire transmettre le driver par lots (streaming), au lieu de tout bufferiser.

Étape 1 — Streaming avec un curseur server-side : UseDeclareFetch + Fetch

C'est le réglage qui résout 90 % des cas de mémoire. Dans la connection string (ou les options du DSN) :

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

Ce que fait chaque option :

• UseDeclareFetch=1 — le driver se met à utiliser un curseur server-side (DECLARE ... CURSOR) dans PostgreSQL. Au lieu de tout ramener, il ne garde en mémoire qu'un lot de lignes à la fois. C'est exactement ce qui empêche l'« Out of memory ».
• Fetch=N — combien de lignes le driver récupère par aller-retour au serveur (par FETCH). C'est la taille du lot. Un point de départ pratique est Fetch=5000 à Fetch=10000 ; montez pour des lignes étroites et un réseau rapide, descendez pour des lignes très larges.

Avec ça, la mémoire du client reste constante, quelle que soit la taille de la table. Le prix est juste : Postgres garde en cache exactement Fetch lignes à la fois.

Un réglage complémentaire qui aide SSIS : psqlODBC rapporte de grandes largeurs pour les colonnes texte sans limite, et SSIS dimensionne le buffer par ligne à partir de ces largeurs. Des options comme MaxVarcharSize contrôlent cela — mais attention : une valeur trop basse tronque les données. Ajustez en connaissance du schéma, pas au pif.

Étape 2 — Quand le curseur échoue : UseServerSidePrepare=0

Il y a une incompatibilité connue : UseDeclareFetch=1 peut entrer en conflit avec UseServerSidePrepare, qui est activé par défaut dans beaucoup d'installations du driver. Quand les deux sont actifs, le driver échoue parfois à construire le DECLARE CURSOR en interne. Si activer le declare/fetch fait échouer l'extraction, testez de le désactiver explicitement :

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

UseDeclareFetch n'est pas non plus compatible avec certains scénarios — SELECT ... FOR UPDATE, les curseurs scrollables et les lots à plusieurs commandes séparées par ;. Un SELECT simple ne devrait pas tomber là-dedans, mais il est bon de savoir que la liste existe.

Étape 3 — L'erreur générique cache la vérité : activez le CommLog

Voici le point qui fait perdre des heures. SSIS affiche :

Error while executing the query

…et avale le vrai message que PostgreSQL a renvoyé. Avant de partir dans les suppositions, forcez le log du driver. Ajoutez temporairement à la connection string :

...;CommLog=1

(ou Debug=1). Cela génère un fichier C:\psqlodbc_xxxx.log avec toute la conversation driver ↔ Postgres, y compris le message d'erreur complet. Relancez, ouvrez la fin du fichier, et la vraie cause y sera — au lieu du texte générique de SSIS.

Le cas réel : un commentaire -- qui a avalé le FETCH

C'est le CommLog qui a révélé le problème dans un cas concret. Dans le log apparaissait quelque chose comme :

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..."

Le coupable était un commentaire de ligne -- resté à la fin de la requête — du vieux code commenté.

Pourquoi passait-il inaperçu ? Quand la requête tourne seule (mode normal, sans curseur), le -- est inoffensif : Postgres ignore le reste de cette ligne et exécute le WHERE normalement. Ça marchait.

Mais avec UseDeclareFetch=1, le driver monte une commande composite en une seule chaîne, sans saut de ligne entre les parties :

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

Comme il n'y a pas de vrai \n entre le --commentaire et le ;fetch 10000 in ... que le driver ajoute, le commentaire avale le FETCH entier. Le log le confirmait : seuls deux ok apparaissaient — BEGIN et DECLARE CURSOR. Le FETCH ne s'exécutait jamais, il était devenu une partie du commentaire. Le driver attendait une réponse qui ne venait pas, se perdait et envoyait un ROLLBACK — et SSIS traduisait tout ça par le générique « Error while executing the query ».

La correction n'était pas dans la connection string — elle était dans la requête. Il suffisait de retirer la partie commentée :

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

En gardant UseDeclareFetch=1;Fetch=10000, l'extraction s'est mise à fonctionner — le mécanisme de curseur lui-même était correct (le DECLARE CURSOR a renvoyé ok) ; seul le FETCH était masqué.

La règle qui reste : un commentaire de ligne (--) et du SQL monté sur une seule ligne ne font pas bon ménage. Si vous devez garder de l'ancienne logique, sortez-la de la requête qui part vers SSIS, ou utilisez /* ... */ avec la fermeture garantie.

Quand l'ODBC ne vaut pas la bataille : un curseur server-side en Python

Si, même avec tout réglé, le driver ODBC reste instable pour de très gros volumes — et en pratique il tend à être plus fragile dans ces cas —, il vaut la peine de déplacer cette extraction précise vers un curseur server-side en Python (psycopg2) :

import psycopg2

conn = psycopg2.connect(host="...", dbname="...", user="...", password="...")
# curseur nommé = server-side ; récupère par lots, mémoire constante
with conn.cursor(name="extraction") as cur:
    cur.itersize = 10000  # équivalent au 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, une ligne à la fois
        ...

Cela résout le même problème de mémoire (curseur server-side, lot contrôlé par itersize), sans dépendre des particularités du driver ODBC dans SSIS.

Checklist rapide

1. La mémoire explose ? UseDeclareFetch=1;Fetch=10000.
2. Le curseur échoue ? UseServerSidePrepare=0.
3. « Error while executing the query » générique ? CommLog=1 et lisez le C:\psqlodbc_xxxx.log.
4. Dans le log, le FETCH disparaît après un -- ? Supprimez le commentaire de ligne de la requête (ou utilisez /* */).
5. Volume très gros et ODBC instable ? Un curseur server-side en Python (psycopg2, cursor(name=...) + itersize).