Q1. You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012.
Package developers in your company must have permission to perform the following tasks only on their own projects:
View projects and packages View Environments Validate packages Execute packages
You need to grant rights to the developers without assigning unnecessary privileges.
What should you do? (Each correct answer presents part of a solution. Choose all that apply.)
A. Add developer logins to the db_ssisltduser role in the msdb database.
B. Add developer logins to the db_ssisoperator role in the msdb database.
C. Grant Execute permission in the projects for the developer logins.
D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments.
E. Add developer logins to the ssis_admin role in the SSISDB database.
F. Grant Modify permission in the projects for the developer logins.
Answer: B,D
Explanation: B: db_ssisoperator
* Read actions Enumerate all packages. View all packages. Execute all packages. Export all packages.
Execute all packages in SQL Server Agent.
* Write actions None
D: Need read permissions on the Environments in order to be able to view them.
Incorrect:
Not A: db_ssisltduser
* Read actions
Enumerate own packages.
Enumerate all packages.
View own packages.
Execute own packages.
Export own packages.
* Write Actions
Import packages.
Delete own packages.
Change own package roles.
Not E: db_ssisadmin
Too many permissions (such as delete all packages).
Q2. You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS
catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project.
Which stored procedure should you use?
A. catalog.create_environment_variable
B. catalog.create_environment_reference
C. catalog.set_execution_parameter_value
D. catalog.set_environment_variable_value
Answer: B
Q3. You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.
All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed.
You need to develop this project with the least amount of development effort.
What should you do? (Each answer presents a part of the solution. Choose all that apply.)
A. Create a package parameter named ConnectionName in each package.
B. Edit each package Connection Manager. Set the ConnectionName property to @[$Project::ParamConnection].
C. Edit the project Connection Manager in Solution Explorer. Set the ConnectionName property to @ [$Project::ParamConnection].
D. Set the Sensitive property of the parameter to True.
E. Create a project parameter named ConnectionName.
F. Set the Required property of the parameter to True.
Answer: B,E,F
Explanation: B: From question: " The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed."
E: SSIS 2012 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.
F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.
Note:
* Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.
Reference: Integration Services (SSIS) Parameters
Q4. A SQL Server Integration Services (SSIS) package named DataFeed interacts with an external vendor data feed. The package is executed several times a day, either as part of other packages' control flow or by itself. The external data feed is unreliable because network failures and slow response times are frequent. The package is currently deployed on the file system.
To analyze the reliability of the external data feed, you must collect execution data.
Every time the DataFeed package is executed, the following information must be logged:
Start Time
End Time
Execution Result
Execution Duration
You need to design a logging solution that meets the requirements by using the least amount of administrative and development effort.
Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Answer:
Q5. You are building a fact table in a data warehouse.
The table must have a columnstore index. The table cannot be partitioned.
You need to design the fact table and load it with data.
Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Answer:
Q6. You are developing a SQL Server Integration Services (SSIS) package.
The package uses custom functionality that accesses a SQL Server database. The custom
functionality must be implemented by using Language Integrated Query (LINQ).
You need to ensure that the LINQ code can be debugged at design time.
What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the answer area.)
Answer:
Q7. You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours.
Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.
You need to partition the table to maximize the performance of queries. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Partition the fact table by month, and compress each partition.
B. Partition the fact table by week.
C. Partition the fact table by year.
D. Partition the fact table by day, and compress each partition.
Answer: D
Q8. Your company uses a proprietary encryption algorithm to secure sensitive data. A custom cryptographic assembly was developed in Microsoft .NET and is used in several applications.
A SQL Server Integration Services (SSIS) package is importing data into a Windows Azure SQL Database database. Some of the data must be encrypted with the proprietary encryption algorithm.
You need to design the implementation strategy to meet the requirements while minimizing development and deployment effort and maximizing data flow performance.
What should you do?
A. Create a SQL Common Language Runtime (SQLCLR) function that uses the custom assembly to encrypt the data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.
B. Use an SSIS Script transformation that uses the custom assembly to encrypt the data when inserting it.
C. Create a SQL Common Language Runtime (SQLCLR) stored procedure that uses the custom assembly to encrypt the data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.
D. Use an SSIS Script task that uses the custom assembly to encrypt the data when inserting it.
Answer: B
Q9. You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data.
The data source will be polled once per hour. The data is updated with multiple important status changes per minute.
For each captured data change, the before and after values must be included.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option in the dialog box in the answer area.)
Answer:
Q10. You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.
The reference data to be used in the transformation does not change.
You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance.
What should you do?
A. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
C. Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.
D. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
E. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
Answer: A
Reference: http://msdn.microsoft.com/en-us/library/ms137786.aspx