none
Passing Variables in Execute SQL Task RRS feed

  • Question

  • Hello All,

      I've one Parametarized procedure that needs to be executed via Execute SQL Task of SSIS, upon the fullfillment of few conditions.

    For example how do I use Execute SQL Task for the below Query;

    DECLARE @StatusID INT

    SELECT @StatusID = StatusID FROM MyTable WHERE PrimaryID=?

    IF @StatusID in (10,13)

    BEGIN

    EXEC dbo.myprocedure ?,13,?,?  

    END



    ELSE IF @StatusID in (14)

    BEGIN

    EXEC MyProcedure ?,7,?,?
    END


    3 Question Marks (?) I need to pass in for MyProcedure are PrimaryID, Name and SomeValue . I'd map these 3 Parameters through Parameters mapping in Execute SQL Task.

    My Question is How Do I map the parameter in the following Part of the above Code

    DECLARE @StatusID INT

    SELECT @StatusID = StatusID FROM MyTable WHERE PrimaryID=?

    Even Here I need to pass the same PrimaryID I mentioned above...

    Please let me know how do we get this job done by using Execute SQL Task? Is it better to use Script Component Instead?





    Unknown

    Wednesday, February 27, 2013 7:56 AM

Answers

All replies

  • Hi,

    In your code there are totally 4 ?(Question Marks). Map these parameters in the same order in the parameter mapping Tab. This should Solve your issue.


    Rajkumar

    Wednesday, February 27, 2013 8:10 AM
  • Hi Rajkumar,

      No that is not working. I assumed in the same way, however it seems it won't take that variable in the following section of the code.

    DECLARE @StatusID INT

    SELECT @StatusID = StatusID FROM MyTable WHERE PrimaryID=?

    If I map only 3 Parameters then it will map with 3 Variables for Stored Procedure but it won't map the above statement.


    Unknown

    Wednesday, February 27, 2013 8:30 AM
  • Hi,

    You need to map four parameters, in following order.

    1 -> PrimaryID

    2 -> PrimaryID

    3 -> Name 

    4 -> SomeValue

    Please confirm if this doesn't work.


    Rajkumar

    Wednesday, February 27, 2013 8:35 AM
  • what will be the ParameterName for both the PrimaryIDs

    PrimaryID 0

    PrimaryID 0

    Name  1

    SomeValue 2

    Or

    PrimaryID 0

    PrimaryID 1

    Name 2

    SomeValue 3


    Unknown

    Wednesday, February 27, 2013 8:39 AM
  • PrimaryID 0

    PrimaryID 1

    Name 2

    SomeValue 3


    Rajkumar

    • Marked as answer by Roger Binny Wednesday, February 27, 2013 8:50 AM
    Wednesday, February 27, 2013 8:41 AM
  • Yes Raj Kumar...It worked...Thanks for your help...I could have been tried this earlier...Appreciate your help..

    Unknown

    Wednesday, February 27, 2013 8:51 AM