sql_variant data type in Lookup activity

ryand009 — Mon, 14 Jul 2025 17:24:17 GMT

Hello

I’ve got a stored procedure that can return a student’s date of birth either as a date or as in bigInt (i.e. number of seconds from Jan 1 1970). The @outputValue in the procedure is a SQL_VARIANT data type

The date/ bigInt return value is based on the input parameter @returnType, see below ...

Trouble is Microsoft Fabric doesn’t seem to like SQL_VARIANT. I’m using the query below in a Lookup activity, but it keeps falling over because of the SQL_VARIANT

DECLARE @outputValue sql_variant;

EXEC [wm].[sp_GetStudentDoB]

@SchemaName = 'dbo',

@TableName = 'tblStudents',

@returnType = 'date', --BigInt

@outputValue = @outputValue OUTPUT;

SELECT CONVERT(DATETIME, @outputValue) AS NewDate;

Is there any work around for this?

Any suggestions much appreciated.

Thanks in advance

Roy

Re: sql_variant data type in Lookup activity

v-hashadapu — Tue, 15 Jul 2025 07:20:20 GMT

Hi @ryand009 , Thank you for reaching out to the Microsoft Community Forum.

Microsoft Fabric doesn't support the sql_variant data type in pipeline activities like Lookup. When your stored procedure returns a sql_variant as an output parameter, Fabric fails to interpret it since it expects outputs to have a clearly defined, supported SQL type, like datetime, bigint, varchar, etc.

To work around this, you need to introduce a wrapper stored procedure. The wrapper should call your original procedure and explicitly convert the sql_variant output to a supported type before returning it. Instead of returning the value as an output parameter, the wrapper should return the final result using a SELECT statement. This way, Fabric can read the output as a standard, well-defined scalar value, which will work smoothly in your Lookup activity.

If you have control over the original stored procedure, the better long-term solution would be to avoid using sql_variant altogether and return the result directly with a SELECT, already cast to the appropriate type.

Preprocess data with a stored procedure before loading into Lakehouse - Microsoft Fabric | Microsoft Learn

topic Re: sql_variant data type in Lookup activity in Data Engineering

sql_variant data type in Lookup activity

Re: sql_variant data type in Lookup activity