SSIS Interview Questions and Answers

In case you’re searching for SSIS Interview Questions and answers for Experienced or Freshers, you are at the correct place. There is a parcel of chances from many presumed organizations on the planet. The SSIS advertise is relied upon to develop to more than $5 billion by 2021, from just $180 million, as per SSIS industry gauges. In this way, despite everything you have the chance to push forward in your vocation in SSIS Development. Gangboard offers Advanced SSIS Interview Questions and answers that assist you in splitting your SSIS interview and procure dream vocation as SSIS Developer.

Best SSIS Interview Questions and Answers

Do you believe that you have the right stuff to be a section in the advancement of future SSIS, the GangBoard is here to control you to sustain your vocation. Various fortune 1000 organizations around the world are utilizing the innovation of SSIS to meet the necessities of their customers. SSIS is being utilized as a part of numerous businesses. To have a great development in SSIS work, our page furnishes you with nitty-gritty data as SSIS prospective employee meeting questions and answers. SSIS Interview Questions and answers are prepared by 10+ years experienced industry experts. SSIS Interview Questions and answers are very useful to the Fresher or Experienced person who is looking for the new challenging job from the reputed company. Our SSIS Questions and answers are very simple and have more examples for your better understanding.

By this SSIS Interview Questions and answers, many students are got placed in many reputed companies with high package salary. So utilize our SSIS Interview Questions and answers to grow in your career.  

Q1) What does SSIS Mean?

Answer: SSIS or in other words, SQL Server Integration Services, is a significant part of Microsoft SQL Server, which can be utilized to achieve an extensive range of data transformation and migration jobs.

Q2) What are the significant parts of the SSIS package?

Answer: The significant parts in the SSIS package are: Data flow,   Package Explorer, Event handler and Control flow.

Q3) What is Solution Explorer in SSIS?

Answer: Solution Explorer in SSIS stands for a screen where you can take a look and access all the data sources, projects, and other sorts of related files.

Q4) What is the meaning of data flow in SSIS?

Answer: Data flow in SSIS stands for the flow of data from the matching sources to the anticipated destinations.

Q5) What is “task” in SSIS?

Answer: A task in SSIS is nothing but the technique of any programming language that stands for an individual component of work.

Q6) What is an SSIS package?

Answer: An SSIS package is a prearranged set of connections like data flow elements, event handlers, variables, control events, and configurations. You collect them by the tools provided by SSIS.

Q7) What are different types of connections that support SSIS?

Answer: Different types of connection that support SSIS are: ODBC, Flat File, XML, Excel, OLEDB, .net SQLClient.

Q8) How many kinds of containers are present in SSIS?

Answer: In SSIS, a container represents a reasonable grouping of tasks, and it allows dealing with the scope of a task collectively. The kinds of containers in SSIS are Sequence container and Task host container.

Q9) What is the meaning of Precedence Constraint in SSIS?

Answer: Precedence Constraint in SSIS allows you to identify the reasonable sequence of tasks, and in the arrangement, they should be implemented.

Q10)  What are the kinds of variables in SSIS?

Answer: Variable in SSIS are utilized to store values as the system variable and user variable.

Q11) What does a checkpoint in SSIS mean?

Answer: Checkpoint in SSIS permits the project to start again from the point of breakdown. Checkpoint stores the details about the package execution; if the package runs effectively the checkpoint file is removed.

Q12) What are connection managers in SSIS?

Answer: While collecting data from different sources and writing it for a purpose, connection managers are useful.  Connection manager eases the connection to the system that incorporates informations like data provider, server name, etc.

Q13) What does SSIS breakpoint mean?

Answer: SSIS breakpoint allows you to suspend the implementation of the package in the business intelligence expansion stage.

Q14) What is event logging in SSIS?

Answer: Event logging in SSIS permits you to choose any particular event of a job and it is advantageous at what time you are getting trouble in your package.

Q15) What does logging mode property mean?

Answer: SSIS packages and all the connected jobs have a property known as logging mode.   This property allows three possible values in the shape of Enabled, Disabled and Parent Setting.

Q16) What is data flow buffer?

Answer: SSIS operates by means of buffers; it is a sort of an in-memory practical table to hold data.

Q17) Data checkpoint data is not saved for what?

Answer: Checkpoint data is not saved for Loop containers.

Q18) What do conditional split transactions in SSIS mean?

Answer: Conditional split transformation in SSIS verifies the prearranged condition, on the basis of the condition assessment.

Q19) What are the different types of Data viewers in SSIS?

Answer: Different types of data viewers are Grid, Column Chart, Histogram, and Scatter Plot.

Q20) What are the possible locations for saving the SSIS package?

Answer: SSIS package can be saved at SQL Server, File System or Package Store.

Q21) What is the Event Handlers tab in SSIS?

Answer: With the aid of the Event Handlers tab in SSIS, workflows can be easily configured to act in response to package events.

Q22) How you can inform your workforce about package failure?

Answer: You can add a Send Mail Task in the event handlers. Also, you can set the warning in the SQL Agent at what time the package fails to run.

Q23) How logging is done in SSIS?

Answer: Logging in SSIS can be done by means of events like on Warning, on Error, etc. to the manifold options like a SQL server table, XML, etc.

Q24) How is the SSIS package deployed on production?

Answer: In order to deploy the SSIS package, we have to implement the manifest files and need to settle on whether to deploy this onto SQL Server of into File System.

Q25) How to manage Late Arriving Dimension?

Answer: Late Arriving Dimension is inevitable; to manage these we need to build a model dimension with business key and maintain the remaining of the attributes as default.

Q26) How to perform incremental load?

Answer: The greatest and top way to perform incremental load is by utilizing a Timestamp column in the supply table and storing the final ETL timestamp.

Q27) What are three data flow constituents in SSIS?

Answer: Three data flow constituents are SSIS are Source, Transformation, and Destination.

Q28) Why check Points are utilized in SSIS?

Answer: Checkpoint utilized in SSIS permits a package to start again at the point of breakdown.

Q29) What are different alternatives for dynamic configuration is SSIS?

Answer: Different alternatives for dynamic configuration are   Customer variables, XML file, and Database per set with the variables

Q30) What is Data conversion Transformation?

Answer: Data conversion is the most excellent method to transmit the data from one category to another.  On the other hand, you need to ensure that you have well-matched data in the column.

Q31) What other tasks can SSIS handle?

Answer: Some of the other tasks that SSIS handles are data profiling, effectively streamline batch operations and system filing.

Q32) What do you understand by SSIS control flow system integration?

Answer: This factor consists of using the logical connectors between the tasks and thus graphically control the flow of data in the system.

Q33) What do you mean by data transformation?

Answer: When a series of rules or functions are applied to the extracted data from the source so that it can be loaded onto the end result, it is known as data transformation.

Q34) Can variables be created in SSIS?

Answer: Yes, variables can be created in SSIS.

Q35) Mention the two types of variables that can be created in SSIS?

Answer: The two variables that can be created in SSIS are task level variables and global variables.

Q36) What types of containers can be used with SSIS packages?

Answer: The types of containers that can be used are sequence containers and loop containers.

Q37) What do you mean by the Deployment process in SSIS?

Answer: When a package is transformed from the development mode into the executables mode, it is known as the Deployment process.

Q38) What do you understand by the Manifiest file in SSIS?

Answer: When the package is transferred using wizard on the file system and the SQL server, it is known as the Manifiest file.

Q39) What is known as the data flow task in SSIS?

Answer: When the data is moved from its source to its destination, this mechanism is known as the data flow task.

Q40) What is meant by data profiling task?

Answer: When source data is analyzed to understand it better in terms of hygiene, patterns, numbers, etc it is known as data profiling task.

Q41) When is the task of data profiling usually done?

Answer: The task of data profiling is generally carried out at the start of the development cycle so that any teething concerns can be immediately addressed.

Q42) What do you understand by a transaction in the SSIS package?

Answer: In order to maintain data integrity, packages use processes called transactions to bind the database actions.

Q43) What is the purpose of the config file in SSIS?

Answer: This is mainly used to provide different input to the connection manager about the different properties.

Q44) What is the difference between control flow and data flow?

Answer: Control flow is what determines the flow or process of the package. Data flow is a subset of the control flow. Without a control flow, the data flow cannot function.

Q45) What is a Checkpoint in SSIS?

Answer: The functionality or property in SSIS which allows the project (in case of failure) to restart from that point itself, is known as Checkpoint.

Q46) What is the main use of the SSIS package?

Answer: This is an upgraded version that allows the seamless merging of data from numerous sources. It is also used to clear up and analyze the existing data.

Q47) Name 4 important components of the SSIS package?

Answer: The four important components of the SSIS package are… Data Flow, Control Flow, Package Explorer, Event Handler

Q48) What is the solution explorer in SSIS?

Answer: The solution explorer in SSIS is a screen in which all the data, data sources, projects, etc. can be viewed.

Q49) What do you mean by a Task in SSIS?

Answer: Just as in any other programming language, a Task is what executes an individual unit of work

Q50) How many categories of tasks are there in SSIS?

Answer: There are two units of Tasks in SSIS… Control Flow tasks and Database Maintenance Tasks

Q51) What is Precedence Constraint in SSIS?

Answer: This process enables users to define the logical sequence of tasks to be executed.

Q52) What do you mean by connection managers in SSIS?

Answer: As the name suggests connection managers help facilitate the seamless connection of data from its source to the destination.

Q53) What do you understand by SSIS breakpoint?

Answer: When a package is getting uploaded in the business intelligence system and is suddenly stopped midway, that point is known as the breakpoint.

Q54) What is event logging in SSIS?

Answer: This is the term given when a specific task or event needs to be logged or selected. This is especially useful during any troubleshooting issues.

Q55) What is data flow buffer in SSIS?

Answer: In SSIS, this is a virtual, in-memory table that holds data.

Q56) Name the 4 data viewers in SSIS?

Answer: The four data viewers in SSIS include Grid, Histogram, Scatter Plot, Column Chart.

Q57) Mention three locations where the SSIS package can be saved?

Answer: The three locations where the SSIS package cane be potentially saved are SQL Server, Package Store, File System.

Q58) Which are the the three data flow components in SSIS?

Answer: The three data flow components in SSIS are Source, Transformation and Destination

Q59) Name three options for dynamic configuration in SSIS?

Answer: The three options for dynamic configuration in SSIS are… XML File, Customer Variables and one Database per environment in sync with the variables.

Q60) Mention some disadvantages of SSIS?

Answer: Some disadvantages of SSIS are that the vision and strategy are sometimes unclear. It can create problems in a non-Windows environment. It also does not yet provide support for alternative data integration methods.

Q61) Different types of debugging in SSIS?

There are 3 types of debugging

• Execute Package partially
• By using breakpoints
• By using Data Viewers

Q62) what is a checkpoint?

Using checkpoint we can restart the package from a failure of the task

Q63) what is package configuration?

The configuration provides user interaction to the package, so a user can control the package without
editing package from solution explorer.

Q64) What are the types of configurations in SSIS?

• XML file configuration
• SQL server DB configuration
• Parent package variable configuration
• Environment variable
• Registry entry.

Q65) What are the different types of command-line utilities in SSIS?

• DTEXEC
• DTEXECUI
• DTUTIL

Q66) What is the difference between merge and Union All?

• Merge will combine only two sorted dataset values.
• Union ALL will combine data from multiple data sets

Q67) How many types of containers?

For loop container – we can loop only for a specific number of times
For each loop container – Loop will be repeated based on the number of files
in the folder and we can traverse to subfolders with this container.
Sequence container – to group the certain the tasks
How to implement transaction control?
By setting the Transaction Option property to Required or Supported

Q68) How to schedule the package?

By using SQL Server Agent.

Q69) Difference between Full cache, partial cache and no cache?

• FULL cache – gets the complete reference data set from the database and places in memory and compares it with source data.
• Partial Cache – Here we can specify how much amount of memory that the reference dataset has to occupy.
• No Cache – Here data will not be cached. It is not recommended.

Q70) Difference between DTS and SSIS?

Everything is different except both are a product of Microsoft.

Q71) How to achieve parallelism in SSIS?

Using the MaxConcurrent Executable property of the package. Its default is -1 and it is calculated as a number of processors +2

Q72) Types of Loggings?

• Flat file
• SQL Server table
• XML
• SQL Profiler

Q73) What is CDC Splitter?

After data has read out of a table with CDC enable, this transform send data that
should be deleted inserted and updated down a different path.

Q74) What is OLEDB Command T\R?

Execute an OLEDB command for each row in the data flow.

Q75) What is the Dimension table?

it is a table which contains details data.

Q76) Different types of transformation in SSIS?

there are two different types of transformations

• Synchronous transformation’s
• Asynchronous transformation’s

Q77) What is the use CDC control task?

Using CDC we can Maintains and interacts with the change capture feature form SQL Server.

Q78) Difference between Merge join and lookup?

In Merge join, we can implement left outer join, right outer join and full outer. Whereas in lookup, we can perform inner join only.

Q79) What is the event handler?

An event handler is like a trigger in our SQL databases which will be executed automatically when an action takes place.

• Onerror
• Onwarning
• OnPreexecute
• OnPostExecute
• OnVariableValueChanged
• OnQueryCancled

Q80) Difference between control flow and data flow?

• Control flow is process-oriented and Data flow is information oriented.
• Data will not be moved from one task to the other task in control flow whereas data will be moved from one transformation to another transformation in the data flow.
• In control flow, we have Precedence constraints to set the task execution whereas in data flow we have data flow paths.

Q81) Different types of package storage mechanisms

• File system
• MSDB

Q82) List out different types of source systems you have used in your current project

• Flat file
• Excel
• Ole DB source
• ADO.NET

Q83) How would you do Logging in SSIS?

Logging configuration provides log the details of various events an inbuilt feature.

Q84) What is Integration Services?

It is a platform including packages that provide extract, building high-performance data integration solutions transformation and load processing for DWH.

Q85) What is the difference between union and merge in SSIS?

Merge: can take only 2 data sets, it requires the data to be sorted
Union: can take multiple inputs and doesn’t need the data to be sorted

Q86) How does sort transformation work in the ssis, how does it impact the performance?

Sort transformation sorts the data by reading all the rows and sorting them completely. When
performed on a large data set it slower. It is better to have the data sorted by the “order by” in the
SQL query.

Q87) What is Multithreading?

It is a process of processing the data-parallel to reduce the time of execution of the package and
to process a large amount of data faster.

Q88) In which isolation level phantom read does not occur?

Snapshot and Serializable

Q89) How to change the isolation level in SQL server?

This can be achieved by using the SQL statement

SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}

Q90) When is the activation of the rcsi take place?

The activation of this has to be performed manually. It will not be created during the creation of the Database.
It can be done by using the query

USE Master;
  GO
  ALTER DATABASE demo_db SET READ_COMMITTED_SNAPSHOT ON;
  GO
  USE demo_db;
  GO

Q91) What does the master database hold?

It has information such as logins, linked servers, endpoints, system configuration settings.
information about the other databases on their instances and their physical file locations.

Q92) How to perform data validation in SSIS?

This can be performed by using a script task, and writing code in c# or VB to validate the data
and give the result.

Q93) Can the SSRS RDL in a report server be exported to EXCEL in from a SSIS package .?

Yes, it can be performed by using a Script task.

Q94) Define the limitation and advantage in using the SQL command as an input source when input in the SQL data source in a data flow task?

Using a SQL command to extract the data from the SQL server is efficient in reducing the load on the site and reduces the execution time. it helps us prevent the use of separate ssis tasks for merging and joining and union. Its limitation is that the typing space is limited to a certain number and the query cannot be bigger. In that instance, it can be defined as a stored procedure and can be called in the SQL command.

Q95) How do we create an excel file and the sheet with defined column and data type in SSIS?

It can be done by using the create table statement in execute the SQL task component and connecting it to an empty excel connection.

Q96) Name a few blocking transformations?

Sorting and aggregation.

Q97) Can the data be viewed as between data tasks?

Yes data can be viewed by using “enable data viewer”

Q98) Name the ssis data types for the varchar and nvarchar data types of SQL server?

Varchar – > dt_str
Nvarchar – > dt_wstr

Q99) What are the deployment models in SSIS?

Package deployment and project deployment models.