November 6, 2014 at 7:30 am
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
November 6, 2014 at 7:46 am
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
November 6, 2014 at 7:54 am
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]
November 6, 2014 at 8:27 am
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
November 6, 2014 at 8:31 am
Thank you for your help Phil. I will try this method out.
Your help was much appreciated!
November 6, 2014 at 8:40 am
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
November 6, 2014 at 4:30 pm
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.
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
November 9, 2014 at 5:21 pm
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