18 Apr

SSIS Parameters And Variables

This is another of those, get my head clear, overview posts.  There’s really nothing here that probably isn’t clear with a bit of study.  The only hang up I had, and really why I wrote this, is that values that can be assigned to a parameter came up in the practice test.

Parameters

Can be assigned at either the project or package level.  They are used to assign values at run-time.  They can also be used in scripts and precedence constraints.

There are 3 types of values that can be assigned to a parameter:

  • Execution Value: Is assigned to a specific instance of an execution value.  It only applies to that specific execution.  It overrides all other values.
  • Server Value: This is the value assigned within the scope of the project.  It overrides the design value.
  • Design Value: This is the value assigned when the project is created.

A single parameter can assign values to multiple package properties.

Variables

Variables store values that a package, container, task or event handler can use at run time.  They can also be used in scripts and precedence constraints.

There are two-types of variables:

  • System: Defined by integration services.
  • User: Defined by developers.

Variables are used in other ways as well.

  • Set package properties at run time.
  • To store data values.  As an example, a Execute SQL Task can store its results in a variable.
  • As a condition in a package.
  • As a condition in a container, for instance, a For Loop task.
  • As an expression.

And, from the 10,000 foot level, that’s pretty much that.

Leave a Reply

Your email address will not be published. Required fields are marked *