TOP SSIS Interview Questions and Answers
What is SSIS breakpoint?
A breakpoint enables you to pause the execution of the package in business intelligence development studio during troubleshooting or development of an SSIS package.
What is event logging in SSIS?
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.
What is logging mode property?
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
What is a data flow buffer?
SSIS operates using buffers; it is a kind of an in-memory virtual table to hold data.
For what data checkpoint data is not saved?
Checkpoint data is not saved for ForEach Loop and ForLoop containers.
What is conditional split transactions in SSIS?
Conditional split transformation in SSIS is just like IF condition, which checks for the given condition based on the condition evaluation.
List out the different types of Data viewers in SSIS?
Different types of data viewers in SSIS include
- Scatter Plot
- Column Chart
What are the possible locations to save SSIS package?
You can save SSIS package at
- SQL Server
- Package Store
- File System
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?
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.
What is the role of Event Handlers tab in SSIS?
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.
How you can notify the staff members about package failure?
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.
How would you do logging in SSIS?
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.
How would you deploy an SSIS package on production?
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.
How to handle Early Arriving Facts or Late Arriving Dimension?
Late Arriving Dimension are 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.
How can you do an incremental load?
The best and fastest way to do incremental load is by using Timestamp column in the source table and storing the last ETL timestamp.