Forum Discussion

girishtharwani2's avatar
girishtharwani2
Helper III
10 months ago
Solved

Could not read message metadata

Hi,   I am using stored procedure to load data from Stg to DWH in Snowflake. The stored procedure worked in snowflake UI however same stored procedure is not working in fabric pipeline. Int...
data engineering
Data Pipeline
  • girishtharwani2's avatar
    girishtharwani2
    10 months ago

    Thanks for the suggestions.

    The issue was, snowflake was returning column which has length more than 100000 and lookup can not return lengths till 99000.

    Trimmed the message to 99000 in snowflake and it worked.

     

    Thanks,

    Girish

girishtharwani2's avatar
girishtharwani2
Helper III
10 months ago

Thanks v-karpurapud for the quick assistance.

It is expected that SP will return counts of rows updated and inserted and it has been working for other tables.

I use lookup activity to execute the SP.

 

Your help will be appreciated.

 

Thanks,

Girish

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    10 months ago

    Hi girishtharwani2 

     

    The Lookup activity in Microsoft Fabric pipelines expects the output to be a single-row result set with supported scalar datatypes. Although it works for other tables, it fails with this stored procedure because the output format doesn't match the required structure, even though the DWH updates are successful.

     

    Modify the output to return a single SELECT statement with two columns: rows_inserted and rows_updated. This will enable the Lookup activity to process the response correctly.

     

    For Example:

    CREATE OR REPLACE PROCEDURE DWH.LOAD_FROM_STG()

    RETURNS TABLE (rows_inserted NUMBER, rows_updated NUMBER)

    LANGUAGE SQL

    AS

    $$

    DECLARE

    v_inserted NUMBER;

    v_updated  NUMBER;

    BEGIN

     -- logic --

     INSERT INTO ...;

    LET v_inserted = SQLROWCOUNT;

    UPDATE ...;

    LET v_updated = SQLROWCOUNT;

    RETURN TABLE (SELECT v_inserted, v_updated);

    END;

    $$;

    Should you have any additional questions, please feel free to reach out, and we will be glad to assist you further.

     

    Regards,

    Microsoft Fabric Community Support Team.