Question 6 of 41

DRAG DROP -

You are designing an extract, transform, load (ETL) process with SQL Server Integration Services (SSIS). Two packages, Package A and Package B, will be designed. Package A will execute Package B.

Both packages must reference a file path corresponding to an input folder where files will be located for further processing.

You need to design a solution so that the file path can be easily configured with the least administrative and development effort.

Which four 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.)

Select and Place:

    Correct Answer:

    References: http://msdn.microsoft.com/en-us/library/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx

Question 7 of 41

DRAG DROP -

You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch record by using an Execute SQL task named Create Batch. After successfully loading the product data, you use another

Execute SQL task named Set Batch Success to mark the batch as successful.

You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails.

Which three steps 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.)

Select and Place:

    Correct Answer:

    References: http://msdn.microsoft.com/en-us/library/ms141003.aspx http://msdn.microsoft.com/en-us/library/ms141261.aspx http://msdn.microsoft.com/en-us/library/ms141722.aspx http://msdn.microsoft.com/en-us/library/ms139895.aspx

Question 8 of 41

Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and nothing is processed.

You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.

What should you do?

    Correct Answer: J

    To ensure that package logging occurs while minimizing deployment and development efforts, you should run the package by using the dtexecui.exe utility and the SQL Log provider. This approach leverages existing logging mechanisms without requiring additional custom development or complex deployment steps. Running the package with dtexecui.exe allows you to specify logging options easily, ensuring that logs are generated for each execution without manual intervention for subsequent runs.

Question 9 of 41

DRAG DROP -

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.)

Select and Place:

    Correct Answer:

    Only nonclustered columnstore indexes are available in SQL Server 2012 so when the index is created, you cannot update the table (without using partition switching). Therefore you have to load the data BEFORE creating the index.

    In SQL Server 2014, you could create a clustered columnstore index which would allow updates to the table after the index is created.

Question 10 of 41

You are adding a new capability to several dozen SQL Server Integration Services (SSIS) packages.

The new capability is not available as an SSIS task. Each package must be extended with the same new capability.

You need to add the new capability to all the packages without copying the code between packages.

What should you do?

    Correct Answer: C

    To add a new capability to multiple SSIS packages without duplicating code, developing a custom task is the most appropriate solution. A custom task in SSIS allows you to encapsulate the new functionality in a reusable unit, which you can then add to any package as needed. This approach ensures maintainability and consistency, as you only need to develop and update the custom task once, and all packages using it will benefit from any updates automatically. Custom components are generally used for data flow transformations, while script tasks and components are more suited for simple or one-off needs rather than a widespread implementation like this.