Guillermo Acosta
Guillermo Acosta

Reputation: 1

Azure Data Factory: Issue with Insert Operation

I have a pipeline that, using a lookup to retrieve a list of companies, iterates through each company and fetches people from a source system (Traerse_Personas lookup). The retrieved people are in the format shown in the attached image. return of the lookup that retrieved people

Then, I have another lookup that attempts to insert these people into the destination table, using the output from the previous lookup. The reason I'm using lookups for insertion is that both databases are on-premises PostgreSQL, and I encountered many limitations. So, I'm using ODBC to perform direct inserts.

The issue is that it's failing. This is the query being generated: insert generated in the lookup which I found in the monitoring section. The lookup is receiving the value from the previous lookup. Here is the lookup that is failing:

@concat(
'INSERT INTO crm.personas (
    wilter_id, wilter_itau_id, wilter_hsbc_id, documento, nombre_completo, 
    trabajo, fecha_nacimiento, notas, creado, actualizado, 
    sexo, estado_civil, hijos, persona_juridica, multicliente, primer_nombre,
    segundo_nombre, primer_apellido, segundo_apellido, fallecido
) SELECT 
    p.wilter_id, 
    COALESCE(p.wilter_itau_id, 0) AS wilter_itau_id,  
    COALESCE(p.wilter_hsbc_id, 0) AS wilter_hsbc_id,  
    p.documento_limpio, 
    REPLACE(p.nombre_completo, '','' , '') AS nombre_completo,  
    p.trabajo, 
    p.fecha_nacimiento_validada::TIMESTAMP WITHOUT TIME ZONE,  
    p.notas, 
    p.creado::TIMESTAMP WITHOUT TIME ZONE, 
    p.actualizado::TIMESTAMP WITHOUT TIME ZONE, 
    p.sexo, 
    p.estado_civil, 
    p.hijos, 
    p.persona_juridica, 
    p.multicliente, 
    p.primer_nombre, 
    p.segundo_nombre, 
    p.primer_apellido, 
    p.segundo_apellido, 
    p.fallecido
FROM jsonb_to_recordset(''', string(activity('Traerse_Personas').output.value), '''::jsonb) AS p(

Can someone help me, please?

Upvotes: 0

Views: 9

Answers (0)

Related Questions