locked
How to pass parameter value as user Input to Stored Procedure in ExecuteSQL task RRS feed

  • Question

  • Hi.

    I have a stored Procedure, which needs to be executed in SSIS. I have added the ExecuteSQL tool and have added the SP name.

    The SP takes in a parameter.

    I have created a variable - CurrYearMonth ,  scope is Package1 , DataType - Int32

    In the Execute SQL task, I have given the SP name in SQLStatement

    Example: sp_spname

    In the parameter mapping, I have defined:

    Variable Name: User::CurrYearMonth

    Direction: Input

    DataType: Int32

    ParameterName: @YrMonth

    ParameterSize: 0

    How should the user be able to pass the data ? How to pass the parameter ?

    Thanks

    Wednesday, April 29, 2020 9:04 AM

Answers

  • How should the user be able to pass the data ?

    SSIS is not intended for user interaction and have no GUI to enter parameter values; SSIS is a ETL to for automated processed.

    One workaround: Place a text file somewhere, user edit the text file with the parameter values, the SSIS package reads the file to get the values


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Venkatzeus Wednesday, April 29, 2020 9:59 AM
    Wednesday, April 29, 2020 9:50 AM