How to set a variable using a scalar-valued tSQL function in SSIS SQL Task

Question

I have a tSQL scalar-valued function

ALTER FUNCTION [dbo].[MyDB_GetJobId]
(
)
RETURNS [uniqueidentifier]

Its results need to go into a SSIS user variable vJobId, declared as String. SqlStatementSource is EXEC ? = dbo.MyDB_GetJobId(); result set is as follows: Result Name: 0; Variable Name: User::vJobId.

It does not work, the error message is as follows

"EXEC ? = dbo.MyDB_GetJobId()" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Connection is fine (a plain SQL request runs ok), no input parameters, ... Could you help? Thanks.

Answer 1

I initially went down the path of trying to use a Parameter Mapping with a direction of Return but that was incorrect.

Instead, I have my Execute SQL Task configured as shown. My ResultSet is a "Single Row". My SQLStatement is simply "SELECT dbo.MyDB_GetJobID()"

In the result set tab, since this is a ResultSet type of Single Row, then we provide a mapping per column with a zero based ordinal system.

This is an example demonstrating the result being assigned to the variable User::SingleRow. You can ignore Other as I was trying to make it work with via the Parameter Mapping tab.