SSIS Interview Questions - Part 4

Q:  How do you eliminate quotes from being uploaded from a flat file to SQL Server?
This can be done using Text Qualifier property. In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.

Q: What is Data Viewer and what are the different types of Data Viewers in SSIS?
A Data Viewer allows viewing data at a point of time at run-time. If data viewer is placed before and after the Aggregate transform, we can see data flowing to the transformation at the run-time and how it looks like after the transformation occurred.
The different types of data viewers are:

• Grid
• Histogram
• Scatter Plot
• Column Chart.

Q: What are variables and what is variable scope?
A variable is used to store values. There are basically two types of variables:

• System Variable: (like ErrorCode, ErrorDescription, PackageName etc) whose values you can use but cannot change.
• User Variable: which you create, assign values and read as needed. A variable can hold a value of the data type you have chosen when you defined the variable.

Variables can have a different scope depending on where it was defined. For example you can have package level variables which are accessible to all the tasks in the package and there could also be container level variables which are accessible only to those tasks that are within the container.

Q: Difference between Union All and Merge Join?

• Merge transformation can accept only two Inputs whereas Union All can take more than two Inputs
• Data has to be sorted before Merge Transformation whereas Union All doesn't have any condition like that.

Q: Can we add our custom code in SSIS?
We can customize SSIS through code by using Script Task. The main purpose of this task is to control the flow of the package. This is very useful in the scenario where the functionality you want to implement is not available in existing control flow item.
To add your own code:-

• In Control Flow Tab, drag and drop Script Task from Toolbox.
• Double click on Script Task to open and select edit to open Script Task Editor.
• In Script Task Editor, there are 3 main properties
• General – Here you can specify name and description
• Script – through this we can add our code by clicking on Design Script button. The scripting language present is Net only.
• Expression

Q What is Ignore Failure option in SSIS?
In Ignore Failure option, the error will be ignored and the data row will be directed to continue on the next transformation. Let’s say you have some JUNK data(wrong type of data or JUNK data) flowing from source, then using this option in SSIS we can REDIRECT the junk data records to another transformation instead of FAILING the package. This helps to MOVE only valid data to destination and JUNK can be captured into separate file.

Q: What is a breakpoint in SSIS? How is it setup? How do you disable it?
A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an
opportunity to review the status of the data, variables and the overall status of the SSIS package. 10 unique conditions exist for each breakpoint.
Breakpoints are setup in BIDS. In BIDS, navigate to the control flow interface. Right click on the object where you want to set the breakpoint and select the ‘Edit Breakpoints…’ option.

Q: Error Handling in SSIS?
An error handler allows us to create flows to handle errors in the package in quite an easy way. Through event handler tab, we can name the event on which we want to handle errors and the task that needs to be performed when such an error arises. We can also add sending mail functionality in event of any error through SMTP Task in Event handler. This is quite useful in event of any failure in office non-working hours. In Data flow, we can handle errors for each connection through following failure path or red arrow.

Q: How to log SSIS Executions?
SSIS includes logging features that write log entries when run-time events occur and can also write custom messages. This is not enabled by default. Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.

Q: Can you explain how to setup a checkpoint file in SSIS?
The following items need to be configured on the properties tab for SSIS package:
CheckpointFileName: Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it’s a good idea to use an expression that concatenates a path defined in a package variable and the package name.

• CheckpointUsage: Determines if/how checkpoints are used. Choose from these options: Never(default), IfExists, or Always. Never indicates that you are not using Checkpoints. IfExists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist.
• SaveCheckpoints: Choose from these options: True or False (default). You must select True to implement the Checkpoint behavior.

Q: What are the different values you can set for CheckpointUsage property ?
There are three values, which describe how a checkpoint file is used during package execution:

• Never: The package will not use a checkpoint file and therefore will never restart.
• If Exists: If a checkpoint file exists in the place you specified for the CheckpointFilename property, then it will be used, and the package will restart according to the checkpoints written.
• Always: The package will always use a checkpoint file to restart, and if one does not exist, the package will fail.

Q: How would you restart package from previous failure point?What are Checkpoints and how can we implement in SSIS?
When a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When the failed package is rerun, the checkpoint file is used to restart the package from the point of failure. If the package runs successfully, the checkpoint file is deleted, and then re-created the next time that the package is run.

Q: How do you deploy SSIS packages?

• Through Manifest
• Create deployment utility by setting its propery as true .
• It will be created in the bin folder of the solution as soon as package is build.
• Copy all the files in the utility and use manifest file to deply it on the Prod.
• Using DtsExec.exe utility
• Import Package directly in MSDB from SSMS by logging in Integration Services.

Q Where are SSIS package stored in the SQL Server?
MSDB.sysdtspackages90 stores the actual content and ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog do the supporting roles.

Q How would you schedule a SSIS packages?
Using SQL Server Agent. Read about Scheduling a job on Sql server Agent

Q Can you name five of the Perfmon counters for SSIS and the value they provide?

• SQLServer:SSIS Service
• SSIS Package Instances
• SQLServer:SSIS Pipeline
• BLOB bytes read
• BLOB bytes written
• BLOB files in use
• Buffer memory
• Buffers in use
• Buffers spooled
• Flat buffer memory
• Flat buffers in use
• Private buffer memory
• Private buffers in use
• Rows read
• Rows written

<< Prev