TCS Interview Questions And Answers

1. SSIS logging

Logging is used to log the information during 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. Error handling

Error Handling can be achieved in Data Flow Task by configuring Error Output.

--> 3 actions can be performed whenever some error occurs

- Ignore Failure

- Redirect Row

- Fail Component

--> By default Fail Component is being used by SSIS

--> Ignore Failure - if error occurs, SSIS will ignore that error and move ahead

--> Redirect Row - if error occurs, SSIS moves those erroneous (bad) records to different destination and correct records to actual destination.

3. How to use inner join in ssis transformations?

By using merge join transformation.

4. Drill down report

Drilldown reports initially hide complexity and enable the user to toggle conditionally hidden report items to control how much detail data they want to see. 

5. Drill through report

Drill through reports commonly contains details about an item that is contained in an original summary report.

6. Lined report?

Linked report provides an access for existing report. Linked report nothing but a replica of master report. You can create a linked report when you want to create additional versions of an existing report.

How to create a linked report:

      i.In Report Manager, navigate to the folder containing the report that you want to link to, and then open the options menu can click Create Linked Report.

     ii.Type a name for the new linked report. Optionally type a description.

     iii.To select a different folder for the report, click Change Location. Click the folder you want to use, or type the folder name in the Location box. Click OK.  If you do not select a different folder, the linked report is created in the current folder (where the report it is based on is stored).

     iv.Click OK.  The linked report opens.

     v.A linked report's icon differs from other items managed by a report server. The following icon indicates a linked report: 

7. Sub report

A sub report displays another report inside the body of a main report. The sub report can use different data sources than the main report. We can set up the parent report to pass parameters to the sub report.

8. Merge and merge join

Merge	Merge Join
The data from 2 input paths are merged into one	The data from 2 inputs are merged based on some common key.
Works as UNION ALL	JOIN (LEFT, RIGHT OR FULL)
Supports 2 Datasets	Supports 1 Datasets
Metadata for all columns needs to be same	Key columns metadata needs to be same.
Data must be sorted.	Data must be sorted.
Does not support error handling	Does not support error handling

9. Aggregation transformation

The Aggregate transformation applies aggregate functions, such as Average, Count, Count Distinct, Sum, Minimum and Maximum.

Group By: Divides datasets into groups. Columns of any data type can be used for grouping.
Sum: Sums the values in a column. Only columns with numeric data types can be summed.
Average: Returns the average of the column values in a column. Only columns with numeric data types can be averaged.
Count: Returns the number of items in a group.
Count distinct: Returns the number of unique non null values in a group.
Minimum: Returns the minimum value in a group. This operation can be used only with numeric, date, and time data types.
Maximum: Returns the maximum value in a group. This operation can be used only with numeric, date, and time data types.

10. Conditional Split Transformation in SSIS

Conditional split is used to route data rows to different outputs based on conditions. 

11. User defined function?

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, and Delete records in the database table. 

12.How can u design SCD in SSIS?

ANS:  Def:-SCD explains how to capture the changes over the period of time.
This is also known as change data capture.
type1: It keeps the most recent values in the target. It does not maintain the history.
type2: It keeps the full history in the target database. For every update in the source a new

record is inserted in the target.
type3: It keeps current & previous information in the target.