Unable to INSERT rec into Table.

The MERGE may be working; the query used to verify it checks a different table and column. Your MERGE never updates utl.partner.last_modified_by. It writes Source_File_Name to utl.partner_network_org.created_by and last_modified_by, so that IN subquery can legitimately return no rows.

First, query the target directly using the actual parameter value:

SELECT * FROM utl.partner_network_org

WHERE created_by = '<Source_File_Name>';

Also add this immediately before the MERGE semicolon:

OUTPUT $action, inserted.partner_number,

inserted.network_org_number,

inserted.created_by, inserted.last_modified_by;

That will show each INSERT or UPDATE. If OUTPUT is empty, run only CodeSourceTableCTE as a SELECT. One of its joins is removing the staging row. The sample names appear to contain extra spaces, so compare LEN/DATALENGTH for partner_name, parent_partner_name and network_org_name, plus external_id versus corpart_number.

One more possibility: if the target row already exists with delete_ind = 'N', it matches but neither branch runs. Finally, confirm the child pipeline received Source_File_Name and ADF evaluated the @{...} expression rather than passing it literally.