Interview Questions

AJAX Interview Questions Android Interview Questions Angular 2 Interview Questions AngularJs Interview Questions Apache Presto Interview Questions Apache Tapestry Interview Questions Arduino Interview Questions ASP.NET MVC Interview Questions Aurelia Interview Questions AWS Interview Questions Blockchain Interview Questions Bootstrap Interview Questions C Interview Questions C Programming Coding Interview Questions C# Interview Questions Cakephp Interview Questions Cassandra Interview Questions CherryPy Interview Questions Clojure Interview Questions Cobol Interview Questions CodeIgniter interview Questions CoffeeScript Interview Questions Cordova Interview Questions CouchDB interview questions CSS Buttons Interview Questions CSS Interview Questions D Programming Language Interview Questions Dart Programming Language Interview Questions Data structure & Algorithm Interview Questions DB2 Interview Questions DBMS Interview Questions Django Interview Questions Docker Interview Questions DOJO Interview Questions Drupal Interview Questions Electron Interview Questions Elixir Interview Questions Erlang Interview Questions ES6 Interview Questions and Answers Euphoria Interview Questions ExpressJS Interview Questions Ext Js Interview Questions Firebase Interview Questions Flask Interview Questions Flex Interview Questions Fortran Interview Questions Foundation Interview Questions Framework7 Interview Questions FuelPHP Framework Interview Questions Go Programming Language Interview Questions Google Maps Interview Questions Groovy interview Questions GWT Interview Questions Hadoop Interview Questions Haskell Interview Questions Highcharts Interview Questions HTML Interview Questions HTTP Interview Questions Ionic Interview Questions iOS Interview Questions IoT Interview Questions Java BeanUtils Interview Questions Java Collections Interview Questions Java Interview Questions Java JDBC Interview Questions Java Multithreading Interview Questions Java OOPS Interview Questions Java Programming Coding Interview Questions Java Swing Interview Questions JavaFX Interview Questions JavaScript Interview Questions JCL (Job Control Language) Interview Questions Joomla Interview Questions jQuery Interview Questions js Interview Questions JSF Interview Questions JSP Interview Questions KnockoutJS Interview Questions Koa Interview Questions Laravel Interview Questions Less Interview Questions LISP Interview Questions Magento Interview Questions MariaDB Interview Questions Material Design Lite Interview Questions Materialize CSS Framework Interview Questions MathML Interview Questions MATLAB Interview Questions Meteor Interview Questions MongoDB interview Questions Moo Tools Interview Questions MySQL Interview Questions NodeJS Interview Questions OpenStack Interview Questions Oracle DBA Interview Questions Pascal Interview Questions Perl interview questions Phalcon Framework Interview Questions PhantomJS Interview Questions PhoneGap Interview Questions Php Interview Questions PL/SQL Interview Questions PostgreSQL Interview Questions PouchDB Interview Questions Prototype Interview Questions Pure CSS Interview Questions Python Interview Questions R programming Language Interview Questions React Native Interview Questions ReactJS Interview Questions RequireJs Interview Questions RESTful Web Services Interview Questions RPA Interview Questions Ruby on Rails Interview Questions SAS Interview Questions SASS Interview Questions Scala Interview Questions Sencha Touch Interview Questions SEO Interview Questions Servlet Interview Questions SQL Interview Questions SQL Server Interview Questions SQLite Interview Questions Struts Interview Questions SVG Interview Questions Swift Interview Questions Symfony PHP Framework Interview Questions T-SQL(Transact-SQL) Interview Questions TurboGears Framework Interview Questions TypeScript Interview Questions UiPath Interview Questions VB Script Interview Questions VBA Interview Questions WCF Interview Questions Web icon Interview Questions Web Service Interview Questions Web2py Framework Interview Questions WebGL Interview Questions Website Development Interview Questions WordPress Interview Questions Xamarin Interview Questions XHTML Interview Questions XML Interview Questions XSL Interview Questions Yii PHP Framework Interview Questions Zend Framework Interview Questions Network Architect Interview Questions

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 –

  1. SSIS service
  2. SSIS runtime engine & runtime executable
  3. SSIS dataflow engine & dataflow components
  4. 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:

  1. SQL Server
  2. File System
  3. 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.