A number of SQL Server Integration Services (SSIS) packages, which will be deployed to their catalog, is being developed by you. Each package contains a step through which you can download the sales transaction data; this is done with access to an FTP site. In order to access the site, you need to create project parameters that can store it’s username as well as the password.

What would be your approach to make sure that, when they are deployed, the username and password values are encrypted?

Every night, the SQL Azure data is imported into a data warehouse through the SQL Server Integration Services (SSIS) which was developed by you. There are many abbreviation variations and misspells on the SQL Azure data. The Fuzzy Lookup transformation was used by a developer to import the data; it essentially picked out the string which closely matched amongst the allowed values on a reference table. Since the number of rows is quite large, the Fuzzy Lookup transformation tends to pass a null value, in the case of no acceptable match being found. Much of the values are matched incorrectly since the current Fuzzy Lookup setting is put on a similarity threshold of 0.50.

What would be your approach to ensure that the Fuzzy Lookup transformation makes more accurate matches without affecting performance?

The SQL Server 2012 database engine instance is installed on the production server, and, a month later, the SQL Server 2012 Integration Services (SSIS) is then installed. With the use of the Project Deployment model, an SSIP project must be developed and deployed to the server. On top of that, there must be automatic cleaning of the Operations Log records present outside of the configured retention period.

What should your approach be to create the SSIS catalog present on the production server? (Every right answer offers a part of the solution. Pick all the relative answers)

The SQL Server Integration Services (SSIP) package, generated less than a year ago and deployed to the SSIS catalog, is starting to periodically fail. At times the AQL Agent schedule is starting on the package, other times the SSIS developer manually starts the package through the use of Object Explorer present in the AQL Server Management studio. Where will you be able to find the information that identifies the responsible user who is authenticated to start the package after every failure?

You are responsible to maintain the SQL Server Integration Services (SSIS) package which is developed by using the SQL Server 2008 Business Intelligence Development Studio (BIDS). Custom scripts are included in the package; however, these need to be upgraded. What is the tool that you should use to upgrade the package to SQL Server 2012?

A data warehouse containing two fact tables need to be designed. In that, the first table must contain the sales per month, while the other must contain the orders per day. There must be declarative enforcement of referential integrity. What will your approach be to design a solution which is able to join both the fat tables to a single time dimension?

SQL Azure hosts a data warehouse that you have to design; in the warehouse is included the factSales fact table and the dimDistrict and dimUser dimension tables. The records for every individual user granted to run reports for warehouse are present in the dimUser table whereas the information regarding sales districts is available in the dimDistrict table. Area supervisors, users from specific districts as well as from headquarters can all access the system. The table structure that you design should ensure that specific users are able to view the data for sales on specific districts while some users should be allowed to view the data for sales on multiple districts.

What would be your approach?

SQL Azure hosts a pre-existing data warehouse where you will be reviewing a customer dimension table’s design. Customer attributes, for example Postcode, is neglected in the current dimension design since it doesn’t permit the historical changes to remain. In order to redesign the dimension, you should enable the changes of full historical reporting to a number of customer attributes which includes Postcode.

What would be your approach?

The enterprise schema that you are designing should be able to merge data from three individual data marts. Amongst these, SQL Azure hosts one of the marts with most dimensions containing similar content and structure. However, each mart contains slightly different geography dimension. What would be your approach to designing a consolidated dimensional structure? Keep in mind that the structure should not only be easily maintainable, but it should also ensure that it represents the three prior solutions’ dimensional data.

A logging methodology is set up in order to facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages. The following requirements are set for the methodology:

• Simplification of the deployment process.
• Centralization of the logs present in the SQL Server.
• Availability of the log data through T-SQL or reports.
• Automation of the log archival must be done.
What would be your approach to configure a logging methodology? This configuration should also be able to meet the requirements as well as minimize the development effort and deployment amount.

The SQL Server Integration Services (SSIS) project that you are developing, not only uses the Package Deployment Model, but is also able to copy a great number of rows from the SQL Azure database. What would be your approach to ensure that, instead of having the project deployed to the SQL test server, it is deployed to the SSIS catalog present on the production server?

The SQL Server Integration Services (SSIS) package is being developed by you. A custom task component is then included to the project in order to process complex data which originates from the SQL Azure database. What would you do to make sure that the custom component is rightly deployed on the test environment?

The SQL Server integration Services (SSIS) package, stored in the file system, is developed and deployed. What would your approach be in order to execute the package without first moving it to the SSIS server? (Pick all the suitable answers, every right one is a full solution.)

You need to retrieve product data from two varying sources which is why you create the SQL Server Integration Services (SSIS) package. SQL Azure database hosts one of the sources. Every varying contributor requires products present in every individual source. One product table destination must be assigned so that the products saved for individual distributor source is combined for insertion.

What is the transformation that you decide to use in order to meet the requirement? (Pick all the suitable answers, every right one is a full solution.)

When developing the SQL Server Integration Services (SSIS) package, your requirement is that, during package execution, it should be able to alter a variable value. Keep in mind that this should be done through the minimum development effort.

What would be your approach?

The SQL Server Data Tools are being used to develop the SQL Server Integration Services (SSIS) project. The project’s first package that is created should be able to access a flat file through a project connection; this file should also be accessible through more packages. What is your approach to reuse and define the flat file connection is every project package?

Fuzzy Lookup transformation us used by the SQL Server Integration Services (SSIS) package that you are designing. There is no change in the reference data used in the transformation. What is your approach to reuse the Fuzzy Lookup match index to both reduce the maintenance as well as increase the performance?

The SQL Server Integration Services (SSIS) project that you use is kept in the SSIS catalog where an Environment needs to be defined. In order to enhance the project and add the Environment to it, what is the procedure that you deem best for use?

SQL Azure hosts a data warehouse in which the data is imported by the SQL Server Integration Services (SSIS) package developed by you. A Foreach container is used by the package in order to process text files in a folder. By using the Project Deployment model, the package, called by a number of SQL Server Agent jobs, must be deployed into an individual server. A different schedule is assigned to the execution of each job, which then passes a folder path towards the package. What would be the package configuration you use in order to accept the folder path from every job?

The SQL Server Integration Services (SSIS) package that you design should be able to upload a file in the ‘Orders’ table in SQL Azure database. The company’s requirements of the auditing policies are as follows:

• OrderLog is a dedicated SQL Server Log table where an entry must be written.
• The entry should be made immediately after the completion of the file upload task.
What is the handler that you use in order to meet the policy requirements of the company?

The SQL Server Integration Services (SSIS) package that you are editing contains only three Execute SQL tasks and none other. The TransactionOption property is changed to Set in all three Execute SQL tasks as well as the package. What would be your approach to ensure that all of the three tasks will revert their changes in case there is a failure with any of the Execute SQL tasks?

A Type 3 Slowly Changing Dimension (SCD) is implemented on an SQL Server Integration Services (SSIS) that you are forming. What is the component or task that you add to the package which will grant you permission for the SCD logic implementation?

The data from the data warehouse is imported from the SQL Server Integration Services (SSIS) package that you are developing; and in order to control the flow, you make an addition of the Execute SQL task. The simple INSERT statement should be executed through this task which has the below mentioned requirements:

• The string variable value should be used by the INSERT statement. StringVar is the name of the variable.
• OLE DB Connection Manager should be used by the Execute SQL task.

The only Parameter added in the Execute SQL task’s Parameter Mapping tab is StringVar. What is the SQL statement that you must use in order to configure the Execute SQL task’s SQLStatement property?

The Data Quality Client is being installed on the user desktop; however, in the process, you should make sure that the software component prerequisite is also installed. In order to meet this goal, what are the components that need to be present? (Pick all the suitable answers, every right one is a full solution.)

In order to store the master list of products, you decide to create a SQL Server Master Data Services (MDS) model. However, since you have to define each product’s sales manager, you should assign Product entity with an attribute. This attribute will also prevent in input of wrong sales manager values by users. Keeping this in mind, what is the attribute type that you decide to make?

The SQL Server Data Quality Services (DQS)’s Data Quality Server component installation is being completed. What would be your approach to ensure the completion of the post-installation configuration?

A company needs SQL Server Master Data Services (MDS) model that you are creating. A single data, containing manager-to-subordinate relationships, stores the company’s source data. What is the hierarchy that you decide to use in order to represent the company’s organizational structure?

As a data steward for a Business Intelligence project, your duty is to check the SQL Server table and find rows that are duplicated as well as discover output to CSV file. The project is supported by a Data Quality Services (DQS) knowledge base. What would be your approach to develop the CSV file while putting in the most minimal effort?

A SQL Server Master Data Services (MDS) environment is managed by you, and it’s repository contains a product data that a new application needs requires access to. What would be your approach to using the most minimal effort for development to make a solution for granting access to the application for the product data?

In order to change the pre-existing knowledge base, you decide to use the Data Quality Service (DQS) client application’s Knowledge Discovery feature. Out of the three columns present in the mapping configuration, only two are mapped to domains existing in the knowledge base. What would be your approach to finish the Team Type, the third column without a domain, column?

There was a failure in the production server when during the maintenance of a Data Quality Services (DQS) environment. In order to correct that, you set up a new server where the DQS databases were then restored and granted the appropriate permissions. However, issues were then experienced by the DQS users when connecting to the new Data Quality Server. What is the Surface Area Configuration property that you must enable so users are allowed to join with the new server?

No procession occurs when, occasionally, a SQL Server Integration Services (SSIS) package is executed by a job that is not finished. What would be your solution, that minimizes both development efforts as well as deployment, for ensuring that the package logging does occur?

The SQL Server Integration Services (SSIS) package that you are writing is able to transfer data from a legacy system, where the data integrity is quite poor. While the package discards wrong rows, this can be done by logging to a CSV file used for the purpose of audit. What would be your approach to creating the best technique that allows you to log the rows deemed invalid, all the while making sure the development effort is minimized?

To a test environment, you decide to deploy a new SQL Server Integration Services (SSIS) project. The project contains a package which makes use of a custom task component. What would be your approach to make sure that there is correct deployment of the custom task component on the test environment?

The project that you are deploying to the SQL Server Integration Services (SSIS) catalog needs to be done with the most minimal administrative effort. What would be your approach to ensuring that happens?

Standardizing the SQL Server Integration Service (SSIS) package logging methodology brings ease to package debugging. The requirements of the methodology are as follows:

• Configurable log details
• Centralized logging in SQL Server
• Automatic purge of older log entries
• Simple deployment
• Availability of log information through reports or T-SQL

What would be your approach to configuring a logging methodology? Keep in mind that this should also minimize the development effort as well as the amount of deployment which meeting the requirements.

The source data for a company whose model you’re creating, SQL Server Master Data Services (MDS) model, is saved in a single table possessing the manager-to-subordinate relationships. The company’s organizational structure must be represented by a hierarchy that you are required to create. What is the hierarchy type that you choose?

As a Business Intelligence project’s data steward, you are responsible for not only identifying SQL Server table’s duplicate rows, but also output discoveries to a CSV file. In order to support the project, a Data Quality Services (DQS) knowledge base has been made. What would be your approach to using the most minimal development effort and producing the CSV file?

In order to change the knowledge base already existing, you decide to use the Knowledge Discovery feature of the Data Quality Services (DQS) client application. While two columns in the mapping configuration are mapped to domains existing in the knowledge base, the third, called Group, is yet to be assigned a domain. What must you do in order to finish the Group column’s mapping?

The SQL Server Data Quality Services (DQS) that you are installing requires you to grant specific users access to the Data Quality Server. What is the SQL Server application that you must use?

The SQL Server Master Data Services (MDS) environment that you manage contains a product data in its repository. Access to this data must be granted to a new application which is why you’re required to create a solution that will grant the access in the most minimal development effort.
What would be your approach?

You are required to finish the post-installation configuration after installing the Data Quality Server component of SQL Server Data Quality Services (DQS). What would be your approach?