Learn About Microsoft SQL Server 2008
By Certification Magazine —
1 | 2 | These questions are based on 70-448: TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
A Self Test Software Practice Test
Objective: Implement an SSIS Solution.
Sub-objective: Implement package logic by using variables.
Single answer, multiple-choice
You are developing a SQL Server 2008 SQL Server Integration Services (SSIS) package. You have included a Script component and an Execute SQL task in the package. The Script component calculates the number of records in a SQL Server 2008 table. You want to use the value calculated by the Script component in the Execute SQL task. What should you do?
A. Create a user variable and set the scope as the Script task.
B. Create a user variable and set the scope as the package.
C. Create a system variable and set the scope as the Script task.
D. Create a system variable and set the scope as the package.
Answer:
B
Tutorial:
You should create a user variable and set the scope as the package to ensure the value calculated by the Script component can be used by the Execute SQL task. Integration Services uses two namespaces: User and System. Variables in the User namespace are custom created by the user, but System namespace variables cannot be created. User-created variables can have literal values or beset to an expression.
When you create a variable in the User namespace, you also need to specify its scope as a package, a container or a task. When a variable is created with the package scope, it is available to all the tasks and containers in the package. Variables with a container scope are accessible by all the tasks in the container, but not to any task outside the container. When the variable is created with the task scope, it is available only to that task and not to any other task in the package.
In the given scenario, you should create a user variable and specify the scope as the package to ensure the variable can be accessed by both the Script component and the Execute SQL task.
You should not create a user variable and set the scope as the Script task because, when a variable is created with the scope as a task, the variable is accessible to only that task and not to any other task in the package. Therefore, the variable will not be accessible to the Execute SQL task.
The options stating that you should create a system variable are incorrect because you cannot create variables in the System namespace. You can only create variables in the User namespace.
Reference:
Objective: Configure, deploy and maintain SSIS.
Sub-objective: Manage SSIS package execution.
Multiple answer, multiple-choice
You are the administrator for the SQL Server 2008 Servers in your company. You create packages that are configured to run multiple tasks related to file archiving and business requirements. You want to configure a file to log information as the package runs. If your package fails, you want to correct the problem and restart the package from the point of failure. What should you configure? (Choose three.)
A. Configure the CheckpointFileName property on the package.
B. Configure SaveCheckPoints property on the package to True.
C. Configure SaveCheckPoints property on the package to False.
D. Configure the FailPackageOnFailure property to True for each task.
E. Configure the FailPackageOnFailure property to False for each task.
Answer:
A, B, D
Tutorial:
You should configure the CheckpointFileName property on the package, configure SaveCheckPoints property to True on the package and configure the FailPackageOnFailure property to True for each task.
The CheckpointFileName property specifies the name of the checkpoint file. The checkpoint file is used to restart the package from the point of failure.
Setting the value of the SaveCheckpoints property to True will allow the package to save information to the checkpoint file on each checkpoint. In this scenario, if the package fails, it can use the saved information from the checkpoint file to restart the execution from the point of failure. After successful completion of the package, the checkpoint file will be deleted. If you set the SaveCheckpoints property to False, there will be no checkpoints from which the package can restart.
The FailPackageOnFailure property specifies the restart point for the package on failure of the task. You should set the FailPackageOnFailure property to True for each task in this scenario. If the package fails, you correct the problem with first task and restart the package. At the next failure, you correct the problem with the second task and restart the package; then correct the problem with any other successive tasks. The package will always start up from the point of failure, allowing you to save time, effort and resources over starting the package from the beginning.
If you set the FailPackageOnFailure property to False, the package will not fail when a task fails to complete, making it more difficult to fix problems in multiple tasks within a package.
References:
SQLServerCentral.com > What's new with SSIS?
Objective: Implement an SSAS solution.
Sub-objective: Implement a data-source view.
Single answer, multiple-choice
You are the database administrator of your company. You install SQL Server Analysis Services on a computer named SQL1. You want to create a data source view to define logical relationship between two tables in the same data source. You open the Create Relationship dialog box. In which direction should you define the relationship?
A. From the primary key of the source table to the foreign key of the destination table.
B. From the foreign key of the source table to the primary key of the destination table.
C. From the foreign key of the source table to the foreign key of the destination table.
D. From the primary key of the source table to the primary key of the destination table.
Answer:
B
Tutorial:
You should define the relationship from the foreign key of the source table to the primary key of the destination table. In SQL Server 2008 Analysis Services, relationships are required for identifying fact and dimension tables and for constructing queries to retrieve data from data sources. When you use the Data Source View Wizard and Data Source View Designer, relationships automatically are defined between tables that you add to the data-source view. This relationship is based on the relationships of the underlying database or on the specified name matching criteria.
In addition to the automatically defined relationships, you should also define logical relationships to supplement the automatically defined relationships. Using the Create Relationship dialogue box, you should select the appropriate table in the Source (foreign key) table list and the Destination (primary key) table list. Next, you should select the columns from the Source Columns and Destination Columns lists to define a relationship between the two tables. The direction of the relationship must be from the foreign key of the source table to the primary key of the destination table. If you specify the wrong direction, you will be prompted to correct the order.
1 | 2 |
