Post reply

Variables, Parameter Markers, How Does It All Work?

  • February 14, 2006 at 8:28 pm

    #167106

    I tried to set up a Stored Procedure with parameters and tried to map them to Package Variables in SSIS.

    I setup up parameter markers in the command source of an OLEDB datasource and mapped them to Packag Variables.

    When I run the pacakge, I get and error syaing that parameter @ParamName (i.e. by name rather than marker position) was not found.

    Any suggestions?

  • February 16, 2006 at 8:03 am

    #621375

    Try constructing the SQL statement in a variable as explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

    This is considered best practice rather than messing about with parameters.

    -Jamie

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • February 16, 2006 at 12:12 pm

    #621444

    Thanks Jamie,

    Does turning "Evaluate Expression" on mean that other variables can be evaluated within the variable that contains the SQL text? If that is the case I would be happy to do it this way. I had assumed that you had to construct the entire SQL string and pass it into the variable.

    I do wonder why the parameter marker method exists at all if it is so unreliable. You would think that it would at least work with SQL Server 2005.

    Thanks again for your reply.

    Dick Campbell

  • February 16, 2006 at 5:20 pm

    #621520

    Dick,

    Yes, you can ref other variables in the expression.

    The ability to parameterise SQL statements is there because that's how you did this sort of thing in DTS. Expressions as a whole came relatively late in the dev cycle by which time the ability to parameterise your SQL statements was already there.

    i can't comment on the usefulness of parameters because i never use them.

    -jamie

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • February 17, 2006 at 12:54 am

    #621562

    Thanks Jamie,

    This is a dimension of SSIS that I haven't explored yet and I am certainly influenced by DTS baggage. All that I really want is to be able to do is pass parameters to the package when I execute it rather than pass the entire SQL text.

    Best regards,

    Dick Campbell

  • February 17, 2006 at 8:15 am

    #621682

    Yeah, one thing I always say is that a DTS mindset is more a hinderance than a help. SSIS is a new paradigm.

    -Jamie

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

Viewing 6 posts - 1 through 5 (of 5 total)

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