none
executing stoered procedure in ssis through execute SQL task

    Question

  • I have a simple stored procedure like,

    EXEC

     

    @return_value = [dbo].[sqsp_CreateLoanBatchRecord]

    @LoanBatchName

    = N'a',

    @FileETLTypeName

    = N'import',

    @LoanBatchDescription

    = N'b',

    @LoanBatchID

    = @LoanBatchID OUTPUT

    SELECT

     

    @LoanBatchID as N'@LoanBatchID'

    SELECT

     

    'Return Value' = @return_value

    GO
    I will read

    @LoanBatchName ,@FileETLTypeName ,@LoanBatchDescription from input variable and want to store
    @LoanBatchID in a variable.


    I am trying to execute this sp through Exicute SQL Task in SSIS and gewtting following error msg.
    failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


    Can any one help me with this.

    Thanks

    Saturday, January 02, 2010 11:44 PM

Answers

All replies

  • you'll need to map the parameters in the parameter mappings section of the t-sql task editor

    Given this as an example procedure
    create proc test (@in varchar(1),@return varchar(1)=null out)
    as
    set @return = @in
    select @return as [Resultingoutput]

    In the task you need to do a few things to get the @return value back out to the package into a variable. 

    first set the ResultSet to "Single Row" or adjust as needed for your output. 

    For the example procedure your call would be "exec test ?".  The ? holds the value by index of the parameter you map in the parameter mappings panel. 

    In parameter mappings create mappings to two variables.  one would be the input.  in the example case that would be a variable to teh package scope of User::var1 set for direction of input and data type of varchar.  The parameter name will be the ordinal position of the parmater to the procedure.  so 0 in this case

    Now to handle the output, fo to result set and add a resulting value for the output parameter from the procedure.  this case named, resultingoutput and set it to a variable.  for simplicity, User::var2

    edit: could follow this also  http://www.julian-kuiters.id.au/article.php/ssis-execute-sql-task-output-parameters


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Sunday, January 03, 2010 12:51 AM
    Moderator
  • I would probably place the sql statment in a variable and then use an expression against that variable:

    http://rafael-salas.blogspot.com/2007/11/ssis-mapping-parameter-inside-of.html


    http://rafael-salas.blogspot.com | Don’t forget to mark the post(s) that answered your question
    • Marked as answer by Zongqing Li Friday, January 08, 2010 9:52 AM
    Sunday, January 03, 2010 5:46 AM
    Moderator