Q1. You are implementing a SQL Server Integration Services (SSIS) 2012 package that loads data from various flat files and a Windows Azure SQL Database database.
Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable as the transaction date.
You need to set the CurrentDate variable to the date stored in a control table of the Windows Azure SQL Database database when the package starts. You need to achieve this goal by using the least amount of development effort.
What should you use to set the variable?
A. an Expression task
B. an Execute SQL task
C. a Script component
D. a Script task
Answer: B
Q2. A SQL Server Integration Services (SSIS) package on a computer is failing periodically in production. The package was created less than one year ago and was deployed to the SSIS catalog.
Sometimes the package is started on a SQL Agent schedule; sometimes the package is started manually by an SSIS developer by using the Object Explorer in SQL Server Management Studio.
You need to identify the authenticated user responsible for starting the package each time it failed in the past.
Where can you find this information?
A. the SQL Server Log
B. the SSISDB.[catalog].[executions] view
C. the SSISDB.[catalog].[event_messages] view
D. the SQL Agent Job History
E. the SQL Agent Error Log
Answer: B
Q3. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse.
You are developing the part of the SSIS package that populates the ProjectDates dimension table. The business key of the ProjectDates table is the ProjectName column.
The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table:
ExpectedStartDate - New values should be tracked over time.
ActualStartDate - New values should not be accepted.
ExpectedEndDate - New values should replace existing values.
ActualEndDate - New values should be tracked over time.
You use the SSIS Slowly Changing Dimension Transformation.
You must configure the Change Type value for each source column.
Which Change Type values should you select? (To answer, drag the appropriate value from the list of values to the correct location or locations in the answer area.)
Answer:
Q4. You are developing a SQL Server Integration Services (SSIS) package that loads data into a data warehouse hosted on Windows Azure SQL Database.
You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column.
You need to develop a data flow that imports the data while meeting the requirements.
How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)
Answer:
Q5. You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.
Which data flow transformation should you use?
A. Audit
B. DQS Cleansing
C. CDC Splitter
D. Pivot
Answer: D
Q6. You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012.
Quality Assurance (QA) testers in the company must have permission to perform the following tasks on specific projects:
View and validate projects and packages
View Environments and Environment variables
Execute packages
You need to grant the minimum possible privileges to the QA testers.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. In the SSISDB database, add QA Tester logons to the ssis_admin role.
B. In the msdb database, add QA Tester logons to the db_ssisoperator role.
C. Grant Modify permission in the projects to the QA Tester logons.
D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments to the QA Tester logons.
E. Grant Execute permission in the projects to the QA Tester logons.
F. In the msdb database, add QA Tester logons to the db_ssisItduser role.
Answer: B,D
Q7. You are developing a SQL Server Integration Services (SSIS) package to insert new data into a data mart. The package uses a Lookup transformation to find matches between the source and destination.
The data flow has the following requirements:
. New rows must be inserted.
. Lookup failures must be written to a flat file.
In the Lookup transformation, the setting for rows with no matching entries is set to Redirect rows to no match output. You need to configure the package to direct data into the correct destinations.
How should you design the data flow outputs? (To answer, drag the appropriate transformation from the list of answer options to the correct location in the answer area.)
Answer:
Q8. You are preparing to install SQL Server 2012 Master Data Services (MDS).
You need to ensure that the database requirements are met.
What should you install?
A. Microsoft SharePoint Server 2010 Standard Edition SP1
B. Microsoft SharePoint Server 2010 Enterprise Edition SP1
C. SQL Server 2012 Data Center (64-bit) x64 on the database server
D. SQL Server 2012 Enterprise (64-bit) x64 on the database server
Answer: D
Explanation:
* Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012.
* SQL Server 2012 Enterprise features include Master Data Services:
Note:
* Microsoft SQL Server Master Data Services is a Master Data Management (MDM) product from Microsoft, which will ship as a part of the Microsoft SQL Server database.Originally code-named Bulldog, Master Data Services is the rebranding of the Stratature MDM product titled +EDM, which Microsoft acquired in June 2007. Master Data Services is architecturally similar to +EDM, with increased integration with other Microsoft applications as well as some new features. Master Data Services first shipped with Microsoft SQL Server 2008 R2.
Q9. You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse.
The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis.
You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum.
What should you do?
A. Add an OnError event handler to the SSIS project.
B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
C. Use an msi file to deploy the package on the server.
D. Open a command prompt and run the gacutil command.
E. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
F. Open a command prompt and run the dtutil /copy command.
G. Create a reusable custom logging component and use it in the SSIS project.
H. Configure the SSIS solution to use the Project Deployment Model.
I. Configure the output of a component in the package data flow to use a data tap.
J. Open a command prompt and run the dtexec /rep /conn command.
K. Open a command prompt and run the dtexec /dumperror /conn command.
Answer: I
Explanation:
References: http://technet.microsoft.com/en-us/library/hh230989.aspx
http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/ms162820.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx http://technet.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx
Q10. You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog. One of the steps in each package accesses an FTP site to download sales transaction data.
You create project parameters to store the username and password that are used to access the FTP site.
You need to ensure that the username and password values are encrypted when they are deployed.
What should you do?
A. Set the Sensitive property of the parameters to True.
B. Set the ProtectionLevel property of the package to EncryptSensitiveWithUserKey.
C. Change the parameters to package parameters.
D. Change the project to the Legacy Deployment model.
Answer: A