Review Questions

1.	You’ve been tasked with writing a standard for package security. You were able to gather the following background information: packages will be deployed to the file system, only sensitive data within the packages needs to be secured, and packages might be executed by multiple users. Which security option would you select from the following? Do Not Save Sensitive Encrypt All with Password Encrypt All with User Key Encrypt Sensitive with Password Encrypt Sensitive with User Key Rely on Server Storage for Encryption
2.	Your packages need to be able to resume at the point they failed. Select all of the options that need to be enabled. Set the CheckPointFileName to any folder and filename. Set CheckPointShouldBeUsed to True. Set CheckPointSave to True. Set CheckPointUsage to Never. Set ForcedExecutionResult to Failed. Set CheckPointUsage to IfExists. Set CheckPointFileName to restart.xml. Set SaveCheckPoint to True.
3.	You’re trying to set up a checkpoint on a conditional split, but you can’t find the properties you need. What is the most likely problem? You need to right-click the green line. Checkpoints are available only in the Enterprise Edition. Checkpoints can be set only on the control flow. Checkpoints can be set only on the data flow.
4.	Which two of the following are valid techniques for scheduling execution of an SSIS package? Click the Scheduling tab of the deployment wizard, and set up a custom schedule. Create a job in SQL Agent with the type set to SSIS Package. Create a job under the legacy node of SSMS. Execute the package using DTExec.exe with a command-line parameter of /SCHEDULE. Create a task in the Windows Task Scheduler that uses DTExec.exe to execute the package.
5.	You’ve been tasked with selecting a security mechanism that will allow your company to guarantee that you can detect whether a deployed package has changed. Which one of the following options would be the best solution? Enable the Rely on Server Storage for Encryption setting. Enable the Encrypt All with User Key setting. Enable the Encrypt All with User Key of the DBA setting. Digitally sign the package. There is no way to tell whether a package has been changed.
6.	You have a package that has checkpoints enabled. You have a production package that failed last night that you want to run from step 1 rather than from the checkpoint. Which of the following is the best way to achieve that? Edit the package, and set SaveCheckPoint to False. Edit the package, and set CheckPointFileName to an empty string. Edit the package, set SaveCheckPoint to False, and then set CheckPointFileName to an empty string. Look up the name and path of the CheckPointFileName property in the package, and then delete that file. It is not possible to bypass the checkpoint recovery.
7.	What action do you need to take to create a deployment manifest? No action is required. It is created automatically when you build the package. Set the CreateDeploymentUtility property of the deployment task to True. Set the CreateDeploymentUtility property of the project to True. Change the deployment output path.
8.	Package configurations can be stored in which of the following ways? (Choose five.) Registry XML file Environmental variable Parent package variable SQL Server Inside the package Remotely using the MS.Config web service
9.	Which of the following items can be changed using the Execute Package Utility? Configurations Connection managers Verification options Logging All of the above None of the above
10.	You need to process all the files in a folder with the extension of .csv. For each file you process, you will need to look up the correct city name based on the ZIP code and then write the results to a file that will be used by a different SSIS package. Which of the following would best accomplish that? Use a ForEach loop container set to Process Only *.csv Files. Add a data flow task inside the loop. On the data flow, add a Flat File source, a lookup task, and a Raw File output. Use a For loop container set to Process Only *.csv Files. Add a Data Flow task inside the loop. On the data flow, add a Flat File source, a lookup task, and a Raw File output. Use the File System task to retrieve all *.csv files, use the Union All transform to concatenate into one big file, and then write that file to disk using an SSIS TempFile destination. Use the Script task to loop through the files in the folder, and then loop through each of the files writing a new temp.csv file line by line. No other tasks or transforms are needed to accomplish this task.
11.	Your manager has asked you to integrate some existing DTS packages into a new project you’ll be starting on. Which of the following would be the best way to proceed, given that you are on a tight deadline? Use the Execute Process task. Use the Execute SQL task to call the package directly. Use the Execute Package task. Rewrite the package in SSIS, because SSIS cannot call DTS packages. Rewrite the package in SSIS, because it will be a cleaner solution.
12.	A number of tasks can start with Transfer: Transfer Logins, Transfer Jobs, and Transfer Databases. What are these tasks most commonly used for? They automate installs of SQL Server. They are not used; they are legacy items from DTS. They are used when the cluster fails over. They are used by maintenance plans.
13.	If you were working on a package that would access SQL Server only and needed to add a column to the data set, which of the following options would work? (Choose two.) Use the Import Column transform. Use the Export Column transform. Build the derived column in the SELECT statement. Use the Derived Column transform.
14.	A co-worker has completed a package and sent to you for deployment. The files include new-employees.dtsconfig, newemployees.SSISDeploymentManifest, and newemployees.dtsx. Based on those files, which of the following are true? (Choose three.) Your co-worker has specified a configuration file (newemployees.dtsconfig). You are missing the security.ini file. The SSIS deployment manifest can be used to deploy the package without having to reopen it in BIDS. Your co-worker has created the package using DTS compatibility mode; otherwise, the package would be named newemployees.ssis. You can view all of these views by opening them in Notepad because they are all XML files.
15.	Which of the following will make it easier for someone else to maintain a package you created? All task and transforms relabeled with meaningful names Transactions used if appropriate Checkpoints used to allow restarting the package Checkpoints used after each control flow step Additional text annotations used to explain key pieces of logic Checkpoints used to allow restarting the package after steps that are resource intensive
16.	A new employee has asked you to give him a very high-level overview of the differences between DTS and SSIS because he has some experience with DTS. Which of the following statements would help him understand? (Choose four.) DTS packages can be executed by themselves or as part of an SSIS package with no changes on a SQL Server 2005 server. DTS packages have to be converted using the SSIS DTS Wizard before they will run on SQL Server 2005. DTS used Enterprise Manager to create and administer packages; in SQL Server 2005, you use BIDS for design and Enterprise Manager for deployment. DTS used Enterprise Manager to create and administer packages; in SQL Server 2005, you use BIDS for design, SSMS for administration, and the DTExec.exe utility for deployment. SSIS does not require SQL Server to be installed to work, and it runs as a service. SSIS requires SQL Server to be installed on the same machine, and packages are executed by the SQL Server service. SSIS packages cannot be saved to Visual Basic code, but C# is OK. SSIS packages are saved as XML files and cannot be saved as code. Enterprise Manager must be loaded to edit a DTS package.
17.	Which control lets you send its output to more than one destination? Data Conversion Merge Join Multicast Conditional Split
18.	A talented co-worker has written some script for you that has been placed into a Script task that you have added to your package, but now the package is failing. Which of the following debugging steps listed are worth trying? (Choose two.) Remove the Script task from the package, and reexecute. Set a breakpoint on the first line of script, and then step through the code in the Visual Studio environment until you reach an error or the entire script has been executed. Set the Break on All Errors property of the project, and then run the package. Roll back to the previously saved version, and add the Script task again; sometimes it fails on the first attempt.
19.	Your manager has asked you to schedule a package to run once per day and to automatically notify her if the package fails. All packages at your company are stored in SQL Server and executed by the SQL Agent. What would be the easiest way to notify her of a failure? Add a Send Mail task to the end of the data flow. Add a Send Mail task to the end of the control flow. Set the SQL job to notify your manager on failure only. Build a second job that checks the error log and emails the manager if “FAILURE” is included in the error log.
20.	You have selected the Rely on Server Storage for Encryption option for security. Which of the following are true? (Choose three.) There is no such setting; you have to specify either user key or password encryption for securing packages. The package will be encrypted and stored in the sysdtspackages90 table. The package will be stored in the sysdtspackages90 table, but it will not be encrypted. Access to the package will be controlled by roles, which by default have db_dtsadmin, db_dtsoperator, and the package creator in the reader role and have db_dtsadmin and the package creator in the writer role. Access to the package will be controlled by roles, which by default have db_dtsadmin, db_dtsexecute, and the package creator in the execute role and have db_dtsadmin and the package creator in the admin role. SQL roles do not apply to packages stored in the file system. SQL roles do not apply to any packages.

Answers

1.	D. Option A would not help because it would remove all the sensitive data that would most likely be needed to execute the package. Options C and E would work only for the user that saved the package, and option F does not work for the file system. That leaves you with options B and D, with D being the better choice in this case because you want to encrypt only the sensitive portions of the package.
2.	A, F, H. For checkpoints to work, you need to set a CheckPointFileName property (it’s blank by default), set SaveCheckPoint to true, and set CheckPointUsage to IfExists. Combined, they tell SSIS that the checkpoint is enabled and to use the file if it is present on disk. Checkpoint files can be named anything, but an .xml extension is common.
3.	C. This is one to watch out for: checkpoints work only in the control flow. If you want to have checkpoints in the data flow, break up the data flow into two or more different data flows, which allows you to put a checkpoint between them on the control flow.
4.	B, E. There are only two standard ways to schedule a job of any kind-using SQL Agent or using Task Scheduler. Neither the deployment wizard nor DTExec.exe allows you to set a schedule.
5.	D. Digital signatures when combined with setting the verification option of the package to require them are the only guaranteed way to detect whether a package has changed. Note that you could certainly do a diff between two versions of a DTSX file to see any changes, but that would not enforce the rule that it cannot run if changed.
6.	D. Deleting the checkpoint file is the best way because it requires no changes to the package, which is handy if it was built with a digital signature, for example; however, even without that change, the package introduces the chance that once you’ve recovered from the problem, you might not restore the package to its original state.
7.	C. Deployment manifests are not created by default; you have to enable them by setting the CreateDeploymentUtility property to True. Changing the deployment path would change where you would find the manifest, but it doesn’t control whether it’s created.
8.	A, B, C, D, E. You get a rich set of options for saving package configurations. Configurations sit outside the package so that you can change them, or they swap between two or more configurations, without actually changing the code flow in the package. There is no option to store the configuration inside the package, and there is no option called MS.Config.
9.	E. The Execute Package Utility lets you manage configurations, connection managers (directly, without even needing to set up a configuration), verification options such as requiring the digital signature to match, and logging.
10.	A. The ForEach loop container is the best choice because it natively supports processing the file system, and then in the data flow you need a Flat File source, a lookup task (to get the city name), and a Raw File destination. Raw files are a native format used by SSIS that avoids the logging overhead of writing staging data to a SQL Server table.
11.	C. The best and quickest way is via the Execute Package task. SSIS can execute DTS packages without any changes (but remember you can modify them only if you have a SQL Server 2000 instance installed or you installed the designer separately).
12.	D. It is certainly possible to use them for automating installs (or more accurately, post-install configuration), but the most common use is for maintenance plans generated by the Maintenance Plan Wizard. This is a big change from SQL Server 2000, and this finally gives the DBA a chance to truly customize maintenance plans without having to write entirely custom solutions.
13.	C, D. It’s usually going to be faster and cleaner to create the derived column in your SELECT statement, but it’s certainly valid to do it with the Derived Column transform. Import and Export Columns transforms are specialized transforms that work with files that are associated with rows of data.
14.	A, C, E. Configuration files typically have the .dtsconfig extension (but you could use something different), and manifests have the SSISDeploymentManifest extension. All three types (configuration, manifest, and package definition) are stored on disk as XML files. There is no DTS compatibility mode.
15.	A, E. Documenting how and why a package works is the best way to make it easier for someone else to maintain a package. In SSIS, you best accomplish this by renaming tasks and transforms and adding text annotations. Transactions and checkpoints should be when used if the project requires them but do not directly help another person understand the implementation of the package.
16.	A, D, E, H. A key part of upgrading to SQL Server 2005 is that very little breaks; in particular, you know that DTS packages will continue to execute as before. A big change was splitting the DTS functionality of Enterprise Manager into multiple applications: BIDS, SSMS, and DTExec.exe. It’s all XML now, with no saving to language files of any type. And finally, there is no requirement to load Enterprise Manager to edit DTS (but you do need either a SQL Server 2000 instance or the DTS Designer installed).
17.	C. The Multicast transform is the unusual one; the other transforms have one output. Although it’s not part of the question, remember that the Union All transform takes multiple inputs (but has a single output).
18.	A, B. If you can do it and still have a working package, option A is the quickest way to prove the problem is in the Script task. Answer B will work but assumes that it was the talented co-worker who erred rather than you! Using the breakpoint in the Visual Studio environment should take you to the exact point of the problem.
19.	C. You could use a Send Mail task, but you would have to make it conditional on something to work only on failure-so either an error generated from the task or transform or a value you could look up. Building a second job would be a lot more work and not as good as just relying on the built-in functionality of the SQL Agent (which can always notify, notify on success, or notify on failure).
20.	C, D, F. Server storage is not usually encrypted. Instead, you secure it with SQL permissions, and the default settings use the built-in roles (db_dtsadmin, db_dtsoperator), but you can build other roles if needed. Option F is correct but doesn’t really apply here since you know you’re not storing packages in the file system. Option E is incorrect because there is not a role called db_dtsexecute