Execute Task: Is it possible to pass parameters in random order to Stored Proc?

  • As TSQL good practice, I have always made sure I would specify each parameter name when calling a stored procedure, hence not relying on the order

    For instance

    EXEC dbo.MyProc @MyParam2 = 44, @MyParam1 = 'Whatever'

    If I understand properly, when firing this stored procedure through an SSIS Execute Task (either Ado.Net or OleDB), I cannot specify the parameter names, hence the order must be correct

    So I should specify

    EXEC dbo.MyProc ?, ?

    where my first parameter (named "0" for OleDb) must be the value for @MyParam1 and the second one must be that for @MyParam2 (named "1" for OleDb).

    Is there a "cleaner" way that would allow to specify something like @MyParam1 = 'something' and @MyParam2?

    Many Thanks

  • When you set up the parameters in SSIS, you can provide their names. In that case, it works off of names, not sequence. You do that on the Parameter Mapping tab of the Execute SQL Task command.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I tried that but it seems to ignore the parameter names... and falls down if they are in the wrong order

    :ermm:

  • Actually... It works fine if I use the Ado.Net provider instead of OleDb

    Thanks

    Eric:D

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply