SSIS Interview Question and Answers Part 2

1. Logging. Different types of Logging files
Logging is used to log the information during the execution of package.
A log provider can be a text file, the SQL Server Profiler, a SQL Server relational database, a Windows event log, or an XML file. If necessary, you can define a custom log provider (e.g., a proprietary file format).

2. How to debug a package

For debugging a package, we have 3 options:
-Setting breakpoints in a package, container or Task
-Using Data Viewer in the Data flow task
-Setting Error redirection in the Data Flow task

3. What is the LoggingMode property?
SSIS packages, tasks and containers have a property called LoggingMode. This property accepts 3 possible values:
Enabled - to enable logging of that component
Disabled - to disable logging of that component
UseParentSetting - to use parent's setting of that component to decide whether or not to log the data.

4. How to configure Error Output in SSIS
We have 3 options to configure error output in ssis.
1. Ignore Failure
2. Redirect Row
3. Fail Component

For Example:

I have comma-separated value in a flat file with two columns (code, Name). Code is an integer value and name is a varchar(20) data type configured in the flat file connection manager. Some of the codes  in the flat files are characters. So, flat file reader component will fail reading the character value. But, I want to redirect the error data to separate table.

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

6. Incremental Load in SSIS

Using Slowly Changing Dimension
Using Lookup and Cache Transformation

7. Difference between Synchronous and Asynchronous Transformation

Synchronous T/F process the input rows and passes them onto the data flow one row at a time.

When the output buffer of Transformation created a new buffer, then it is Asynchronous transformation. Output buffer or output rows are not sync with input buffer.

8. What are Row Transformations, Partially Blocking Transformation, Fully Blocking Transformation with examples.

In Row Transformation, each value is manipulated individually. In this transformation, the buffers can be re-used for other purposes like following:

OLEDB Datasource,         OLEDB Data Destinations

Other Row transformation within the package,     Other partially blocking transformations within the package.

examples of Row Transformations: Copy Column, Audit, Character Map

Partially Blocking Transformation:

These can re-use the buffer space allocated for available Row transformation and get new buffer space allocated exclusively for Transformation.

examples: Merge, Conditional Split, Multicast, Lookup, Import, Export Column

Fully Blocking Transformation:

It will make use of their own reserve buffer and will not share buffer space from other transformation or connection manager.

examples: Sort, Aggregate, Cache Transformation

9. Difference between FileSystem and Sql server Deployment

File System Deployment: We can save the package on a physical location on hard drive or any shared folder with this option, and we should provide a full qualified path to stored package in the FileSystem option.
Sql Server Deployment: SSIS packages will be stored in the sysssispackages table of MSDB Database.

10. Difference between Lookup and Fuzzy Lookup transformation

Lookup Transformation finds the exact match.
Fuzzy Lookup transformation matches input table with reference table. It finds the closest match and indicates the quality of the match.