SSIS Interview Questions And Answers For Experienced

SSIS Interview Questions And Answers. If you are looking for SQL Server Integration Services Interview Questions, here is the comprehensive list from basic to most advanced SSIS interview questions for 2+, 3+, 4+, 5+, 6+ years of SSIS experienced professionals.

When attending interviews for experienced roles, you should know practical SSIS interview questions, and real-time SSIS scenario based interview questions. These SSIS interview questions for experienced will help you to crack your SSIS job interview.

SSIS Interview Questions

These are the basic interview questions on SSISSQL Server Integration Services. Go through this below list of beginner SSIS interview questions.

  1. Explain what is SSIS?
  2. Mention what are the important components of SSIS package?
  3. Explain what is Solution Explorer in SSIS?
  4. Explain what does it mean by data flow in SSIS?
  5. Define what is “task” in SSIS?

SSIS Package Interview Questions

  1. Explain what is SSIS package?
  2. List out different types of connection or files that support SSIS?
  3. Explain what is a container? How many types of containers are there in SSIS?
  4. Explain what is Precedence Constraint in SSIS?
  5. Explain what variables in SSIS and what are the types of variables in SSIS?

SSIS Checkpoint Interview Questions

  1. Explain what is a checkpoint in SSIS?
  2. Explain what is connection managers in SSIS?
  3. Explain what is SSIS breakpoint?
  4. Explain what is event logging in SSIS?
  5. Explain what is logging mode property?
  6. Explain what is a data flow buffer?

SSIS Split Interview Questions

  1. For what data checkpoint data is not saved?
  2. Explain what is conditional split transactions in SSIS?
  3. List out the different types of Data viewers in SSIS?
  4. Mention what are the possible locations to save SSIS package?
  5. 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?

SSIS Event Handler Interview Questions

  1. Explain what is the role of Event Handlers tab in SSIS?
  2. Explain how you can notify the staff members about package failure?
  3. Explain how would you do logging in SSIS?
  4. Mention how would you deploy a SSIS package on production?
  5. Explain how to handle Early Arriving Facts or Late Arriving Dimension?
  6. Explain how can you do an incremental load?

SSIS Interview Questions And Answers

Q) Explain what is SSIS?

A) 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.

Q) Mention what are the important components of SSIS package?

A) The important component in SSIS package are:

  • Data flow
  • Control flow
  • Package explorer
  • Event handler

Q) Explain what is Solution Explorer in SSIS?

A) 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.

Q) Explain what does it mean by data flow in SSIS?

A) Data flow in SSIS is nothing but the flow of data from the corresponding sources to the target destinations.

Q) Define what is “task” in SSIS?

A) 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
  • SSIS

Q) Explain what is SSIS package?

A) 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.

Advanced SSIS Interview Questions And Answers

Q) List out different types of connection or files that support SSIS?

A) Different types of connection that work within SSIS are:

  1. ODBC
  2. OLEDB
  3. .net SQLClient
  4. Flat File
  5. Excel
  6. XML

Q) Explain what is a container? How many types of containers are there in SSIS?

A) 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:

  1. Sequence container
  2. For loop container
  3. Foreach loop container
  4. Task host container

Q) Explain what is Precedence Constraint in SSIS?

A) 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.

Q) Explain what variables in SSIS and what are the types of variables in SSIS?

A) Variable in SSIS is basically used to store values. In SSIS, there are two types of variables system variable and user variable.

Q) Explain what is a checkpoint in SSIS?

A) 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.

Q) Explain what is connection managers in SSIS?

A) 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.

SSIS Interview Questions For Experienced

  1. How do I sort packages in Solution Explorer?
  2. How do I check to see if a record exists in my destination and update it, and if it doesn’t exist, insert it?
  3. Why are my mapped drives not working?
  4. Why is my SQL Server Destination component not working?
  5. Why is my Excel interop failing intermittently in my Script Task?
  6. How do I set a flat file name for flat file connection manager dynamically?
  7. How do I use dynamic T-SQL in Lookup?
  8. How do I use lookup transformation to load data incrementally?
  9. How do I perform data type conversions in SSIS?
  10. How do I create a folder for SSIS package in MSDB with T-SQL?

SSIS Practical Interview Questions

  1. How do I resolve the error “Connection is busy with results for another command”?
  2. How do I programmatically create a Transfer SQL Server Objects task?
  3. How do I execute a stored procedure with a return value and an output parameter in an Execute SQL Task?
  4. How do I use the Fuzzy Grouping transformation?
  5. How do I use Integration Services to achieve Push-mode processing for Analysis Services?
  6. How do read data from Excel 2007 with Integration Services 2005?
  7. How do I upgrade my DTS packages to SSIS 2008 packages?
  8. How do I troubleshoot SSIS packages failed execution in a SQL Agent job?
  9. How do I track the error message when my SSIS package failed execution?
  10. How do I troubleshoot a connection error regarding DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER ?

SSIS Scenario Based Interview Questions

  1. How do I troubleshoot SSIS package hanged for running?
  2. How do I use SSIS to completely transfer my database including logins, users, and permissions from one server to another server?
  3. How do I record log information in Script Component?
  4. How do I create and deploy a SSIS custom task?
  5. How do I improve package loading performance in BIDS when I open a package which is imported from another place?

SQL Server Integration Services Interview Questions

Q) Explain what is SSIS breakpoint?

A) A breakpoint enables you to pause the execution of the package in business intelligence development studio during troubleshooting or development of a SSIS package.

Q) Explain what is event logging in SSIS?

A) 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.

Q) Explain what is logging mode property?

A) SSIS packages and all the associated tasks have a property called LoggingMode. This property accepts three possible values:

Disabled: To enable logging of the component
Enabled: To disable logging of the component
UseParentSetting: To use parent’s setting of the component

Q) Explain what is a data flow buffer?

A) SSIS operates using buffers; it is a kind of an in-memory virtual table to hold data.

SSIS 2012 Interview Questions And Answers

Q) For what data checkpoint data is not saved?

A) Checkpoint data is not saved for ForEach Loop and ForLoop containers.

Q) Explain what is conditional split transactions in SSIS?

A) Conditional split transformation in SSIS is just like IF condition, which checks for the given condition based on the condition evaluation.

Q) List out the different types of Data viewers in SSIS?

A) Different types of data viewers in SSIS include:

  • Grid
  • Histogram
  • Scatter Plot
  • Column Chart

Q) Mention what are the possible locations to save SSIS package?

A) You can save SSIS package at:

  • SQL Server
  • Package Store
  • File System

SSIS Real Time Interview Questions

Q) 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?

A) 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.

Q) Explain what is the role of Event Handlers tab in SSIS?

A) 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.

Q) Explain how you can notify the staff members about package failure?

A) 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.

Q) Explain how would you do logging in SSIS?

A) Logging in SSIS can be done by logging various events like onError, onWarning, etc. to the various options like a flat file, XML, SQL server table, etc.

Q) Mention how would you deploy a SSIS package on production?

A) To deploy SSIS package we need to execute the manifest files and need to determine whether to deploy this into File System or onto SQL Server. Alternatively, you can also import package from SSMS from SQL Server or File System.

Q) Explain how to handle Early Arriving Facts or Late Arriving Dimension?

A) Late Arriving Dimension is unavoidable, to handle these we can create a dummy dimensions with natural/business key and keep the rest of the attributes as null or default.

So when actual dimension arrives, the dummy dimension is updated with Type 1 change. This is also referred as Inferred Dimensions.

Q) Explain how can you do an incremental load?

A) The best and fastest way to do incremental load is by using Timestamp column in the source table and storing the last ETL timestamp.

Happy Learning..!!!

References:

https://social.msdn.microsoft.com/

https://siri-kt.blogspot.in/

RELATED INTERVIEW QUESTIONS

  1. Accenture Java Interview Questions
  2. Advanced Java Interview Questions
  3. Core Java Interview Questions
  4. .NET Interview Questions
  5. Ansible Interview Questions
  6. ServiceNow Interview Questions
  7. RPA Interview Questions
  8. Blue Prism Interview Questions
  9. SSIS Interview Questions And Answers
  10. Oracle Performance Tuning Interview Questions
  11. SCCM Interview Questions
  12. ServiceNow Interview Questions
  13. SQL Interview Questions
  14. Docker Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *