MicrosoftI was able to call a stored procedure from the Lookup activity until a few days ago. We built a pretty extensive pipeline to effectively synchronize any SQL Server schema and data to a Snowflake DB. The entire process is generic / dynamic. We built custom logic to automatically add/remove columns and tables to accommodate for schema drift in the source. We built custom logic to generate dynamic MERGE INTO statements (as snowflake will happily create rows with the same primary key 🙄) and ADF only provides COPY INTO. Mount the stage, execute the merge, drop the stage. Same as the Copy activity only with Merge instead.
All was working quite well until a few days ago. Now all SP’s invoked from a Lookup activity fail with “The following ODBC query is not valid” This is incredibly frustrating. We spent nearly 12 weeks building out a custom ADF workflow and now we’re left here in the dust.
