Explain Merge Join Transformation?
Merge Join transformation will merge output from 2 inputs and doing INNER or OUTER join on the data. But if you the data come from 1 OLEDB data source, it is better you join through SQL query rather than using Merge Join transformation. Merge Join is intended to join 2 different data source.
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.
How to provide security to packages?
We can provide security in two ways
1. Package encryption
2. Password protection.
What are 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.
What is Design time Deployment in SSIS ?
When you run a package from with in BIDS,it is built and temporarily deployed to the folder. By default the package will be deployed to the BIN folder in the Package’s Project folder and you can configure for custom folder for deployment. When the Package’s execution is completed and stopped in BIDS,the deployed package will be deleted and this is called as Design Time Deployment.
This transformation sends output to multiple output paths with no conditional as Conditional Split does. Takes ONE Input and makes the COPY of data and passes the same data through many outputs. In simple Give one input and take many outputs of the same data.
Explain Percentage and row sampling Transformations?
This transformation will take data from source and randomly sampling data. It gives you 2 outputs. First is selected data and second one is unselected data. It is used in situation where you train data mining model. These two are used to take the SAMPLE of data from the input data.
Explain Sort Transformation?
This component will sort data, similar in TSQL command ORDER BY. Some transformations need sorted data.
Explain Union all Transformation?
It works in opposite way to Merge transformation. It can take output from more than 2 input paths and combines into single output path.
What r the possible locations to save SSIS package?
You can save a package wherever you want.
SQL Server
Package Store
File System
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.
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.
What is the diff 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 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.
What are the main component 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
Different components in SSIS package?
Control flow
Data flow
Event handler
Package explorer
What are Connection Managers?
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.
Explain what is SSIS?
SSIS or SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server, which can be used to accomplish a broad range of data migration tasks.
Mention what are the possible locations to save SSIS package?
You can save SSIS package at
SQL Server
Package Store
File System
What will be your first approach if the package that runs fine in Business Intelligence Development Studio (BIDS) but fails when running from an SQL agent job?
The account that runs SQL Agent Jobs might not have the required permission for one of the connections in your package. In such cases, either you can create a proxy account or elevate the account permissions.
Explain what is connection managers in SSIS?
While gathering data from different sources and writing it to a destination, connection managers are helpful. Connection manager facilitates the connection to the system that include information’s like data provider information, server name, authentication mechanism, database name, etc.
Explain what is SSIS breakpoint?
A breakpoint enables you to pause the execution of the package in business intelligence development studio during troubleshooting or development of an SSIS package.
Explain what is event logging in SSIS?
In SSIS, event logging allows you to select any specific event of a task or a package to be logged. It is very helpful when you are troubleshooting your package to understand the performance package.
Explain what is logging mode property?
SSIS packages and all the associated tasks have a property called LoggingMode. This property accepts three possible values
Disabled: To disable logging of the component
Enabled: To enable logging of the component
UseParentSetting: To use parent’s setting of the component
Explain what is a data flow buffer?
SSIS operates using buffers; it is a kind of an in-memory virtual table to hold data.
Explain what is a container? How many types of containers are there in SSIS?
In SSIS, a container is a logical grouping of tasks, and it allows to manage the scope of a task together.
Types of containers in SSIS are
Sequence container
For loop container
Foreach loop container
Task host container
Explain what is Precedence Constraint in SSIS?
Precedence Constraint in SSIS enables you to define the logical sequence of tasks in the order they should be executed. You can connect all the tasks using connectors- Precedence Constraints.
Explain what variables in SSIS and what are the types of variables in SSIS?
Variable in SSIS is basically used to store values. In SSIS, there are two types of variables system variable and user variable.
Mention what are the important components of SSIS package?
The important component in SSIS package are
Data flow
Control flow
Package explorer
Event handler
Explain what is Solution Explorer in SSIS?
Solution Explorer in SSIS Designer is a screen where you can view and access all the data sources, data sources views, projects, and other miscellaneous files.
Explain what does it mean by data flow in SSIS?
Data flow in SSIS is nothing but the flow of data from the corresponding sources to the target destinations.
Define what is “task” in SSIS?
Task in SSIS is a very much similar to the method of any programming language that represents or carries out an individual unit of work. Tasks are categorized into two categories
Control Flow Tasks
Database Maintenance Tasks
Explain what is SSIS package?
A package in SSIS is an organized collection of connections like data flow elements, control events, event handlers, parameters, variables, and configurations. You assemble them by either building it programmatically or by graphical design tools that SSIS provides.
List out different types of connection or files that support SSIS?
Different types of connection that work within SSIS are
ODBC
OLEDB
.net SQLClient
Flat File
Excel
XML
Checkpoint in SSIS allows the project to restart from the point of failure. Checkpoint file stores the information about the package execution, if the package run successfully the checkpoint file is deleted or else it will restart from the point of failure.
For what data checkpoint data is not saved?
Checkpoint data is not saved for ForEach Loop and ForLoop containers.
Explain what is conditional split transactions in SSIS?
Conditional split transformation in SSIS is just like IF condition, which checks for the given condition based on the condition evaluation.
List out the different types of Data viewers in SSIS?
Different types of data viewers in SSIS include
Grid
Histogram
Scatter Plot
Column Chart
Explain what is the role of Event Handlers tab in SSIS?
On the event handlers tab, workflows can be configured to respond to package events. For instance, you can configure workflow when any task stops, fails or starts.
Explain how you can notify the staff members about package failure?
Either inside the package you could add a Send Mail Task in the event handlers, or you can even set notification in the SQL Agent when the package runs.
Explain how can you do an incremental load?
The best and fastest way to do incremental load is by using Timestamp column in the source table and storing the last ETL timestamp.
Merge Join transformation will merge output from 2 inputs and doing INNER or OUTER join on the data. But if you the data come from 1 OLEDB data source, it is better you join through SQL query rather than using Merge Join transformation. Merge Join is intended to join 2 different data source.
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.
How to provide security to packages?
We can provide security in two ways
1. Package encryption
2. Password protection.
What are 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.
What is Design time Deployment in SSIS ?
When you run a package from with in BIDS,it is built and temporarily deployed to the folder. By default the package will be deployed to the BIN folder in the Package’s Project folder and you can configure for custom folder for deployment. When the Package’s execution is completed and stopped in BIDS,the deployed package will be deleted and this is called as Design Time Deployment.
 |
| Infosys Frequently Asked SSIS Interview Questions Answers |
Explain Multicast Transformation?
This transformation sends output to multiple output paths with no conditional as Conditional Split does. Takes ONE Input and makes the COPY of data and passes the same data through many outputs. In simple Give one input and take many outputs of the same data.
Explain Percentage and row sampling Transformations?
This transformation will take data from source and randomly sampling data. It gives you 2 outputs. First is selected data and second one is unselected data. It is used in situation where you train data mining model. These two are used to take the SAMPLE of data from the input data.
Explain Sort Transformation?
This component will sort data, similar in TSQL command ORDER BY. Some transformations need sorted data.
Explain Union all Transformation?
It works in opposite way to Merge transformation. It can take output from more than 2 input paths and combines into single output path.
What r the possible locations to save SSIS package?
You can save a package wherever you want.
SQL Server
Package Store
File System
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.
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.
What is the diff 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 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.
What are the main component 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
Different components in SSIS package?
Control flow
Data flow
Event handler
Package explorer
What are Connection Managers?
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.
Explain what is SSIS?
SSIS or SQL Server Integration Services (SSIS) is a component of Microsoft SQL Server, which can be used to accomplish a broad range of data migration tasks.
Mention what are the possible locations to save SSIS package?
You can save SSIS package at
SQL Server
Package Store
File System
What will be your first approach if the package that runs fine in Business Intelligence Development Studio (BIDS) but fails when running from an SQL agent job?
The account that runs SQL Agent Jobs might not have the required permission for one of the connections in your package. In such cases, either you can create a proxy account or elevate the account permissions.
Explain what is connection managers in SSIS?
While gathering data from different sources and writing it to a destination, connection managers are helpful. Connection manager facilitates the connection to the system that include information’s like data provider information, server name, authentication mechanism, database name, etc.
Explain what is SSIS breakpoint?
A breakpoint enables you to pause the execution of the package in business intelligence development studio during troubleshooting or development of an SSIS package.
Explain what is event logging in SSIS?
In SSIS, event logging allows you to select any specific event of a task or a package to be logged. It is very helpful when you are troubleshooting your package to understand the performance package.
Explain what is logging mode property?
SSIS packages and all the associated tasks have a property called LoggingMode. This property accepts three possible values
Disabled: To disable logging of the component
Enabled: To enable logging of the component
UseParentSetting: To use parent’s setting of the component
Explain what is a data flow buffer?
SSIS operates using buffers; it is a kind of an in-memory virtual table to hold data.
Explain what is a container? How many types of containers are there in SSIS?
In SSIS, a container is a logical grouping of tasks, and it allows to manage the scope of a task together.
Types of containers in SSIS are
Sequence container
For loop container
Foreach loop container
Task host container
Explain what is Precedence Constraint in SSIS?
Precedence Constraint in SSIS enables you to define the logical sequence of tasks in the order they should be executed. You can connect all the tasks using connectors- Precedence Constraints.
Explain what variables in SSIS and what are the types of variables in SSIS?
Variable in SSIS is basically used to store values. In SSIS, there are two types of variables system variable and user variable.
Mention what are the important components of SSIS package?
The important component in SSIS package are
Data flow
Control flow
Package explorer
Event handler
Explain what is Solution Explorer in SSIS?
Solution Explorer in SSIS Designer is a screen where you can view and access all the data sources, data sources views, projects, and other miscellaneous files.
Explain what does it mean by data flow in SSIS?
Data flow in SSIS is nothing but the flow of data from the corresponding sources to the target destinations.
Define what is “task” in SSIS?
Task in SSIS is a very much similar to the method of any programming language that represents or carries out an individual unit of work. Tasks are categorized into two categories
Control Flow Tasks
Database Maintenance Tasks
Explain what is SSIS package?
A package in SSIS is an organized collection of connections like data flow elements, control events, event handlers, parameters, variables, and configurations. You assemble them by either building it programmatically or by graphical design tools that SSIS provides.
List out different types of connection or files that support SSIS?
Different types of connection that work within SSIS are
ODBC
OLEDB
.net SQLClient
Flat File
Excel
XML
Explain what is a checkpoint in SSIS?
Checkpoint in SSIS allows the project to restart from the point of failure. Checkpoint file stores the information about the package execution, if the package run successfully the checkpoint file is deleted or else it will restart from the point of failure.
For what data checkpoint data is not saved?
Checkpoint data is not saved for ForEach Loop and ForLoop containers.
Explain what is conditional split transactions in SSIS?
Conditional split transformation in SSIS is just like IF condition, which checks for the given condition based on the condition evaluation.
List out the different types of Data viewers in SSIS?
Different types of data viewers in SSIS include
Grid
Histogram
Scatter Plot
Column Chart
Explain what is the role of Event Handlers tab in SSIS?
On the event handlers tab, workflows can be configured to respond to package events. For instance, you can configure workflow when any task stops, fails or starts.
Explain how you can notify the staff members about package failure?
Either inside the package you could add a Send Mail Task in the event handlers, or you can even set notification in the SQL Agent when the package runs.
Explain how can you do an incremental load?
The best and fastest way to do incremental load is by using Timestamp column in the source table and storing the last ETL timestamp.