3
votes

I noticed in SSIS Package Configuration, when choosing a property to add to the configuration file there are often more than one version of that property listed. For instance, I can add a package variable property through this path:

Package->Variables->VariableName->Property

But I can also access the same property through these paths:

Package->Executables->Variables->VariableName->Property

Package->Connection Managers->Connection->Variables->VariableName->Property

First question is what is the reason for this? And secondly, what is the most appropriate option to add the property to the configuration file?

1

1 Answers

1
votes

The reason is encapsulation/scoping and everything is configurable.

If you create a variable at the package level scope, everything that is added to that package can access and use it. Add a Sequence Container, inside that add a Foreach Loop and inside that, an Execute SQL Task to a Data Flow to a File System Task. You could create a Variable on each of those objects. Everything contained inside that thing has visibility to those Variables. My Foreach loop defines and populates CurrentFileName and that's used by the Execute SQL Task (to record what file I am processing), the Data Flow to use the correct source file and finall the File System Task to push it into the archive. There may not be a need for that variable to be visible outside of that scope. A specific scenario where I would create a Variable not-at-package-scope is if I am loading things in parallel---like a Dimension load. I have 3 Foreach Enumerators processing through Dimension Lists. I'm lazy and have the same code inside of each Enumerator, just using a different source list.

Other than than, you generally do not want to create SSIS Variables at any scope except the package scope. Otherwise, you can spend far too much time clicking on objects looking for variables (the package explorer tab can be helpful in situations like this)

My rule of thumb on configurations would be only select Value or ConnectionString (depending on whether it's a Variable or a Connection Manager) and only select it at the root level.

Finally, if you don't have BIDS Helper it's a free add in for VS that makes SSIS/SSAS/SSRS development a much less painful experience.