Implementing a Data Warehouse with Microsoft SQL Server 2012: Dynamic Packages

Answers

This section contains answers to the lesson review questions and solutions to the case scenario in this chapter.

Lesson 1

1. Correct Answers: A and D

   1. Correct: Project-level parameters are available.

   2. Incorrect: Solution-level parameters are not available.

   3. Incorrect: Parameters can be defined on the project or package level.

   4. Correct: SSIS supports package-level parameters.

2. Correct Answers: A and D

   1. Correct: Parameter values can be bound to build configurations.

   2. Incorrect: Variables cannot be bound to build configurations.

   3. Incorrect: Control flow task properties cannot be bound to build configurations.

   4. Correct: Project-level properties can be set for each build configuration.

3. Correct Answers: A and C

   1. Correct: You can set the SQL statement of the Execute SQL task at run time by using property expressions.

   2. Incorrect: Variable properties cannot be set by using property expressions.

   3. Correct: General data flow task properties can be set by using property expressions.

   4. Incorrect: You can only change the SQL command of the Lookup task when you are using property expressions for the data flow task that has a Lookup task inside it.

Lesson 2

1. Correct Answers: A, B, and D

   1. Correct: You can use XML as a configuration file.

   2. Correct: You can store the configuration values in a SQL Server database.

   3. Incorrect: You can only use a SQL Server database.

   4. Correct: You can use registry entries to store configuration values.

2. Correct Answers: B and D

   1. Incorrect: Parameters can be used in the project deployment model.

   2. Correct: You can set variable properties.

   3. Incorrect: You can only set data flow task properties, and not for a specific transformation.

   4. Correct: You can dynamically set properties for a Sequence Container task by using configurations.

3. Correct Answers: A, B, and D

   1. Correct: Connection properties can be set by using package configurations.

   2. Correct: You can set variable properties.

   3. Incorrect: You cannot use parameters when using package configurations.

   4. Correct: You can dynamically set properties for the package by using package configurations.

Case Scenario

1. Add parameters for the connection strings. Create development, test, and production build configurations and bind parameters to each of them with different data source values. This will allow you to execute the package from SSDT against different configurations without manually changing the value of parameters.

2. Add parameters and parameterize all needed connection managers.

3. Create a parameter that will hold the value of the file location folder. Create a new variable to store the current file name. Using SSIS expression language, set the new variable value to combine the value from the parameter with the value you get from the Foreach Loop container. Use the new variable as the property expression for the Foreach Loop to dynamically change the fully qualified file name while the package is running.