sql_variant data type in Lookup activity

[ryand009]

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

[v-hashadapu · Accepted solution]

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...