5

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
Yulia V
  • 3,507
  • 10
  • 31
  • 64
  • ResultSet is Single row – Yulia V Feb 19 '13 at 18:31
  • http://www.sqlis.com/post/The-Execute-SQL-Task.aspx describes how to use stored procedures, but not the functions. Also, I am not sure I understand 0s and ?s, maybe this is where the problem is. – Yulia V Feb 19 '13 at 18:33
  • It runs now, but the user variable value is not set. I have tried `SELECT dbo.MyDB_GetJobId() AS vJobId` and set `Result Name` to vJobId, but the user variable is still empty. – Yulia V Feb 19 '13 at 18:40
  • And that's to be expected. EXEC goes with stored procedures, SELECT is for table and scalar objects. This function is a scalar valued one. – billinkc Feb 19 '13 at 18:41
  • @Siva: In Locals, when the brekapoint set at the next task is hit. Using convert as `SELECT convert(nvarchar(50), dbo.MyDB_GetJobId()) AS vJobId` did not help. – Yulia V Feb 19 '13 at 18:45
  • @billinkc: sorry never come across SELECT and user functions in combination, I have literally 3-4 days of SSIS exprrience, so I guess some silly errors are expected. – Yulia V Feb 19 '13 at 18:46
  • @Siva: works fine now, null result was actually correct; updated my table and got a non-trivial output. Many thanks! – Yulia V Feb 19 '13 at 18:50

1 Answers1

18

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()"

Execute SQL General tab

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.

Execute SQL Result Set tab

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.

enter image description here

billinkc
  • 59,250
  • 9
  • 102
  • 159