locked
ADO Connection Manager Variable Mapping RRS feed

  • Question

  • Hi All,

    Can Some body help me to create Execute SQL Task using ADO Connection Manager and variable mapping for SP with input ,output parameter types and return value?

    Thanks, 

    Venkat.

    Thursday, September 22, 2016 5:08 AM

Answers

  • Also, don't use ADO.  It's a very old (obsolete) technology layer that allowed VB and VBScript to interact with OleDB.

    Use ODBC, OleDB, or ADO.NET instead.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Thursday, September 22, 2016 12:38 PM
  • Hi Venkat,

    Based on my test, if setting Parameter Name as three “?” in Execute SQL Task Parameter Mapping page and using  ADO connection in Execute SQL Task. Your SP will executed successfully. For more details, please reference the following information.


      

    By the way, @ Olaf Helper if I set Parameter Name as 0,1,2 in  Execute SQL Task Parameter Mapping page, the Execute SQL Task fails with following error, do you have any thoughts on this ?

    [Execute SQL Task] Error: Executing the query "
    
    exec Addition_Of_Nos_Res ?,?,? output
    
    " failed with the following error: "Value does not
    fall within the expected range.". Possible failure reasons: Problems with
    the query, "ResultSet" property not set correctly, parameters not set
    correctly, or connection not established correctly. 

    Regards,

    Seif


    Regards, Seif

    • Proposed as answer by Seif Wang Monday, September 26, 2016 2:01 AM
    • Marked as answer by Eric__Zhang Monday, October 10, 2016 3:22 AM
    Friday, September 23, 2016 10:36 AM

All replies

  • Hello Venkat,

    Where exact do you have an issue with? See also MSDN Execute SQL Task for some more details.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 22, 2016 6:14 AM
  • Hi Olaf,

    Thanks for your reply.

    I am trying to execute a SP from Execute SQL Task using ADO Connection and please find the SP Below.

    ALTER PROC [dbo].[Addition_Of_Nos_Res]
    @firstNum int,
    @secondNum int,
    @result int out
    as
    begin
     set nocount on;
     declare @res int=0;
     set @res=@firstNum+@secondNum;
     set @result=@res;
     return @res;
    end

    I have to pass two input params, receive output param value and return value.

    SQL Statement in E SQL task follows.

    ? = [dbo].[Addition_Of_Nos_Res] ?,?,? OUTPUT

    i am getting an error that "no value given for one or more required parameters."

    I am able to do so using OLEDB and ADO.net Connection only problem with ADO Connection.

    Thanks,

    Venkat.


    Thursday, September 22, 2016 9:17 AM
  • Hi Venkat,

    Based on my test, if you want to execute a Stored Procedure with input parameter and output parameter, please reference the following steps.

    1. My Stored Procedure in database. 
    2. Create variables in SSIS for SP input parameter and output variable. 
    3. Configure input output parameter for SP in Execute task. 


    4. In this case I pop-up the exec SP result with a Script, and you could also reference this value in other task. 

    If you have any other questions, please feel free to post.


    Regards, Seif


    • Edited by Seif Wang Thursday, September 22, 2016 9:52 AM
    Thursday, September 22, 2016 9:52 AM
  • Hi Seif,

    Problem with ADO Connection not ADO.net Connection, test your package using ADO Connection and please return a value also from SP also. My Package works fine with ADO.net and OLEDB Connection.

    Thanks,

    Venkat.

    Thursday, September 22, 2016 10:09 AM
  • Hello Venkat,

    For ADO (OleDB) it works the similar way, only differences
    - In the SQL Statement you have to use the question mark ? as placeholder for the Parameter, as you already did in your second post
    - The SSIS Parameter in the mapping must be number in order of the occurence of the placeholders, starting with 0.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 22, 2016 10:38 AM
  • Also, don't use ADO.  It's a very old (obsolete) technology layer that allowed VB and VBScript to interact with OleDB.

    Use ODBC, OleDB, or ADO.NET instead.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Thursday, September 22, 2016 12:38 PM
  • Hi Olaf,

    I used ? as place holder and did mapping as 0,1,2 in my case and used aInteger as datatype, I am getting an error called,

    [Execute SQL Task] Error: Executing the query "[dbo].[Addition_Of_Nos_Res] ?,?,?" failed with the following error:
    Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet"
     property not set correctly, parameters not set correctly, or connection not established correctly.

    Thanks,

    Venkat.

    Friday, September 23, 2016 10:27 AM
  • Hi Venkat,

    Based on my test, if setting Parameter Name as three “?” in Execute SQL Task Parameter Mapping page and using  ADO connection in Execute SQL Task. Your SP will executed successfully. For more details, please reference the following information.


      

    By the way, @ Olaf Helper if I set Parameter Name as 0,1,2 in  Execute SQL Task Parameter Mapping page, the Execute SQL Task fails with following error, do you have any thoughts on this ?

    [Execute SQL Task] Error: Executing the query "
    
    exec Addition_Of_Nos_Res ?,?,? output
    
    " failed with the following error: "Value does not
    fall within the expected range.". Possible failure reasons: Problems with
    the query, "ResultSet" property not set correctly, parameters not set
    correctly, or connection not established correctly. 

    Regards,

    Seif


    Regards, Seif

    • Proposed as answer by Seif Wang Monday, September 26, 2016 2:01 AM
    • Marked as answer by Eric__Zhang Monday, October 10, 2016 3:22 AM
    Friday, September 23, 2016 10:36 AM