How to set variable using tSQL scalar function in SSIS SQL Task
I have a tSQL scalar function
ALTER FUNCTION [dbo].[MyDB_GetJobId]
(
)
RETURNS [uniqueidentifier]
Its results should go into a custom SSIS variable vJobId
declared as String
. SqlStatementSource - EXEC ? = dbo.MyDB_GetJobId()
; the result set is as follows Result Name
:: 0; Variable Name
: User::vJobId
.
It doesn't work, the error message looks like this
"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.
The connection is fine (simple SQL query works fine), no props, ... Could you help? Thank.
source to share
I first went down the path of using parameter mapping with the Return direction, but that was wrong.
Instead, I have my Execute SQL task set up as shown. Mine ResultSet
is "One Line". Mine SQLStatement
is just "SELECT dbo.MyDB_GetJobID ()"
On the ResultSet tab, since it is of type ResultSet Single Row, we provide a mapping for each column with a zero-based ordinal system.
This is an example to demonstrate that the result is assigned to a variable User::SingleRow
. You can ignore Other
it as I tried to work with it via the Parameter Mapping tab.
source to share