Microsoft TS:MS SQL Server 2008.Business Intelligence Dev and Maintenan Sample Questions:

1. You are an ETL developer for a mortgage company. In your application you have to load data from a Microsoft Excel source and an OLE DB source.
The Excel source contains vender data and has approximately 1000 records.
The OLE source contains loan data and has approximately 1,000,000 records.
Sample vendor data (Excel source) is shown in the following table:
VendorID VendorName 1 VendorA 2 VendorB 3 VendorC 4 VendorD 5 VendorE
Sample loan data (OLE DB source) is shown in the following table:
VendorID VendorName
1 VendorA
2 VendorB
3 VendorC
4 VendorD
5 VendorE
Sample loan data (OLE DB source) is shown in the following table:
LoanNumber VendorID LoanAmount
I-100 1 120000
I-200 1 130000
I-300 2 150000
I-400 3 150000
I-500 4 160000
I-600 4 170000
I-700 5 180000
The CIO asks you to generate an Excel file with the loan list information for each vendor. You build a SQL Server 2008 Integration Services (SSIS) package that can generate the Excel files dynamically. The Excel files should have the vendor name as the prefix and the date as the suffix (e.g., VendorA_DDMMYYYY.xlsx).
You get the error message shown in the exhibit. (Click the Exhibit button.)
You need to sort both sources in order to merge join the data.
What should you do?

A) Set the ISSorted property to True for the OLE DB, sort the data in the selct statement of the OLE DB source, and use sort transformation after the Excel source.
B) Set the IsSorted property to True for both the OLE DB and Excel sources.
C) Use the sort transformation after the OLE DB source and set the IsSorted property to True for the Excel destination.
D) Sort the data in the select statement of the OLE DB source and use sort transformation after the Excel source.

2. You build and deploy a SQL Server 2008 Integration Service (SSIS) package.
During package development, the XML configuration is not specified.
You need to apply the configuration file to the deployed package stored in files system.
Which parameter should you set in the DTEXEC command line utility?

A) /conf
B) /L
C) /CheckF
D) /Set

3. Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in this series. Information and details provided in a question apply only to the question.
You maintain the SQL Server 2008 Integration Services (SSIS) instance for a healthcare supply chain data management company. Your company uses third-party event based scheduler.
You develop a package named Memberinformation.dtsx and save it in the SSISpackages folder on the company network. You need to deploy this package to the ProdSSISpackages file system on the production server. You need to rename the package to Memberinformation_Prod.dtsx, and the event based scheduler will execute the renamed package.
Which command syntax should you use?

A) dtutil /FILE c:\ SSISpackages|Memberinformation.dstx /COPY SQL; Memberinformation.dtsx
B) dtutil /SQL Memberinformation /COPY DTS;c:\SSISpackages\Memberinformation
C) dtutil /FILE c:\ SSISpackages|Memberinformation.dstx /DestServer DBPROD /COPY SQL; Memberinformation.dtsx
D) dtexec /sq Memberinformation / verify (a400e860-38d5-11c5-11ce-ae62-08002b2b79ef)
E) dtexec /f "c: \ Memberinformation .dtsx" /1 " DTS.LogProviderTextFile;Y:\Loginformation.txt"
F) dtexec /sq Memberinformation /ser productionServer / va
G) dtutil /FILE c:\SSISpackages\Memberinformation.dtsx /COPY FILE;W: \ProdSSISpackages\ Memberinformation_Prod.dtsx
H) dtexec /f " Z:\ Memberinformation.dtsx " /conf "
Z: \Memberinformationconfig,cfg "

4. You create a SQL Server 2008 R2 Analysis Services (SSAS) solution.
You create a new data mining query for you instance.
Which tool should you use?

A) SQL Server Error and Usage Reporting.
B) SQL Server Configuration Manager.
C) SQL Server management Studio
D) SQL Server Business Intelligence Development Studio

5. You maintain a SQL Server 2008 Integration Services (SSIS) instancE.The instance stores packages in
a SQL Server 2008 instance.
You need to deploy the package of an instance of SSIS to another SSIS instance that stores its packages in
a shared directory.
What should you do?

A) Execute the dtexec command.
B) Execute the dtswizard command.
C) Execute the dtutil command.
D) Execute the dtexecui command.

Solutions: