SSIS Interview Questions for Basic Concepts and Event Logging


By:   |   Updated: 2011-12-06   |   Comments (10)   |   Related: 1 | 2 | 3 | 4 | More > Professional Development Interview Questions BI

Problem

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.

Solution

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


Last Updated: 2011-12-06


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Sunday, September 08, 2013 - 4:33:15 AM - yogi Back To Top

very helpful article

 


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

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:

http://www.mssqltips.com/sqlservertutorial/200/sql-server-integration-services-ssis/

http://www.sql-server-performance.com/2009/SSIS-Introduction-Part1/

http://arshadali.blogspot.com/search/label/SSIS?updated-max=2009-01-27T11:25:00-08:00&max-results=20&start=20&by-date=false


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

What is new feature in SSIS than DTS ??/


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

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

IS that debugging Feature like visual studio in SSIS package ?


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

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

How We can Check wheather SSIS PAchakge Run Sucessfully ??


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

very good journal....thanks for sharing


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

Thanks again for the great article about SSIS :)


Friday, December 09, 2011 - 8:49:03 AM - MikeA Back To Top

Thanks for the questions and answers.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools