Post reply

SSIS driving me nuts... again!

  • May 12, 2012 at 5:20 pm

    #260813

    I found the problem and fixed it but... I'd like to understand! :crazy:

    I have an "OLE DB Command task" that fires a SQL Server stored procedure with ~20 named parameters.

    EXEC MyProc @MyArg1 = ?, @MyArg2 = ?, @MyArg3 = ?...

    What I don't understand is how does SSIS generates the "Available Destination Columns" based on these parameters?

    For some reason SSIS got it's nickers in a twist and the order of the "destination columns" displayed did not match the order of the parameters in my statement... I may have modified my statement but... "so what?"

    It looks like OLE DB relies on this order as Profiler showed me SSIS was firing the proc with the params based on their order rather than name.

    Is it just me... or is it really that messy?

    Thanks

    Eric

  • May 12, 2012 at 6:51 pm

    #1486810

    Eric,

    In OLEDB the parameters are not named . The question mark (?) is the parameter and that's what the parser processes. That's why the order matters.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • May 13, 2012 at 6:04 am

    #1486848

    Ddi someone get fired after designing this?

    Just kidding! 😀

    Thanks for the info

  • May 14, 2012 at 12:05 pm

    #1487303

    Eric Mamet (5/13/2012)

    Ddi someone get fired after designing this?

    Just kidding! 😀

    Thanks for the info

    It's a pretty common pattern going back a long ways. ODBC uses "ordinal parameters" as well. It goes back to how the low-level data access APIs were designed way back in the day. If you consider the overhead of always passing a parameter name you can imagine how it can actually makes things faster in the large.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 14, 2012 at 3:13 pm

    #1487413

    Now that you mention it, it's true that I can use ADO .Net connections which are far cleaner... but then I can pay a penalty price...

    Cheers

    Eric 😉

  • May 16, 2012 at 2:02 am

    #1488224

    Eric Mamet (5/14/2012)

    Now that you mention it, it's true that I can use ADO .Net connections which are far cleaner... but then I can pay a penalty price...

    Cheers

    Eric 😉

    You are using an OLE DB command to fire off a stored procedure for every row.

    You are already paying a penalty price 😉

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 16, 2012 at 3:33 pm

    #1488713

    I know but it's "worth it" in this case

Viewing 7 posts - 1 through 7 (of 7 total)

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