1 / 51

Implementing a Data Warehouse with SQL 2012

Implementing a Data Warehouse with SQL 2012. Exam 70-483. Question 1. You work as a senior database administrator at ProDataMan. The ProDataMan network has a single domain named ProDataMan.com . ProDataMan uses Microsoft SQL Server 2012 in their environment.

hiero
Download Presentation

Implementing a Data Warehouse with SQL 2012

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Implementing a Data Warehouse with SQL 2012 Exam 70-483

  2. Question 1 You work as a senior database administrator at ProDataMan. The ProDataMan network has a single domain named ProDataMan.com. ProDataMan uses Microsoft SQL Server 2012 in their environment. You are hosting a training exercise for Microsoft SQL Server 2012 administrators. You are currently discussing the Fuzzy Lookup transformation. Which of the following is TRUE with regards to the Fuzzy Lookup similarity threshold setting? (Choose all that apply.) A. Increasing the threshold can improve the speed of matching. B. Increasing the threshold can deteriorate the speed of matching. C. The nearer the value is to 1, the nearer the resemblance of the lookup value to the source value must be to qualify as a match. D. The nearer the value is to 1, the further the resemblance of the lookup value to the source value must be to qualify as a match. Answer: A,C

  3. Question 2 You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. You have recently installed SQL Server 2012, with SQL Server Integration Services (SSIS), in ABC.com’s environment. You are preparing to install the SSISDB catalog on the SSIS server. Which of the following is TRUE with regards to the SSISDB catalog? (Choose all that apply.) A. You can make use of SQL Server Management Studio to create the SSISDB catalog. B. You can make use of SQL Server Data Tools to create the SSISDB catalog. C. You can make use of Solution Explorer to create the SSISDB catalog. D. You can make use of Windows PowerShell to create the SSISDB catalog programmatically. Answer: A,D

  4. Question 3 You work as a senior database administrator at ABC.com. The ABC.com network has a single domain named ABC.com. ABC.com makes use of SQL Server 2012 in their environment. ABC.com has a SQL Server Integration Services (SSIS) package that is currently deployed on the file system. The package is configured to interact work together with an outside vendor data feed. It has been reported that the outside vendor data feed is erratic as a result of network failures and slow response times. You have been instructed to investigate the problem. Your solution must include information about the Start Time, End Time, Execution Result, and Execution Duration of the package. You start by creating a new project and adding the package to it. You then deployed the package to the msdb database. Which of the following actions should you take NEXT? A. You should consider querying the ExecutionlLog table. B. You should consider viewing the SSISDB catalog. C. You should consider viewing the SQL Agent Error Log. D. You should consider viewing the SQL Server Log. Answer: A

  5. Question 4 You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. It is reported that a SQL Server Integration Services (SSIS) package regularly stops working in production. The package is often started manually by an SSIS developer. You want to know who the authenticated user is. Which of the following actions should you take? A. You should consider making use of the catalog.packages SSISDB Database Transact-SQL statement. B. You should consider making use of the catalog.executions SSISDB Database Transact-SQL statement. C. You should consider making use of the catalog.environments SSISDB Database Transact-SQL statement. D. You should consider making use of the catalog.executables SSISDB Database Transact-SQL statement. Answer: B

  6. Question 5 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You have been instructed to upgrade SQL Server 2008 Business Intelligence Development Studio (BIDS) package to SQL Server 2012. The package has custom scripts configured. Which of the following actions should you take? A. You should consider making use of dtexecui.exe from the command prompt. B. You should consider making use of gacutil.exe from the command prompt. C. You should consider making use of dtutil.exe from the command prompt. D. You should consider making use of SSISUpgrade.exe file from the command prompt. Answer: D

  7. Question 6 You work as a database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. Subsequent to deploying a new SQL Server Integration Services (SSIS) project to the SSIS catalog, you want to output the data streaming via a number of data flows into text files for added examination. You have acquired a list of data flow package paths and identification strings of the various task components that requires examination. You want to make sure that the output files are created in the easiest way possible. To achieve this, you need to make use of three stored procedures in a specific sequence. Which of the following is the second stored procedure in the sequence? A. catalog.startup_package B. catalog.start_package C. catalog.validate_package D. catalog.create_execution Answer: D

  8. Question 7 You work as a database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. ABC.com has a dimensional database with conformed dimensions and surrogate keys. What is the reason for making use of conformed dimensions? A. It prevents a dimension from being used in multiple data marts. B. It links dimension and fact tables. C. It allows for a dimension to be used in multiple data marts. D. It is used to guarantee uniqueness and to improve performance. Answer: C

  9. Question 8 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing a utility that can be run from the command prompt to manage SQL Server Integration Services packages. Which of the following is the utility you are discussing? A. dbimpexp.exe B. dtutil.exe C. gacutil.exe D. dtexecui.exe Answer: B

  10. Question 9 You work as a database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You have received reports regarding a current existing SQL Server Integration Services (SSIS) package not completing its execution, and not transferring data. You have decided to make use of the Project Deployment Model. Which of the following is TRUE with regards to using the Project Deployment Model? A. CLR integration is required on the database engine. B. CLR integration is not required on the database engine. C. Events that are created by the package are captured automatically and saved to the catalog during execution. D. Packages are executed using the dtExec and DTExecUI execution utilities. Answer: A,C

  11. Question 10 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) package. You are including a custom task element to deal with complicated scientific information derived from a SQL Azure database. You want to test the custom task element prior to deploying it into the production environment. You have to make sure that the custom task element is properly deployed into the testing environment. Which of the following actions should you take? A. You should consider making use of dtexecui.exe from the command prompt. B. You should consider making use of gacutil.exe from the command prompt. C. You should consider making use of dtutil.exe from the command prompt. D. You should consider making use of SSISUpgrade.exe file from the command prompt. Answer: B

  12. Question 11 You work as a database administrator at ABC.com. The ABC.com network has a single domain named ABC.com. ABC.com makes use of SQL Server 2012 in their environment. You have recently created an SQL Server Integration Services (SSIS) project catalog that contains two Environments. A single Environment Variable, named ConnectionString, of type string is specified by each environment. The project also has a project Connection Manager configured to link up with the data warehouse. You have been instructed to make sure that deployed packages are executed using any of the specified environments. Which of the following suitably describes an Environment Variable? A. An environment variable specifies the data that will be used by a package. B. An environment variable can be used to manage Integration Services objects in the catalog. C. An environment variable defines a literal value that can be assigned to a parameter during package execution. D. An environment variable allows you to use folders to organize your projects and environments. E. An environment variable is a container of variables that can be referenced by Integration Services projects. Answer: C

  13. Question 12 You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You have recently created a SQL Server Integration Services (SSIS) package that is hosted by the file system. Subsequent to deploying the package, you want to execute it using a Transact-SQL statement. Which of the following is the Transact-SQL statement you should use? A. catalog.startup_package B. catalog.start_package C. catalog.validate_package D. catalog.create_execution Answer: B

  14. Question 13 You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are hosting a training exercise for Microsoft SQL Server 2012 administrators. You are currently discussing Multicast transformation. Which of the following is TRUE with regards to Multicast transformation? (Choose two.) A. It prevents a package from creating logical copies of data. B. It allows a package to create logical copies of data. C. It distributes its input to one or more outputs. D. It distributes its input to a single output. Answer: B,C

  15. Question 14 You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a data flow to load sales data into a fact table. You have configured a full cache mode Lookup Transformation in the data flow to find the product data for sales. Two tables host the lookup source for the product data. You then access the Connection Page of the Lookup Transformation Editor. Which of the following is a reason for doing this? A. To configure a suitable connection manager. B. To configure partial caching. C. To modify the SQL statement for the Lookup transformation. D. To specify the join between the source table and the reference table. Answer: A

  16. Question 15 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) package. You have decided to make use of Expression Task. Which of the following is TRUE with regards to Expression Task? A. It creates and evaluates expressions that set variable values at runtime. B. It provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides. C. It runs an application or batch file as part of a SQL Server Integration Services package workflow. D. It allows you to run SQL statements during package execution. Answer: A

  17. Question 16 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) project. You have created an initial package that is configured to include a connection manager that was created at the package level. The connection manager is used to access a flat file. Subsequent to creating more packages within the project, you change the package Connection Manager in the initial package to a project Connection Manager. Which of the following is a reason for doing so? A. It allows all packages in the project to access the flat file. B. It only allows the initial package in the project to access the flat file. C. It only allows the additional packages in the project to access the flat file. D. It prevents all packages in the project from accessing the flat file. Answer: A

  18. Question 17 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) package. The package will be configured to make use of the GenerateAndPersistNewIndex Fuzzy Lookup transformation option. Which of the following is the purpose of this option? A. It allows for a current index to be reused. B. It allows for a new index to be created, without saving it. C. It allows for a new index to be created and saved, without maintaining it. D. It allows for a new index to be created, saved, and maintained. Answer: C

  19. Question 18 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) project. The project must be included in the SSIS catalog. You need to make use of a stored procedure that creates an environment reference for a project in the SSIS catalog. Which of the following actions should you take? A. You should consider making use of the catalog.create_execution stored procedure. B. You should consider making use of the catalog.create_environment_variablestored procedure. C. You should consider making use of the catalog.create_environment_referencestored procedure. D. You should consider making use of the catalog.add_data_tap stored procedure. Answer: C

  20. Question 19 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing the use of Slowly Changing Dimension transformation Outputs. One of the output options causes Derived Column transformations to create columns for the expired row and the current row indicators. Which option is the output that causes this? A. Unchanged Output B. Inferred Member Updates Output C. Historical Attributes Inserts Output D. Fixed Attribute Output E. Changing Attributes Updates Output Answer: C

  21. Question 20 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) package. The package will be configured to import data into a SQL Azure hosted data warehouse. You have decided to make use of the Environment Variable package configuration as part of your solution. Which of the following is TRUE with regards to the Environment Variable package configuration? A. It stores the property path inside the package and the value outside the package. B. It stores both the property path and the value outside the package. C. It is used to fetch a value from a variable in a calling package. D. It is the most commonly used configuration type. Answer: A

  22. Question 21 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) package. Every evening data is copied to a directory automatically, via an external FTP procedure. You want to make sure that the package loads this data into a SQL Server destination table. You have configure the use of a Foreach ADO enumerator, and configured the ADO object source variable. Which of the following actions should you take NEXT? A. You should consider creating a task host container. B. You should consider creating a Sequence container. C. You should consider creating a For Loop container. D. You should consider creating a Foreach Loop container. Answer: C

  23. Question 22 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) package. The package will be configured upload a file to a table in a SQL Azure database. You need to make sure that the code includes an event handler that makes use of an executable to raise the event directly after it has finished running. Which of the following is the event handler that you should use? A. The OnPostExecute event handler. B. The OnTaskFailed event handler. C. The OnPreExecute event handler. D. The OnPostValidate event handler. Answer: A

  24. Question 23 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You have been asked to revise a SQL Server Integration Services (SSIS) package. The package only includes Execute SQL tasks. To make sure that, in the event of an Execute SQL task failing, changes made by all of the Execute SQL tasks is rolled back. To do this, you want to alter the TransactionOption property of the package. Which of the following is the value that must be set for the TransactionOption property of the package? A. NotSupported. B. Supported. C. NotRequired. D. Required. Answer: D

  25. Question 24 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) package. You have been instructed to make sure that the package implements a Slowly Changing Dimension (SCD) that creates new fields in the dimension record. Which of the following is the change type that you should use? A. Type 1 B. Type 2 C. Type 3 D. Type 4 Answer: C

  26. Question 25 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You have been instructed to install the SQL Server Data Quality Services (DQS). The SQL Server Data Quality Services (DQS) is made up of the Data Quality Server and Data Quality Client components. Which of the following describes the minimum requirements for installing the Data Quality Client? A. 2 GB RAM. B. .NET Framework 4.0. C. SQL Server 2012 Database Engine. D. Internet Explorer 6.0 SP1 or later. Answer: B,D

  27. Question 26 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing the use of Domain-based attributes in Master Data Services. Which of the following is TRUE with regards to using Domain-based attributes? (Choose all that apply.) A. It is an attribute with values that are populated by members from another entity. B. It is an attribute with values that are populated by members from the same entity. C. It allows users to enter invalid attribute values. D. It prevents users from entering invalid attribute values. Answer: A,D

  28. Question 27 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of installing the Business Intelligence features. You are currently performing the post-installation of the Data Quality Server component of SQL Server Data Quality Services (DQS). Which of the following actions should you take to conclude the Data Quality Server installation? A. You should consider executing the DQSInstaller.exe file. B. You should consider executing the SSISUpgrade.exe file. C. You should consider executing the dtexecui.exe file. D. You should consider executing the dtutil.exe file. E. You should consider executing the gacutil.exe file. Answer: A

  29. Question 28 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of developing a SQL Server Master Data Services (MDS) model. ABC.com’s source data is hosted by a single table. The table includes the manager-to-subordinate relationships. You now have to design a hierarchy that suits ABC.com’s organizational structure. Which of the following actions should you take? A. You should consider making use of a Recursive hierarchy type. B. You should consider making use of a Non-Mandatory Explicit hierarchy. C. You should consider making use of a Mandatory Explicit Hierarchy. D. You should consider making use of a Derived Hierarchy with Explicit Caps. Answer: A

  30. Question 29 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing data quality projects. Which of the following are benefits of data quality projects? (Choose all that apply.) A. It enables you to perform data cleansing on your source data by using the knowledge in a DQS knowledge base. B. It allows you to perform data matching on your source data by using the knowledge in a DQS knowledge base. C. It allows you to perform data matching on your source data by using the matching policy in a knowledge base. D. It enables you to perform data cleansing on your source data by using the matching policy in a knowledge base. Answer: A,C

  31. Question 30 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 Master Data Services (MDS) in their environment. You have been asked to make sure that a user, named Mia Hamm, has the ability to create a Subscription View in Master Data Services (MDS). Which of the following does Mia Hamm require? (Choose all that apply.) A. Write permissions. B. Model administrator permissions. C. Read permissions. D. Permission to access the Integration Management functional area. E. Read/Write permissions. Answer: B,D

  32. Question 31 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing the use of Recursive hierarchies in a SQL Server Master Data Services (MDS) model. Which of the following is TRUE with regards to Recursive hierarchies? A. Each leaf member can be included in the hierarchy only once. B. Consolidated members in the hierarchy tree do not have to contain leaf members underneath them. C. Recursive hierarchies cannot include circular relationships. D. All consolidated members must be included in a hierarchy. Answer: C

  33. Question 32 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing Data Quality Services (DQS) Knowledge Bases. Which of the following is TRUE with regards to DQS Knowledge Bases? A. It is a repository of knowledge with reference to your data that enables you to understand your data and maintain its integrity. B. It creates and evaluates expressions that set variable values at runtime. C. It runs an application or batch file as part of a SQL Server Integration Services package workflow. D. It allows you to run SQL statements during package execution. Answer: A

  34. Question 33 You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior administrators. You are discussing a SQL Server application that can be used to access, configure, manage, administer, and develop all components of SQL Server. Which of the following are you discussing? A. The Solution Explorer. B. The Object Explorer. C. The SQL Server Management Studio. D. The Template Explorer Answer: C

  35. Question 34 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing the use of Subscription Views in SQL Server Master Data Services (MDS). Which of the following describes the purpose of Subscription Views in SQL Server Master Data Services (MDS)? A. To ensure the quality and accuracy of your master data. B. To change the location of members in an explicit hierarchy. C. To view a list of leaf and consolidated members. D. It allows subscribing systems to view your data in the Master Data Services database. E. It indicates the version that users or subscribing systems should use. Answer: D

  36. Question 35 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are hosting a training exercise for ABC.com’s junior database developers. You are currently discussing an EventHandlerClass property that is used to retrieve or configure a value that indicates whether the container participates in transactions. Which of the following is the EventHandlerClass property that you are discussing? A. The TransactionOption property. B. The Executables property. C. The LoggingOptions property. D. The Variables property. Answer: A

  37. Question 36 ProDataMan.com has two database servers, named PDM-SR13 and PDM-SR14. You are in the process of creating a SQL Server Integration Services (SSIS) package that must be able to load data into a SQL Server table on PDM-SR13, and is executed on PDM-SR14. The package also contains a data flow. You have decided to make use of the OLE DB Destination with Fast Load to add a destination. Which of the following are fast load options that you can specify in the OLE DB Destination Editor for the destination? (Choose all that apply.) A. You can specify that a table-level lock for the duration of the bulk load operation must be acquired. B. You can specify that identity values from the imported data file must be discarded. C. You can specify that common values assigned by SQL Server must be used. D. You can specify that a null value must be retained during the bulk load operation. Answer: A,D

  38. Question 37 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for ABC.com’s database developers. You are currently discussing package configurations. Which of the following are benefits of package configurations? (Choose all that apply.) A. It beneficial when deploying packages to numerous diverse servers. B. It enables you to perform data cleansing on your source data by using the knowledge in a DQS knowledge base. C. It simplifies the transfer of packages from a development environment to a production environment. D. It allows you to perform data matching on your source data by using the matching policy in a knowledge base. E. It allows for packages to have greater flexibility. Answer: A,C,E

  39. Question 38 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing an event handler provided by Integration Services that is raised by an executable to establish if it should stop running. Which of the following is the event handler you are discussing? A. The OnPostExecute event handler B. The OnQueryCancel event handler C. The OnError event handler D. The OnProgress event handler Answer: B

  40. Question 39 You work as a database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. ABC.com has a dimensional database with conformed dimensions and surrogate keys. Which of the following is TRUE with regards to surrogate keys? A. It prevents a dimension from being used in multiple data marts. B. It is used for internal relationships between tables. C. It allows for a dimension to be used in multiple data marts. D. It serves as the identifiers to entities from the business itself. Answer: B

  41. Question 40 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) package. You have decided to make use of Script Task. Which of the following is TRUE with regards to Script Task? A. It creates and evaluates expressions that set variable values at runtime. B. It provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides. C. It runs an application or batch file as part of a SQL Server Integration Services package workflow. D. It allows you to run SQL statements during package execution. Answer: B

  42. Question 41 You are running a training exercise for ProDataMan.com’sdatabase developers. You are currently discussing the transformations data flow component. You are highlighting the Pivot Transformation type. Which of the following is TRUE with regards to the Pivot Transformation type? A. It is used to obtain a randomly selected subset of an input dataset. B. It sorts input data in ascending or descending order and copies the sorted data to the transformation output. C. It makes a normalized data set into a less normalized but more compact version. D. It creates new column values by applying expressions to transformation input columns. E. It creates a sample data set by selecting a percentage of the transformation input rows. Answer: C

  43. Question 42 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) data flow. The data flow must be configured to make data from various external data sources available to the other components in the data flow. Which of the following actions should you take? A. You should consider making use of the expressions data flow component. B. You should consider making use of the source data flow component. C. You should consider making use of the transformations data flow component. D. You should consider making use of the destination data flow component. Answer: B

  44. Question 43 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of designing a SQL Server Integration Services (SSIS) package that is saved in the file system. You want make use of the command prompt to load the package. Which of the following actions should you take? A. You should consider making use of dtutil /Fi from the command prompt. B. You should consider making use of dtexec /ISServer from the command prompt. C. You should consider making use of dtexec /f from the command prompt. D. You should consider making use of dtutil /Si from the command prompt. Answer: C

  45. Question 44 You work as a database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You have received reports regarding a current existing SQL Server Integration Services (SSIS) package not completing its execution, and not transferring data. You have decided to make use of the Package Deployment Model. Which of the following is TRUE with regards to using the Package Deployment Model? A. CLR integration is NOT required on the database engine. B. Environment-specific parameter values are stored in environment variables. C. Events that are created by the package are captured automatically and saved to the catalog during execution. D. Events that are created by the package are NOT captured automatically during execution. Answer: A,D

  46. Question 45 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are hosting a training exercise for ABC.com’s junior database developers. You are currently discussing the Fuzzy Lookup transformation. You want to show them how to set parameters for the fuzzy lookup. Which tab of the Fuzzy Lookup Transformation Editor should you access? A. The General tab. B. The Advance tab. C. The Columns tab. D. The Reference Table tab. Answer: B

  47. Question 46 You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior administrators. You are discussing a tool that executes scheduled administrative tasks in SQL Server 2012. Which of the following is the tool you are discussing? A. Object Explorer B. SQL Server Agent C. Template Explorer D. Solution Explorer Answer: B

  48. Question 47 You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are in the process of creating a SQL Server Integration Services (SSIS) package. The package will be configured upload a file to a table in a SQL Azure database. You need to make sure that the code includes an event handler that makes use of an executable to raise the event when its verification is completed. Which of the following is the event handler that you should use? A. The OnPostExecute event handler. B. The OnTaskFailed event handler. C. The OnPreExecute event handler. D. The OnPostValidate event handler. Answer: D

  49. Question 48 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for ABC.com’s database developers. You are currently discussing the transformations data flow component. You are highlighting the Derived Column type. Which of the following is TRUE with regards to the Derived Column type? A. It is used to obtain a randomly selected subset of an input dataset. B. It sorts input data in ascending or descending order and copies the sorted data to the transformation output. C. It makes a normalized data set into a less normalized but more compact version. D. It creates new column values by applying expressions to transformation input columns. E. It creates a sample data set by selecting a percentage of the transformation input rows. Answer: D

  50. Question 49 You work as a senior database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment. You are running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing the use of Slowly Changing Dimension Transformation Outputs. One of the output options are used to update the record in the lookup table. Which of the following is the output option? A. Unchanged Output B. Inferred Member Updates Output C. Historical Attributes Inserts Output D. Fixed Attribute Output E. Changing Attributes Updates Output Answer: E

More Related