Read the most frequently asked 24 top SSIS interview questions and answers for freshers and experienced job interview questions pdf download free.
1. What is a package?
A discrete executable unit of work composed of a collection of control flow and other objects, including data sources, transformations, process sequence, and rules, errors and event handling, and data destinations.
2. What is a workflow in SSIS?
A workflow is a set of instructions on how to execute Tasks.
(It is a set of instructions on how to execute Tasks such as sessions, emails and shell commands. a workflow is created form work flow mgr.)
3. What is the Difference between control flow Items and data flow Items?
The control flow is the highest level control process. It allows you to manage the run-time process the run time process activities of data flow and other processes within a package.
When we want to extract, transform and load data within a package. You add an SSIS dataflow Task to the package control flow.
4. What are the main components of SSIS (project-architecture)?
SSIS architecture has 4 main components
1.SSIS service
2.SSIS runtime engine & runtime executables
3.SSIS dataflow engine & dataflow components
4.SSIS clients’
5.different components in SSIS package?
1. Control flow
2.data flow
3.event handler
4.package explorer
Containers: provide structure and scope to your package
Types of containers:
i. Task host container: the Taskhost container services a single Task.
ii. Sequence container: It can handle the flow of subset of a package and can help you drive a package into smaller more manageable process.
Uses:-
1. Grouping Tasks so that you can disable a part of the package that no longer needed.
2. Narrowing the scope of the variable to a container.
3. Managing the property of multiple Tasks in one step by setting the properties of the container.
iii. For loop container: evaluates an expression and repeats Its workflow until the expression evaluates to false.
iv. For each loop container: defines a control flow repeatedly by using an enumerator.
For each loop container repeats the control flow for each member of a specified enumerator.
Tasks: It provides the functionality to your package.
Ø It is a individual unit of work.
Event handler: It responds to raised events in your package.
Precedence constraints: It provides ordinal relationship b/w various Items in your package.
6. How to deploy the package?
To deploy the package first we need to configure some properties.
=> Go to project tab->package properties->we get a window, configure deployment Utility as "True"
=> Specify the path as "bin/deployment"
7. Connection manager:
It is a bridge b/w package object and physical data. It provides logical representation of a connection at design time the properties of the connection mgr describes the physical connection that integration services creates when the package is run.
8. Tell the Utility to execute (run) the package?
In BIDS a package that can be executed in debug mode by using the debug menu or toolbar or from solution explorer.
In production, the package can be executed from the command line or from Microsoft windows Utility or It can be scheduled for automated execution by using the SQL server agent.
i). Go to->debug menu and select the start debugging button
ii).press F5 key
iii).right click the package and choose execute package.
iv).command prompts utilities
a).DTExecUI
1. To open command prompt->run->type dtexecui->press enter
2. The execute package Utility dialog box opens.
3. in that click execute to run the package.
Wait until the package has executed successfully.
b).DTExec Utility
1.open the command prompt window.
2. Command prompt window->type dtexec /followed by the DTS, SQL, or file option and the package path, including package name.
3. If the package encryption level is encrypting sensitive with password or encrypt all with password, use the decrypt option to provide the password.
If no password is included, dtexec will prompt you for the password.
4. Optionally, provide additional command-line options
5. Press enter.
6. Optionally, view logging and reporting information before closing the command prompt window.
The execute package Utility dialog box opens.
7. In the execute package Utility dialog box, click execute package.
Wait until the package has executed successfully.
v).using SQL server mgmt studio to execute package
1. In SSMS right click a package, and then click run package.
Execute package Utility opens.
2. Execute the package as described previously.
9. How can u design SCD in SSIS?
Def:-SCD explains how to capture the changes over the period of time.
This is also known as change data capture.
type1: It keeps the most recent values in the target. It does not maintain the history.
type2: It keeps the full history in the target database. For every update in the source a new record is inserted in the target.
type3: It keeps current & previous information in the target.
10. How can u handle the errors through the help of logging in SSIS?
To create an on error event handler to which you add the log error execute SQL Task.
11. What is a log file and how to send log file to mgr?
It is especially useful when the package has been deployed to the production environment, and you cannot use BIDS and VSA to debug the package.
SSIS enables you to implement logging code through the Dts. Log method.
When the Dts. Log method is called in the script, the SSIS engine will route the message to the log providers that are configured in the containing package.
12. What is environment variable in SSIS?
An environment variable configuration sets a package property equal to the value in an environment variable.
Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package.
13. about multiple configurations?
It means including the xml configuration, environment variable, registry entry, parent package variable, SQL Server table, and direct and indirect configuration types.
14. How to provide security to packages?
In two ways:-
1. Package encryption
2. Password protection.
15. as per error handling in T/R, which one handle the better performance? Like fail component, redirect row or ignore failure?
Redirect row provides better performance for error handling.
16. Staging area?
It is a temporary data storage location. Where various data T/R activities take place.
Staging area is a kitchen of data warehouse.
17. Task?
An individual unit of work.
Types:-
1. Active x script Task
2. Analysis services execute DDL Task *
3. Analysis services processing Task *
4. Bulk insert Task *
5. Data flow Task *
6. Data mining query Task
7. Execute Dts 2000 package Task
8. Execute package Task *
9. Execute process Task
10. Execute SQL Task *
11. File system Task *
12. Ftp Task
13. Message queue Task
14. Script Task *
15. Send mail Task *
16. Web service Task
17. Wmi data reader Task
18. Wmi event Task
19. Xml Task
18. Event handler & logging?
Even handler is the mechanism to raise a event based on specific scenario. For example if there is any failure in data load it will notify thru email or entry in error table etc.
Logging can be done based on event, in SSIS there are 12 events that can be logged at Task or package level. You can enable partial logging for one Task and enable much more detailed logging for billing Tasks.
Example:-
On error; On post validate; On progress; On warning
In SSIS different type of logging mechanism are there:
SQL profiler
Text files
SQL server
Window event log
Xml file
19. Import & export wizard?
Easiest method to move data from sources like oracle, db2, SQL server.
Right click on database name->go to Task->import and export wizard
Select the source
Select the destination
Query copy of tables
Execute
Finish
20.what solution explorer?
Once you creating project with specific project name then if you want to add
data source/-data source views/packages/ miscellaneous; then this window will help to organize different files under one structure.
21. Precedence constraints?
Constraints that link executable, container, and Tasks within the package control flow and specify condition that determine the sequence
and conditions for determine whether executable run.
22. Data pipeline?
The memory based, multithreaded, buffered t/r process flow data through an SSIS data flow Task during package execution.
23. TRANSFORMATIONS?
It is an object that generates, modifies, or passes data.
1. AGGEGATE T/R:-It applies an aggregate function to grouped records and produces new output records from aggregated results.
2. AUDIT T/R:-the t/r adds the value of a system variable, such as machine name or execution instance GUID to a new output column.
3. CHARACTER MAP T/R:-this t/r makes string data changes such as changing data from lower case to upper case.
4. CONDITIONAL SPLIT:-It separate input rows into separate output data pipelines based on the Boolean expressions configured for each output.
5. COPY COLUMN:-add a copy of column to the t/r output we can later transform the copy keeping the original for auditing personal
6.DATA CONVERSION:-converts a columns data type to another data type.
7. DATA MINING QUERY:-perform a data mining query against analysis services.
8. DERIVED COLUMN:-create a new derive column calculated from expression.
9. EXPORT COLUMN:-It allows you to export a column from the data flow to a file.
10. FUZZY GROUPING:-perform data cleansing by finding rows that are likely duplicates.
11. FUZZY LOOKUP:-matches and standardizes data based on fuzzy logic.
Ex:-transform the name jon to john
12.IMPORT COLUMN:-reads the data from a file & adds It into a dataflow.
13. LOOKUP:-perform the lookup of data to be used later in a transform.
Ex:-t/T to lookup a city based on zip code.
1. Getting a related value from a table using a key column value
2. Update slowly changing dimension table
3.to check whether records already exist in the table.
14. MERGE:-merges two sorted data sets into a single data set into a single data flow.
15. MERGE JOIN:-merges two data sets into a single dataset using a join junction.
16. MULTI CAST:-sends a copy of two data to an additional path in the workflow.
17. ROW COUNT:-stores the rows count from the data flow into a variable.
18. ROW SAMPLING:-captures the sample of data from the dataflow by using a row count of the total rows in dataflow.
19. ROW SAMPLING:-captures the sample of the data from the data flow by using a row count of the total rows in data flow.
20. UNION ALL:-merge multiple data sets into a single dataset.
21. PIVOT:-converts rows into columns
22.UNPIVOT:-converts columns into rows
24. Batch?
A batch is defined as group of sessions. Those are 2 types.
1. Parallel batch processing
2. Sequential batch processing
to execute a SSIS package we will use "execute package utility"
To deploy a SSIS package we will use "package deployment Utility”
1. What is a package?
A discrete executable unit of work composed of a collection of control flow and other objects, including data sources, transformations, process sequence, and rules, errors and event handling, and data destinations.
2. What is a workflow in SSIS?
A workflow is a set of instructions on how to execute Tasks.
(It is a set of instructions on how to execute Tasks such as sessions, emails and shell commands. a workflow is created form work flow mgr.)
3. What is the Difference between control flow Items and data flow Items?
The control flow is the highest level control process. It allows you to manage the run-time process the run time process activities of data flow and other processes within a package.
When we want to extract, transform and load data within a package. You add an SSIS dataflow Task to the package control flow.
4. What are the main components of SSIS (project-architecture)?
SSIS architecture has 4 main components
1.SSIS service
2.SSIS runtime engine & runtime executables
3.SSIS dataflow engine & dataflow components
4.SSIS clients’
5.different components in SSIS package?
1. Control flow
2.data flow
3.event handler
4.package explorer
Containers: provide structure and scope to your package
Types of containers:
i. Task host container: the Taskhost container services a single Task.
ii. Sequence container: It can handle the flow of subset of a package and can help you drive a package into smaller more manageable process.
Uses:-
1. Grouping Tasks so that you can disable a part of the package that no longer needed.
2. Narrowing the scope of the variable to a container.
3. Managing the property of multiple Tasks in one step by setting the properties of the container.
iii. For loop container: evaluates an expression and repeats Its workflow until the expression evaluates to false.
iv. For each loop container: defines a control flow repeatedly by using an enumerator.
For each loop container repeats the control flow for each member of a specified enumerator.
Tasks: It provides the functionality to your package.
Ø It is a individual unit of work.
Event handler: It responds to raised events in your package.
Precedence constraints: It provides ordinal relationship b/w various Items in your package.
6. How to deploy the package?
To deploy the package first we need to configure some properties.
=> Go to project tab->package properties->we get a window, configure deployment Utility as "True"
=> Specify the path as "bin/deployment"
7. Connection manager:
It is a bridge b/w package object and physical data. It provides logical representation of a connection at design time the properties of the connection mgr describes the physical connection that integration services creates when the package is run.
8. Tell the Utility to execute (run) the package?
In BIDS a package that can be executed in debug mode by using the debug menu or toolbar or from solution explorer.
In production, the package can be executed from the command line or from Microsoft windows Utility or It can be scheduled for automated execution by using the SQL server agent.
i). Go to->debug menu and select the start debugging button
ii).press F5 key
iii).right click the package and choose execute package.
iv).command prompts utilities
a).DTExecUI
1. To open command prompt->run->type dtexecui->press enter
2. The execute package Utility dialog box opens.
3. in that click execute to run the package.
Wait until the package has executed successfully.
b).DTExec Utility
1.open the command prompt window.
2. Command prompt window->type dtexec /followed by the DTS, SQL, or file option and the package path, including package name.
3. If the package encryption level is encrypting sensitive with password or encrypt all with password, use the decrypt option to provide the password.
If no password is included, dtexec will prompt you for the password.
4. Optionally, provide additional command-line options
5. Press enter.
6. Optionally, view logging and reporting information before closing the command prompt window.
The execute package Utility dialog box opens.
7. In the execute package Utility dialog box, click execute package.
Wait until the package has executed successfully.
v).using SQL server mgmt studio to execute package
1. In SSMS right click a package, and then click run package.
Execute package Utility opens.
2. Execute the package as described previously.
9. How can u design SCD in SSIS?
Def:-SCD explains how to capture the changes over the period of time.
This is also known as change data capture.
type1: It keeps the most recent values in the target. It does not maintain the history.
type2: It keeps the full history in the target database. For every update in the source a new record is inserted in the target.
type3: It keeps current & previous information in the target.
10. How can u handle the errors through the help of logging in SSIS?
To create an on error event handler to which you add the log error execute SQL Task.
11. What is a log file and how to send log file to mgr?
It is especially useful when the package has been deployed to the production environment, and you cannot use BIDS and VSA to debug the package.
SSIS enables you to implement logging code through the Dts. Log method.
When the Dts. Log method is called in the script, the SSIS engine will route the message to the log providers that are configured in the containing package.
12. What is environment variable in SSIS?
An environment variable configuration sets a package property equal to the value in an environment variable.
Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package.
13. about multiple configurations?
It means including the xml configuration, environment variable, registry entry, parent package variable, SQL Server table, and direct and indirect configuration types.
14. How to provide security to packages?
In two ways:-
1. Package encryption
2. Password protection.
15. as per error handling in T/R, which one handle the better performance? Like fail component, redirect row or ignore failure?
Redirect row provides better performance for error handling.
16. Staging area?
It is a temporary data storage location. Where various data T/R activities take place.
Staging area is a kitchen of data warehouse.
17. Task?
An individual unit of work.
Types:-
1. Active x script Task
2. Analysis services execute DDL Task *
3. Analysis services processing Task *
4. Bulk insert Task *
5. Data flow Task *
6. Data mining query Task
7. Execute Dts 2000 package Task
8. Execute package Task *
9. Execute process Task
10. Execute SQL Task *
11. File system Task *
12. Ftp Task
13. Message queue Task
14. Script Task *
15. Send mail Task *
16. Web service Task
17. Wmi data reader Task
18. Wmi event Task
19. Xml Task
18. Event handler & logging?
Even handler is the mechanism to raise a event based on specific scenario. For example if there is any failure in data load it will notify thru email or entry in error table etc.
Logging can be done based on event, in SSIS there are 12 events that can be logged at Task or package level. You can enable partial logging for one Task and enable much more detailed logging for billing Tasks.
Example:-
On error; On post validate; On progress; On warning
In SSIS different type of logging mechanism are there:
SQL profiler
Text files
SQL server
Window event log
Xml file
19. Import & export wizard?
Easiest method to move data from sources like oracle, db2, SQL server.
Right click on database name->go to Task->import and export wizard
Select the source
Select the destination
Query copy of tables
Execute
Finish
20.what solution explorer?
Once you creating project with specific project name then if you want to add
data source/-data source views/packages/ miscellaneous; then this window will help to organize different files under one structure.
21. Precedence constraints?
Constraints that link executable, container, and Tasks within the package control flow and specify condition that determine the sequence
and conditions for determine whether executable run.
22. Data pipeline?
The memory based, multithreaded, buffered t/r process flow data through an SSIS data flow Task during package execution.
23. TRANSFORMATIONS?
It is an object that generates, modifies, or passes data.
1. AGGEGATE T/R:-It applies an aggregate function to grouped records and produces new output records from aggregated results.
2. AUDIT T/R:-the t/r adds the value of a system variable, such as machine name or execution instance GUID to a new output column.
3. CHARACTER MAP T/R:-this t/r makes string data changes such as changing data from lower case to upper case.
4. CONDITIONAL SPLIT:-It separate input rows into separate output data pipelines based on the Boolean expressions configured for each output.
5. COPY COLUMN:-add a copy of column to the t/r output we can later transform the copy keeping the original for auditing personal
6.DATA CONVERSION:-converts a columns data type to another data type.
7. DATA MINING QUERY:-perform a data mining query against analysis services.
8. DERIVED COLUMN:-create a new derive column calculated from expression.
9. EXPORT COLUMN:-It allows you to export a column from the data flow to a file.
10. FUZZY GROUPING:-perform data cleansing by finding rows that are likely duplicates.
11. FUZZY LOOKUP:-matches and standardizes data based on fuzzy logic.
Ex:-transform the name jon to john
12.IMPORT COLUMN:-reads the data from a file & adds It into a dataflow.
13. LOOKUP:-perform the lookup of data to be used later in a transform.
Ex:-t/T to lookup a city based on zip code.
1. Getting a related value from a table using a key column value
2. Update slowly changing dimension table
3.to check whether records already exist in the table.
14. MERGE:-merges two sorted data sets into a single data set into a single data flow.
15. MERGE JOIN:-merges two data sets into a single dataset using a join junction.
16. MULTI CAST:-sends a copy of two data to an additional path in the workflow.
17. ROW COUNT:-stores the rows count from the data flow into a variable.
18. ROW SAMPLING:-captures the sample of data from the dataflow by using a row count of the total rows in dataflow.
19. ROW SAMPLING:-captures the sample of the data from the data flow by using a row count of the total rows in data flow.
20. UNION ALL:-merge multiple data sets into a single dataset.
21. PIVOT:-converts rows into columns
22.UNPIVOT:-converts columns into rows
24. Batch?
A batch is defined as group of sessions. Those are 2 types.
1. Parallel batch processing
2. Sequential batch processing
to execute a SSIS package we will use "execute package utility"
To deploy a SSIS package we will use "package deployment Utility”
No comments:
Post a Comment