Multiple choice technology architecture

You are developing a SQL Server 2008 Integration Services package. The package consists of a Sequence container holding a For Loop and Foreach Loop. There are two Execute SQL tasks in the Foreach Loop, and one Script Task in the For Loop. You have created a variable for the Foreach Loop. What will be the scope of the variable?

  1. The variable is only accessible to the first Execute SQL Task

  2. The variable is accessible to both Execute SQL Task, but not the Script Task

  3. The variable is accessible to both Execute SQL Task and the Script Task

  4. The variable is not accessible to any Task
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In SSIS, when you create a variable within a container (like Foreach Loop), its scope is that container and all tasks inside it. Both Execute SQL tasks are inside the Foreach Loop, so they can access the variable. The Script Task is in the For Loop (sibling container, not nested inside Foreach), so it cannot access this variable.

Keep practicing — related questions