SSIS Interview Questions and Answers
Q.1 What is SSIS?
SQL Server Integration Service (SSIS) is a component of Microsoft SQL Server. It is a powerful ETL tool that is used for building enterprise-level data transformation and data integration solutions.
Q.2 What is an ETL tool?
ETL tool is a specialized form of software that allows any organization to extract data from a variety of sources including Existing databases, Cloud, Mobile devices, CRM systems, and Data warehouses. Then transform the data into a usable format, and load the data into data mart, database or data warehouse for the analysis, reporting, and data synchronization.
Q.3 What is Data Transformation?
Data Transformation is the process of transferring, validating, and normalizing the data and information from source formats, such as XML document, database file or excel sheet, to the destination format.
Q.4 What is Data Integration?
Data Integration is the process of combining heterogeneous data from different sources to a single, meaningful, and unified view. This process starts from the ingestion process and includes steps such as ETL mapping, cleansing, and transformation.
Q.5 What is a workflow in SSIS?
Workflow is used to automate the maintenance of SQL Server databases and to update multidimensional analytical data.
Q.6 What is the difference between DTS and SSIS?
| Data Transformation Services (DTS) | SQL Server Integration Services (SSIS) |
| DTS is a part of the Microsoft SQL Server since 2000. | SSIS is part of the Microsoft SQL Server since 2005. |
| A limited set of transformations are available. | A huge set of transformations are available. |
| DTS can be developed through the Enterprise manager. | SSIS can be developed through Business Intelligence Development Studio. |
| Event handlers are not available. | Event handlers are available. |
| DTS can be deployed in the local server only. | SSIS package can be deployed in all available servers using BIDS. |
Q.7 What are the important components of SSIS package?
There are the following important components of SSIS package –
- Data flow
- Control flow
- Package explorer
- Event handler
Q.8 What are the main components of SSIS architecture?
SSIS architecture consists of the following four main components –
- SSIS service
- SSIS runtime engine & runtime executable
- SSIS dataflow engine & dataflow components
- SSIS clients
Q.9 What is the configuration in SQL Server Integration Services (SSIS)?
SSIS configuration provides a way to make dynamic page means; once you develop the SSIS package, then you can deploy it in a different environment.
Q.10 What are different types of SSIS configuration available in SSIS?
SSIS offers the following configuration types:
- XML configuration file
- Environment variable
- Registry entry
- Parent package variable
- SQL Server table
Q.11 What is the control flow?
A control flow is a part of a package that contains tasks with functionality (create backups, execute scripts, connect to FTP, etc.) and constraints that connect the executable, containers, and tasks into an ordered flow.
Q.12 What is the data flow task in SSIS?
Data flow task is a mechanism that moves data from source to destination. It is also used to handle the transformation of data.
Q.13 What are the data flow components in SSIS?
There are three data flow components in SSIS.
Source: Source extracts the data from data stores.
Transformation: Transformation update, summarize, and delete the data.
Destination: Destination loads data into data stores.
Q.14 Explain solution Explorer in SSIS.
Solution Explorer in SSIS is a place where you can view and access all the data sources, projects, and other miscellaneous files.
Q.15 What is a task in SSIS?
A task is a control flow element that defines units of work that are performed in a package control flow. A task can be explained as an individual unit of the work. You can write custom tasks using the programming language that supports COM, such as Visual Basic, C#, or a .NET programming language.
Q.16 What is an SSIS package?
SSIS is the storage file with .dtsx extension that contains control flow, data flow, connections, parameters, event handlers, etc. in the SSIS project,
Q.17 What are the different types of connection or files that SSIS support?
There are the following types of connections that SSIS support –
- ODBC
- OLEDB
- .net SQL Client
- Flat File
- Excel
- XML
Q.18 Enlist the possible locations to save SSIS packages.
SSIS packages can be saved in the following locations:
- SQL Server
- File System
- Package Store
Q.19 Explain what is connection manager in SSIS?
A connection manager is a link between the package and the data source. It defines the connection string for accessing the data. A package includes at least one connection manager.
Q.20 List out the different types of data viewers in SSIS?
- Grid
- Histogram
- Scatter Plot
- Column Chart
Q.21 What is the function of Event handlers in SSIS?
The event handler is a workflow that runs in response to the run-time events raised by a package, container, or task.
Q.22 What is the difference between Execute T-SQL Task and Execute SQL Task?
| Execute T-SQL | Execute SQL |
| Execute T-SQL takes less memory. | Execute SQL takes more memory. |
| Execute T-SQL provides faster performance. | Execute SQL provides slower performance. |
| It only supports ADO.net connection. | It supports multiple types of connection. |
Q.23 What is the difference between Control Flow and Data Flow?
| Control Flow | Data Flow |
| It is Process Oriented. | It is Data-Oriented. |
| It is made up of Tasks and Container. | It is made up of Source, Transformation, and Destination. |
| It is the smallest unit of Task | It is the smallest unit of a component. |
| It is connected through the Precedence constraints. | It is made up through the Paths. |
Q.24 What is the precedence constraint?
The precedence constraint is a predefined condition checker in the package that can be used to create the workflow in SSIS packages It also specifies the conditions that determine whether executable runs or not.
Q.25 Explain the SSIS checkpoint.
SSIS checkpoint is useful when you load large data or import/export the images. SSIS checkpoint allows you to restart the packages from the point of failure, instead of re-running the whole package.
Q.26 What is the Data Profiling task?
Data profiling task is used to compute various profiles that help us to become more familiar with a data source, and also have an ability to identify the problems in the data.
Data profiling task offers the following benefits:
- Analyze the source data more effectively.
- Prevent data quality problems.
Q.27 What is the difference between Merge and Union All?
| Merge | Union All |
| Merge can accept only two inputs. | Union All can accept more than two inputs. |
| Merge always accepts sorted input data. | In Union All, no need to sort the data. |
Q.28 What are the disadvantages of SSIS?
There are the following disadvantages of SSIS –
- SSIS sometimes create issues in non-window environments.
- It does not clearly define the vision and strategy.
- It requires high memory and conflicts with SQL.
- In case of CPU allocation, it is a problematic case when you have more packages to run parallel.
Q.29 What is the use of XML task?
XML task is used to split, merge, or reformat any XML file.
Q.30 What are the features of SSIS?
- Tight Integration with other Microsoft SQL family.
- Data Mining Query Transformation
- Can handle data that occurs from the heterogeneous data source at the same package.
- Better for multi-step operations, complex transformations, and structured exception handling.