1.What do you understand by design limitation in SSIS package?
Making use of parallelism
None of the mentioned
Package uses few single-threaded tasks.
Not making use of parallelism
2. Which of the following BI technique can predict value for a specific data item attribute?
Modeling
Association
Predictive value
Predictive modeling
3. You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors. Products for each distributor source must be combined for insertion into a single product table destination. You need to select the appropriate data flow transformation to meet this requirement. Which transformation types should you use?
Merge
Union All
Merge Join
Multicast
4.We can insert record in Lookup Table by using Lookup Transformation
4.We can insert record in Lookup Table by using Lookup Transformation
True
False
5.Which tasks run data flows to extract data, apply column level transformations, and load data?
SQL Server tasks
Data Preparation Tasks
Data Flow Tasks
Workflow Tasks
6. Which container is not visible from the IDE?
Task Host Container
For Loop Container
Sequence Container
For Each Loop Container
7. You got an order from your company CIO, you are asked to design a SQL Server 2008 Integration Services (SSIS) package. The package contains a Data Flow task. According to the requirement of the company CIO, the package has to be modified to write all rows by which errors are generated to a text file. So what should you do?
A Flat File Destination component should be used (Missed)
A Raw File Destination component should be used
The SSIS log provider for the SQL Profiler should be used
The SSIS log provider for the text file should be used
An Error output should be added to the Data Flow component
8. What are the main component of SSIS (project-architecture)?
SSIS service (Missed)
SSIS dataflow engine & dataflow components (Missed)
SSIS clients
All the above
SSIS runtime engine & runtime executables (Missed)
9.What Is BCP?
None
Business continuity program
Bulk copy program.
Both
10.Merge join works as
10.Merge join works as
Inner left full
Self right outer
Right outer join
Left right full
11. How many components are present in SSIS Toolbox?
11. How many components are present in SSIS Toolbox?
7
6
4
5
12.OLEDB connection manager supports
DB2 (Missed)
Sql Server
MySql (Missed)
Teradata(Missed)
Oracle
13.What are the types of Logging available in SSIS?
Windows event Log Provider
Excel Log provider
Text file Provider (Missed)
Csv log provider
14. Identify the control flow components in the options provided?
Derived Column
Merge Transformation
For each Loop containers
SQL Task
History Cleanup Task
Data Flow Task
Lookup Task
15.Define Index?
15.Define Index?
None
Predefined pointers to data page
Primary key
Foreign Key
16. You are creating a SQL Server 2008 Integration Services (SSIS) package on a SQL Server 2008 database for Company.com. In order to develop a failure recovery plan that is published for a SQL Server. Which is the correct answer?
16. You are creating a SQL Server 2008 Integration Services (SSIS) package on a SQL Server 2008 database for Company.com. In order to develop a failure recovery plan that is published for a SQL Server. Which is the correct answer?
You should back up the systemdb databse.
You should back up the master database.
You should back up the local database.
You should back up the system database.
17. The total Number of container tasks available in SSIS is
17. The total Number of container tasks available in SSIS is
4
3
1
2
18. Which of the following is TRUE with regards to the Fuzzy Lookup similarity threshold setting
?
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.
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(Missed)
Increasing the threshold can improve the speed of matching.(Missed)
Increasing the threshold can deteriorate the speed of matching
19. You need to update the package to extract data from multiple text files of the same directory. Which three actions should you perform?
Add a variable to the data flow task.
Add a For Loop container to the package.
Add a Foreach Loop container to the package.
Add an expression to the Flat File Source component.
Add a variable to the package.
Add an expression to the Flat File connection manager.
20.Which of the following BI technique can predict value for a specific data item attribute?
20.Which of the following BI technique can predict value for a specific data item attribute?
Association
Modeling
Predictive value
Predictive modeling
21.What do you understand by design limitation in SSIS package?
Making use of parallelism
Not making use of parallelism
None of the mentioned
Package uses few single-threaded tasks.
22.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 Advance Page of the Lookup Transformation Editor.Which of the following is a reason for doing this?
22.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 Advance Page of the Lookup Transformation Editor.Which of the following is a reason for doing this?
To configure partial caching.
To configure a suitable connection manager.
To modify the SQL statement for the Lookup transformation.
To specify the join between the source table and the reference table.
23.Default Isolation level in SSIS?
23.Default Isolation level in SSIS?
Read Committed
Serializable
Read Uncommitted
Snapshot
24.Which of the following show flow of data from the corresponding sources to the referred destinations
Transformation
control flow
data flow
None of these
26. What conditions can be given to preceednce constraints?
26. What conditions can be given to preceednce constraints?
Constraint
Expression
All of the above
None of the above
27.What are the effects of setting IsolationLevel transaction property of a Data Flow task to Chaos?
27.What are the effects of setting IsolationLevel transaction property of a Data Flow task to Chaos?
It reduces blocking by storing a version of data that one application can read while another is modifying the same data.
It prevents other users from updating the data.
It prevents shared locks from being issued and exclusive locks from being honored.
It prevents pending changes from more highly isolated transactions from being overwritten
28.You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on Server A. The package includes a data flow and is executed on ServerB. The destination table has its own identity column. The destination data load has the following requirements: Ÿ1. The identity values from the source table must be used.Ÿ2. Default constraints on the destination table must be ignored.Ÿ3. Batch size must be 100,000 rows. You need to add a destination and configure it to meet the requirements. Which destination should you use?
28.You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on Server A. The package includes a data flow and is executed on ServerB. The destination table has its own identity column. The destination data load has the following requirements: Ÿ1. The identity values from the source table must be used.Ÿ2. Default constraints on the destination table must be ignored.Ÿ3. Batch size must be 100,000 rows. You need to add a destination and configure it to meet the requirements. Which destination should you use?
SQL Server Destination
OLE DB Destination without Fast Load
OLE DB Destination with Fast Load
ADO NET Destination without Bulk Insert
29.You design a SQL Server 2008 Integration Services (SSIS) package. The package assigns a value to a variable by using a Script task. When the package executes successfully, the value of the variable is not updated. You need to modify the package so that the Script task can assign the value of the variable. What should you do?
29.You design a SQL Server 2008 Integration Services (SSIS) package. The package assigns a value to a variable by using a Script task. When the package executes successfully, the value of the variable is not updated. You need to modify the package so that the Script task can assign the value of the variable. What should you do?
Modify the variable scope to the package.
Modify the EvaluateAsExpression property of the variable.
Modify the RaiseChangedEvent property of the variable.
Modify the variable scope to the script task.
30.What are different ways to execute your SSIS Package?
30.What are different ways to execute your SSIS Package?
Dtexec.exe (Missed)
DtExecUI(Missed)
By using BIDS/ SSDT
SQL Server Agent Job
Run SSIS Package Programmatically (Missed)
Windows Scheduler or Any third party Scheduler
30.You are creating a SQL Server 2008 Integration Services (SSIS) package which gets information from a FTP server through utilizing many FTP tasks and operate information through utilizing many Execute SQL tasks for Company.com. After the package downloads the files, you should make sure that you could set the package to restart. In order to solve the problem, which is the correct answer?
You should set the package to utilize a checkpoint.
You should transfer the half HTTP job to a sequence container.
You should set the Execute SQL store procedure to hold transactions Log.
You should set the HTTP tasks to hold transactions Log.
31.You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD). You need to add a task or component to the package that allows you to implement the SCD logic. What should you use?
An SCD component
A Script component
A Merge component
An Aggregate component
32.You develop a series of SQL Server 2008 Integration Services (SSIS) packages. There are dependencies among the packages. You need to configure the packages. Which configuration type should you use?
Parent package variable
XML configuration file
Environment variable
Registry entry
33.Which of the following is not a feature of SQL Server 2008 R2 Enterprise?
Hot-add RAM
Hot-add CPU
Database partitioning
In memory database
34.How to execute a bat/cmd files from SSIS tasks?
None of the listed options
Execute SQL Task
Execute Package Task
Execute Process Task
35.How many types of ssis connection managers are there?
4
6
19
5
36.The column in a table is set to read only then can we create a new column using derived column?
True
False
37.What are the main component of SSIS (project-architecture)?
SSIS runtime engine & runtime executables (Missed)
SSIS service (Missed)
SSIS dataflow engine & dataflow components (Missed)
All the above
SSIS clients
39.You are in the process of creating a SQL Server 2008 Integration Services (SSIS) data flow. Every source database is belongs to a different server and based on a SELECT statement which utilizes an ORDER BY clause. The data flow is listed below: You get error message below when you connect the paths from the two sources "Data Flow Task: The input is not sorted. The 'input "Merge Join Left Input" (80)' must be sorted." You should make sure that data from every search is contained in every row in the data flow. In order to solve the problem, which is the correct answer?
You should refresh the sorting properties in each source component.
38.What are the different types of containers in SSIS
Foreach Loop Container
For Loop Container
Task Host Container
Sequence Container (Missed)
39.You are in the process of creating a SQL Server 2008 Integration Services (SSIS) data flow. Every source database is belongs to a different server and based on a SELECT statement which utilizes an ORDER BY clause. The data flow is listed below: You get error message below when you connect the paths from the two sources "Data Flow Task: The input is not sorted. The 'input "Merge Join Left Input" (80)' must be sorted." You should make sure that data from every search is contained in every row in the data flow. In order to solve the problem, which is the correct answer?
You should refresh the sorting properties in each source component.
You should increase a Lookup transformation for every data flow.
You should choose a default transformation
You should increase a Lookup transformation for some data flows.
40. You design a SQL Server 2008 Integration Services (SSIS) package. The package assigns a value to a variable by using a Script task. When the package executes successfully, the value of the variable is not updated. You need to modify the package so that the Script task can assign the value of the variable. What should you do?
Modify the variable scope to the script task.
Modify the RaiseChangedEvent property of the variable.
Modify the EvaluateAsExpression property of the variable.
Modify the variable scope to the package.
41.What is break point in ssis?
Helps in the extraction of data
Helps in the transformation of data
Break point is stopping point in code
None of the above
42. What are types of components available in Data flow task?
Transformation
Destination
Container
Source
43.You are creating a SQL Server 2008 Integration Services (SSIS) instance for Company.com. The package is listed below: You should make sure that you create a parameter which includes important information that is only useful to the Data Flow Task and the Execute SQL Task and so on. Which is the correct answer?
You should develop the parameter at the Control Flow Job scope.
You should develop the parameter at the Sequence Container scope.
You should develop the parameter at the Running SQL Job scope.
You should develop the parameter at the default scope.
44. When using an Execute SQL task the developer must ensure that the order of the parameters within the query must be the same as the order in which the parameters appear in the Query.
True
False
45.You are creating a SQL Server 2008 Integration Services (SSIS) package for Company.com. In order to specify the XML configuration during package development, you should apply the configuration to the deployed package. Which is the correct answer?
45.You are creating a SQL Server 2008 Integration Services (SSIS) package for Company.com. In order to specify the XML configuration during package development, you should apply the configuration to the deployed package. Which is the correct answer?
You should utilize the Publish utility.
You should utilize the exec command line utility.
You should utilize the DTEXEC command line utility.
You should utilize the default command line utility.
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 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?
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 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?
It prevents a package from creating logical copies of data.
It distributes its input to one or more outputs.
It allows a package to create logical copies of data.
It distributes its input to a single output.
47.How many Data Flow Tasks can a package run in parallel?
Total task=100
Total Tasks=Number of processors of machine+2
Total task=10
Total task=6
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 Microsoft SQL Server 2012 junior developers. You are discussing the ValidateExternalMetaData property for a data flow. Which of the following represents the data type for this property?
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 Microsoft SQL Server 2012 junior developers. You are discussing the ValidateExternalMetaData property for a data flow. Which of the following represents the data type for this property?
IDTSExternalMetadataColumn100
Integer
String
Boolean
49.How you can notify the staff members about package failure?
Send Mail Task
Notification from SQL Agent
Can’t send notification automatically
50.You administer an instance of a SQL Server 2008 server. The server is used to execute SQL Server 2008 Integration Services (SSIS) packages. You need to ensure that the server executes only correctly signed packages. What should you do?
50.You administer an instance of a SQL Server 2008 server. The server is used to execute SQL Server 2008 Integration Services (SSIS) packages. You need to ensure that the server executes only correctly signed packages. What should you do?
Set the package protection level on all packages to EncryptSensitiveWithPassword.
Set the package protection level on all packages to DontSaveSensitive.
Set the BlockedSignatureStates registry entry to Block invalid and untrusted signatures and unsigned packages
Set the BlockedSignatureStates registry entry to NoAdministrativeRestriction
51.You got an order from your company CIO, you're asked to develop a SQL Server 2008 Integration Services (SSIS) data flow. The data flow loads data to a SQL Server 2008 database. Using data from the rows in your data flow, you must make sure that the existing records in the SQL Server database is updated by the data flow. Of the following data flow components, which one should be used?
51.You got an order from your company CIO, you're asked to develop a SQL Server 2008 Integration Services (SSIS) data flow. The data flow loads data to a SQL Server 2008 database. Using data from the rows in your data flow, you must make sure that the existing records in the SQL Server database is updated by the data flow. Of the following data flow components, which one should be used?
Data Conversion Transformation should be used.
OLE DB Command Transformation should be used
OLE DB Destination should be used
SQL Server Destination should be used
52. What Other sources can be downloaded in Microsoft website?
Orcale
Teradata (Missed)
SAPBI
My sql
53.You are creating a SQL Server 2008 Integration Services (SSIS) data flow which moves information to a SQL Server 2008 database for Company.com. You should make sure that data flow refreshes the existed data in the SQL Server database through utilizing data in your data flow. Which is the correct answer?
You should utilize Dynamic Management View (DMV)
You should utilize Data Conversion Transformation
You should utilize OLE DB Destination
You should utilize SQL Server Destination
53.In the event of an Execute SQL task failing, changes made by all of the Execute SQL tasks is rolled back.To do that which of the following is the value that must be set for the TransactionOption property of the package?
NotSupported
NotRequired
Supported
Required
54.You need to control the execution behavior of the package by manipulating the SuccessValue of the task, When can this manipulation can take affect?
ReturnErrorCode
FailTaskIfReturnCodeIsNotSuccessValue
FailTaskCodeIsNotSuccessValue
None of the listed options
56. You want to run the subsequent container in the Control flow task only if the preceding container with execute sql task sets a variable with a appropriate value, what type of evaluation option is required?
Constraint AND Expression
Constraint OR Expression
Expression
All of the listed options
Constraint
57. You are creating a SQL Server 2008 Integration Services (SSIS) instance for Company.com. The package displays job listed below: - You should operate multiple files through utilizing a For Each Loop container. - You should get the files' content to a table through utilizing a Data Flow task. - Through utilizing an Execute SQL task, you should Log the results of the state into a table - You should make sure that the Execute SQL task only could not run in one transaction.Which is the correct answer?
You should set the TransactionOption property to Supported in the package.
You should set the TransactionOption property to Required in the package. (Missed)
You should set the TransactionOption property to RequiredNew in the package.
You should set the TransactionOption property to None in the package.
You should set the TransactionOption property to Supported in the Execute SQL task.
You should set the TransactionOption
58.If you need to create a sequence number for input records, which transformation will you use?
Row Count
Derived column
Script component
None
59.Dimension and cube processing can be scheduled using SSIS
True
False
60. What are the effects of setting IsolationLevel transaction property of a Data Flow task to Chaos?
It prevents other users from updating the data.
It prevents shared locks from being issued and exclusive locks from being honored.
It reduces blocking by storing a version of data that one application can read while another is modifying the same data.
It prevents pending changes from more highly isolated transactions from being overwritten
61.Identify the control flow components in the options provided?
Lookup Task
For each Loop containers
Derived Column
SQL Task
Data Flow Task
Merge Transformation
History Cleanup Task
62.You are operating a SQL Server 2008 Integration Services (SSIS) instance through Business Intelligence Development Studio. Utilizing for Company.com, there is a common data flow component in the package which implements normally. You try to increase one sample of the common information flow component to the package. But the component could not show in thetoolbox . You should make sure that you should increase the common data flow component to the toolbox. Which is the correct answer?
You should recompile the assembly through utilizing the SYSTEM32 utility.
62.You are operating a SQL Server 2008 Integration Services (SSIS) instance through Business Intelligence Development Studio. Utilizing for Company.com, there is a common data flow component in the package which implements normally. You try to increase one sample of the common information flow component to the package. But the component could not show in thetoolbox . You should make sure that you should increase the common data flow component to the toolbox. Which is the correct answer?
You should recompile the assembly through utilizing the SYSTEM32 utility.
You should increase the assembly to the default Assembly Cache.
You should recompile the assembly through utilizing the Assembly Registration Tool
You should duplicate the assembly to the proper folder.
63.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?
An environment variable can be used to manage Integration Services objects in the catalog.
An environment variable allows you to use folders to organize your projects and environments
An environment variable specifies the data that will be used by a package.
An environment variable defines a literal value that can be assigned to a parameter during package execution.
64. 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 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?
64. 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 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?
Fixed Attribute Output
Changing Attributes Updates Output
Historical Attributes Inserts Output
Inferred Member Updates Output
65.Which options can be used to group/ungroup components?
Maintenence Tasks
Control Flow Tasks (Missed)
Event Handlers
Data Flow Tasks
No comments:
Post a Comment