SSIS Parameter Vs Variables

  • Hi,

    Just looking to see if you have any thoughts of using variables vs parameters in SSIS. I have extensively used variables previously to make CSV imports for files and associated folder structures dynamic using variables in SSIS expressions.

    Examples of the kind of variables I have used are:

    1. Source Folder: @[User::Server] + @[User::SourceFolder]

    2. FilePath: @[User::Server] + @[User::SourceFolder] + @[User::FileName]

    And so on ..

    So for one to change the variables above you would have to go into the package and change the @server variable.

    Is there any advantage of using parameters over the metohd I have been using?

    Thank you

  • Yes, a couple of major reasons come to mind:

    * parameters can be at project level (so defined only once and shared by many packages).

    * the values of parameters can be set using SSIS environments.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Thank you for your reply Phil ..

    From your experience can you get Project Level parameters to be used in SSIS expressions along with other variables?

    So you it be the case where I can use it like:

    Source Folder: ProjectParmeterServer + @[User::SourceFolder]

  • aarionsql (11/6/2014)


    Thank you for your reply Phil ..

    From your experience can you get Project Level parameters to be used in SSIS expressions along with other variables?

    So you it be the case where I can use it like:

    Source Folder: ProjectParmeterServer + @[User::SourceFolder]

    Sure. The syntax to refer to them in expressions is

    @[$Project::ParamName]

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Thank you for your help Phil. I will try this method out.

    Your help was much appreciated!

  • No problem, thanks for posting back.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • As a side note, if you haven't gotten yourself upgraded (like my shop) past 2k8R2, I still use variables and configurations... I just configure the variables, then share that config amongst multiple packages.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Thanks Craig .. Thats what I do to when I work on 2008R2. New to the world of parameter in 2012.

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

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