Calling a scalar function in an Execute SQL task - SSIS 2005


  • Hi,

    I've a scalar function with an output parameter and 5 input parameter. I need to call into an Execute SQL task.

    I've written this statement in the task: SELECT MessageText = dbo.f_BuildMessagge(?, ?, ?, ?, ?, ?), but doesn't function!

    The first parameter is the outputted. I've created six parameters to map for the excecute sql task that as ResultSet has Single row and returned the MessageText.

    Any suggests to me, please? Thanks

    21/محرم/1433 11:23 ص


جميع الردود

  • Hi, what do you want to do with the resulting message? Maybe you can just build a stored procedure that receives those parameters, calls the scalar function and then performs the desired action (e.g., write to log table, send email, update some column).

    I don't think you can pass output parameters like that to some variable in SSIS (in an easy way, you can always use scripts but...).


    21/محرم/1433 11:56 ص
  • Hi,

    I'd like to avoid to use Script task in a 64-bit environment that my customer doesn't want to upgrade to avoid any problems with old pkgs. So I think to create a parameterized scalar function. Now, is it possible to invoke a parameterized scalar function in an Execute SQL task? I'd like to use a function and not a stored procedure, if possible of course.

    Many thanks

    22/محرم/1433 07:41 ص
  • As I said, I cannot use a Script task. So, is it possible or not to call a scalar function from an Execute SQL task? Please, I need to know the right reply.

    Many thanks

    24/محرم/1433 09:24 ص
  • Hi, you can call the scalar function but I think you can't get the output parameter. If you told me what you want to do with the resulting message I could help a little more.

    If you really want to avoid stored procedures (and use scalar functions) you can have a temporary table, insert the records in this table and have a view that gets these records and adds an extra column with the resulting message.


    24/محرم/1433 11:08 ص
  • Hi,

    I'd like to invoke a scalar function with the same simplicity as for a stored procedure, if possible. Why do I need to have a temporary table in order to transform some SSIS variables? Moreover, I've read that it is possible to create the command for a function to pass a variable for the Execute SQL task.

    However, I've used some stored procedures. But for me a scalar function should be better.


    24/محرم/1433 10:05 م
  • You do not need temp tables or stored procs.

    Use a SQL statement that gets a single-row rowset back, like this:

    SELECT dbo.f_BuildMessagge(?, ?, ?, ?, ?, ?) AS result

    In the ResultSet property, select Single Row.  On the ResultSet page of the editor, add a variable, and set the Result Name to zero (0).


    Todd McDermid's Blog Talk to me now on
    25/محرم/1433 04:28 م
  • Hi Todd, in my proofs I've set the ResultSet as SingleRow in the Execute SQL task with the statement

    SELECT dbo.f_BuildMessage(?, ?, ?, ?, ?, ?) as result.

    I've specified zero as the Result Name mapped to a pkg variable.

    But it doesn't function! Are your tried a such task? And the input parameters, that replace the placeholders?


    25/محرم/1433 05:47 م
  • Set up the input parameters on the Parameters page.  You may want to try it out for testing purposes by hard-coding some values into the statement itself. 

    And please clarify what "doesn't function" means...

    Todd McDermid's Blog Talk to me now on

    26/محرم/1433 05:14 م
  • Hi, I've already mapped the input parameters for the Execute SQL task.

    The task doesn't function because I've an error message about occurred errors in an OLE DB operation made by more steps, verify the single values of the OLE DB status ...


    26/محرم/1433 05:58 م
  • Please try your task without parameters.

    If it fails, please post complete error messages, as well as the complete configuration of the Execute SQL Task.

    Todd McDermid's Blog Talk to me now on
    • تم وضع علامة كإجابة بواسطة Eileen Zhao 05/صفر/1433 02:49 ص
    27/محرم/1433 05:10 م