Question 1
You are asked to combine data from an Excel workbook and a database table and then push the results to a fixed-width flat file. Can you accomplish this task by using the Import And Export Wizard? Answer.
No. The Import And Export Wizard lets you work with only a single source and a single destination. To combine data merging or data cleansing tasks, you need to either create a new package specifi cally for that purpose or modify a package previously created by the wizard.
Question 2
You need to create both SSIS packages to process your data and SSAS cubes to perform analysis. Can you create both objects in a single project? Answer.
No. you cannot create both SSIS and SSAS objects in one project because BIDS does not let you combine objects used for different platforms. you need to build two separate projects in BIDS: one for the SSIS packages and another for the SSAS cubes and dimensions.
Question 3
What is the difference between a project data source and a package connection? Answer.
Both project data sources and package connections are connection strings. However, a data source resides outside the package and can be used as the connection reference for more than one package. A package connection does not have to be associated with a data source.
Question 4
If a connection references a data source and the data source is changed, when will the connection be updated? Answer.
Connections are updated by their associated data sources only when the package is opened for editing in BIDS.
Question 5
What is the difference between a control flow task and a control flow container? Answer.
Control flow tasks perform operations, whereas containers coordinate and group tasks. for example, a foreach Loop Container can look through the files in a system folder, and a File System Task embedded within the container can then move the files to a new folder location.
Question 6
To run a stored procedure within a SQL Server database, what task would you choose? Answer.
The Execute SQL Task can run a stored procedure within SQL Server or any relational database for which you have an installed data provider. The syntax of the statement entered in the Execute SQL Task will be in the native language of the underlying database.
Question 7
How would you use SSIS to import a file from an FTP server to a SQL Server table? Answer.
First, you would use an FTP Task to copy the file to the machine on which SSIS is installed. you can then import the file into a SQL Server table by using a Data Flow Task configured with a flat file Source adapter and either a SQL Server Destination adapter or an OLE DB Destination adapter.
Question 8
You need to migrate a user-created Access database to SQL Server, but the Data flow toolbox does not contain an Access source adapter. How do you import this data into SQL Server? Answer.
Although not listed in the toolbox, Access is one of the many database sources and destinations that SSIS works with. To extract data from Access, you first need to make a package connection to the Microsoft Jet OLE DB Provider. You can then use the OLE DB Source Adapter to select the table or perform a custom query.
Question 9
The Multicast Transformation and the Conditional Split Transformation both can have multiple outputs. Which transformation would you use if you needed to send rows matching certain conditions out one output and rows matching different conditions out another? Answer.
The Conditional Split Transformation lets you defi ne expressions against which the rows from the source are evaluated. for every row, the expressions are evaluated in order, and a row is sent out the first output when the matching expression evaluates to True. Therefore, any single row can go out only one output. With a Multicast Transformation, on the other hand, all rows go out every output.
Question 10
Describe the transformations you could use to combine data from two different database tables that exist on two different servers based on matching columns. Answer.
To combine data from two different database tables that exist on two different servers, you could use the Merge Join Transformation, which combines datasets by joining the rows across a set of common keys. This transformation allows an inner join, a left outer join, or a full outer join. you could also use a Lookup Transformation to associate data from two sources. The Lookup can cache a table in memory and, through matching columns, can return new columns to the data flow.
Question 11
You add a Sequence Container to a package that contains several tasks, one of which calls a command on a legacy system and another of which — A Data flow Task—imports data into SQL Server. Both tasks have the Transactionoption property set to Required. Even with the MSDTC service started and transactions turned on, your Sequence Container fails before the tasks even run. What is the problem? Answer.
The transactions featured in SSIS use the MSDTC service. However, not all systems support MSDTC, and a transaction cannot be forced on a noncompliant system, so the container will fail. you should remove the legacy task from the Sequence Container that has the transaction or set the Transactionoption property to not Supported.
Question 12
What must you set to use checkpoint properties at the package level after you have turned on the checkpoint properties? Answer.
You need to set the FailPackageOnFailure property to True for tasks to write to the checkpoint file. However, if you want to rerun any successful tasks that occur before the failed task, you need to use a Sequence Container around the group of related tasks that require transactions.
Question 13
When a package fails while you are developing it, where should you look to identify what happened? Answer.
The Progress or Execution Results tabs in the SSIS Designer show package execution details, including any warnings that were displayed or errors that occurred during execution. often, you will need to scroll through the results and look for the errors and their descriptions. A single error might produce multiple error messages.
Question 14
You have a package that includes a step that occasionally fails because of network connectivity problems. When a network connectivity error occurs, you need to perform an alternative step to run the same operation in a slower but more reliable way. At the completion of the alternative step, you would like to run the next step in the original workflow. How can you accomplish this? Answer.
From the first task, create a red failure precedence constraint to the alternative task. You then need to create Success constraints from both the alternative task and the original task to the third task. you need to set the Success constraints to Logical OR so that when either the first task or the second task is successful, the final task will run.
Question 15
A Data Conversion Transformation is failing in the middle of the data flow execution, and you need to determine what is causing the error. How should you proceed? Answer.
To determine what is causing the error, configure the Data Conversion Transformation error path to flat file so that any rows that are failing conversion are sent to a file. Then create a data viewer on the error path, and run the package in BIDS. This technique will capture the errors in a file and display the rows in the SSIS Designer for troubleshooting.
Question 16
You would like to log all the package errors to a custom database table that you have created for auditing purposes. How can you accomplish this task? Answer.
By using the OnError event handler assigned to the package level, you can also use an Execute SQL Task that calls a stored procedure, passing in the SourceName and ErrorDescription variable values. The procedure can then track these details into a metadata storage table for auditing.
Question 17
What are some reasons to use package configurations in your SSIS architecture? Answer.
SSIS package configurations are valuable when you have an SSIS environment in which you need to deploy packages from one server to another and the properties of those packages, such as their connections, need to be updated.
Question 18
When does a package read and use the configuration entries? Answer.
Configuration entries are read at the start of the package execution. In BIDS, configurations are applied only when you execute a package in the debug mode; they are not applied when you are designing your package.
Question 19
WWhen are property expressions evaluated as a package is running? Answer.
Unlike configuration entries that are read at the start of the package execution, property expressions are updated when the property is accessed by the package during package execution. A property expression can change the value of a property in the middle of the package execution, and the new value is read when the property is needed by the package.
Question 20
What items are created when the CreateDeploymentutility property for the SSIS project is set to True and the project is built? Where can you fi nd these items? Answer.
A copy of all the packages, XML configuration files, the project’s miscellaneous files, and the installer kit configuration file are all created with the deployment utility. These files are copied to the location specifi ed in the project’s DeploymentoutputPath property. The default value is [project path]\bin\Deployment, where [project path] is the location of the project you are working on.
Question 21
What are the two types of destinations to which an installer kit can deploy SSIS packages? Answer.
SSIS packages can be deployed to either a file system or a SQL Server database, and the installer kit will prompt you to choose one of these.
Question 22
Can you use DTutil to delete a package that is deployed to SQL Server? Answer.
Yes, you can use DTutil to delete packages in the file system and in SQL Server by using the /DELETE command-line switch.
Question 23
Can you configure the security of a package so that you can store a password in clear text in the underlying .dtsx file in the file system? Answer.
No, sensitive information such as connection passwords cannot be stored as clear text in the package file in the file system. The only way to store the connection password in the file is to encrypt the sensitive information by setting the ProtectionLevel to EncryptSensitiveWithPassword or EncryptSensitiveWithuserKey. A better choice is to set the ProtectionLevel to DontSaveSensitive and use an SSIS configuration to store the connection password.
Question 24
What information stored in the package defi nition would cause a security risk if it were found? Answer.
Although a package file does not contain data, it does contain the schema details about input sources and destinations. Even if these sources and destinations cannot be accessed, this information can be a security risk because it exposes table and column names.
Question 25
If your database user login is assigned to the db_ssisoperator role, which has only read access and does not have write access to a package stored in msdb, what are you able to do with the package? Answer.
Because you are assigned the db_ssisoperator role, you can execute the package inside msdb, but you cannot delete the package from the msdb store or import packages into the store.
Question 26
What are the benefi ts and drawbacks of storing packages in SQL Server? Answer.
When packages are stored or deployed to SQL Server, you can back them up by backing up the msdb system database. In addition, when packages reside in SQL Server, you can assign package roles to manage security. However, packages stored in the msdb database require more management than packages that are not stored there. for example, to modify packages stored in the database, you have to export them and then reimport them to SQL Server.
Question 27
Can you schedule packages to execute through SQL Server Agent if you have the EncryptSensitiveWithuserKey or EncryptAllWithuserKey value set for the ProtectionLevel property? Answer.
A package can be executed through SQL Server Agent with the user key encrypion only if the package is executed on the server on which it was created and by the user who created it. If the ProtectionLevel is set to EncryptSensitiveWithUserKey and Windows Authentication is used for the connection, a package can be executed on a different server or by a different user, but a warning will be returned.
Question 28
If you have a package for which a variable must be updated at the start of execu- tion, what methods are available to you? Answer.
Variables can be updated at execution by using a configuration or by using the Set value command-line parameter, where the property path is typed as \package.variables[user:: strusername].value and the value is passed in.
