How to set a variable using a scalar-valued tSQL function in SSIS SQL Task
ssis
Solution
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.
Problem
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.