SSIS Interview Questions for Basic Concepts and Event Logging

By:   |   Comments (11)   |   Related: 1 | 2 | 3 | 4 | More > Professional Development Interview Questions BI


When you are preparing for an SSIS interview you need to understand what questions could be asked in the interview. In this tip series, I will try to cover as much as I can to help you prepare for SSIS interview. In this tip we will cover SSIS basics and SSIS event logging.


What are SSIS Connection Managers?

What is the RetainSameConnection property and what is its impact?

  • Whenever a task uses a connection manager to connect to source or destination database, a connection is opened and closed with the execution of that task. Sometimes you might need to open a connection, execute multiple tasks and close it at the end of the execution. This is where RetainSameConnection property of the connection manager might help you. When you set this property to TRUE, the connection will be opened on first time it is used and remain open until execution of the package completes.

What are a source and destination adapters?

  • A source adaptor basically indicates a source in Data Flow to pull data from. The source adapter uses a connection manager to connect to a source and along with it you can also specify the query method and query to pull data from the source.
  • Similar to a source adaptor, the destination adapter indicates a destination in the Data Flow to write data to. Again like the source adapter, the destination adapter also uses a connection manager to connect to a target system and along with that you also specify the target table and writing mode, i.e. write one row at a time or do a bulk insert as well as several other properties.
  • Please note, the source and destination adapters can both use the same connection manager if you are reading and writing to the same database.

What is the Data Path and how is it different from a Precedence Constraint?

  • Data Path is used in a Data Flow task to connect to different components of a Data Flow and show transition of the data from one component to another. A data path contains the meta information of the data flowing through it, such as the columns, data type, size, etc. When we talk about differences between the data path and precedence constraint; the data path is used in the data flow, which shows the flow of data. Whereas the precedence constraint is used in control flow, which shows control flow or transition from one task to another task.

What is a Data Viewer utility and what it is used for?

  • The data viewer utility is used in Business Intelligence Development Studio during development or when troubleshooting an SSIS Package. The data viewer utility is placed on a data path to see what data is flowing through that specific data path during execution. The data viewer utility displays rows from a single buffer at a time, so you can click on the next or previous icons to go forward and backward to display data. Check out the Data Viewer enhancements in SQL Server 2012.

What is an SSIS breakpoint? How do you configure it? How do you disable or delete it?

  • A breakpoint allows you to pause the execution of the package in Business Intelligence Development Studio during development or when troubleshooting an SSIS Package. You can right click on the task in control flow, click on Edit Breakpoint menu and from the Set Breakpoint window, you specify when you want execution to be halted/paused. For example OnPreExecute, OnPostExecute, OnError events, etc. To toggle a breakpoint, delete all breakpoints and disable all breakpoints go to the Debug menu and click on the respective menu item. You can event specify different conditions to hit the breakpoint as well. To learn more about breakpoints, refer to Breakpoints in SQL Server 2005 Integration Services SSIS.

What is SSIS event logging?

  • Like any other modern programming language, SSIS also raises different events during package execution life cycle. You can enable or write these events to trace the execution of your SSIS package and its tasks. You can also can write your custom message as a custom log. You can enable event logging at the package level as well as at the tasks level. You can also choose any specific event of a task or a package to be logged. This is essential when you are troubleshooting your package and trying to understand a performance problem or root cause of a failure. Check out this tip about Custom Logging in SQL Server Integration Services SSIS.

What are the different SSIS log providers?

  • There are several places where you can log execution data generated by an SSIS event log:
    • SSIS log provider for Text files
    • SSIS log provider for Windows Event Log
    • SSIS log provider for XML files
    • SSIS log provider for SQL Profiler
    • SSIS log provider for SQL Server, which writes the data to the msdb..sysdtslog90 or msdb..sysssislog table depending on the SQL Server version.

How do you enable SSIS event logging?

  • SSIS provides a granular level of control in deciding what to log and where to log. To enable event logging for an SSIS Package, right click in the control flow area of the package and click on Logging. In the Configure SSIS Logs window you will notice all the tasks of the package are listed on the left side of the tree view. You can specifically choose which tasks you want to enable logging. On the right side you will notice two tabs; on the Providers and Logs tab you specify where you want to write the logs, you can write it to one or more log providers together. On the Details tab you can specify what events do you want to log for the selected task.
  • Please note, enabling event logging is immensely helpful when you are troubleshooting a package, but also incurs additional overhead on SSIS in order to log the events and information. Hence you should only enabling event logging when needed and only choose events which you want to log. Avoid logging all the events unnecessarily.

What is the LoggingMode property?

  • SSIS packages and all of the associated tasks or components have a property called LoggingMode. This property accepts three possible values: Enabled - to enable logging of that component, Disabled - to disable logging of that component and UseParentSetting - to use parent's setting of that component to decide whether or not to log the data.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, February 21, 2020 - 11:03:15 AM - VISHAL DANDGE Back To Top (84693)

What if I have set the UseParentSetting on parent container? From where it takes the logging information.

Sunday, September 8, 2013 - 4:33:15 AM - yogi Back To Top (26666)

very helpful article


Monday, August 13, 2012 - 2:49:01 AM - Arshad Back To Top (19012)

From an end-user perspective DTS and SSIS may appear similar, however they are actually quite different. SSIS has been completely written from scratch and overcomes several limitations of DTS. Though the list of differences between DTS and SSIS is quite large, something of note is that the internal architecture of SSIS is completely different from DTS. It has segregated the Data Flow Engine from the Control Flow Engine or SSIS Runtime Engine and hence improves the performance by a significant amount.

You can refer these links for more information:

Friday, July 27, 2012 - 1:27:00 AM - Dinesh Dattatray Vishe Back To Top (18820)

What is new feature in SSIS than DTS ??/

Wednesday, July 25, 2012 - 2:12:26 AM - Arshad Back To Top (18792)

Yes to some extent its similar. Right click on the task, click on Edit Breakpoints and then specify the break condition to let debugger stop during debugging in BIDS. If you are using script task, you can hit F9 to set a breakpoint on a line.

Once debugger stops at breakpoint you can look into Local window to verify values of different variable.

If you want to look into data passing from source to destination you need to place data viewer on the data path connecting source and destination.

Hope it helps.

Tuesday, July 24, 2012 - 1:20:33 AM - Dinesh Dattatray Vishe Back To Top (18760)

IS that debugging Feature like visual studio in SSIS package ?

Monday, July 23, 2012 - 11:46:29 AM - Arshad Back To Top (18752)

You can use failure precedence constraints to call another task when the existing task fails and you can then log the failure information.

You can also use package event logging to log task or package failure events to text files, SQL Server tables, event viewer etc. Let me know if you want to know more detail about it.

Sunday, July 22, 2012 - 10:19:35 PM - DINESH VISHE Back To Top (18733)

How We can Check wheather SSIS PAchakge Run Sucessfully ??

Monday, June 11, 2012 - 4:31:51 AM - kallol ghosh Back To Top (17922)

very good journal....thanks for sharing

Wednesday, January 18, 2012 - 10:46:36 AM - Jason Yousef Back To Top (15698)

Thanks again for the great article about SSIS :)

Friday, December 9, 2011 - 8:49:03 AM - MikeA Back To Top (15327)

Thanks for the questions and answers.

get free sql tips
agree to terms