Top 10 SSIS interview questions

SSIS Interview Questions

1) What happens if we enable checkpoint and transaction together in SSIS package?

Ans: Transaction always gets priority over checkpoint to fulfill acid property.

2) What happens if we enable checkpoint and the package fails after processing few rows of the data flow task.

Ans: The package will start from the beginning of the data flow task, i.e it will try to load all the rows again. To avoid this we can add audit columns and remove all the loaded rows or ignore from source which has already been loaded.

3) How do we handle parallel processing inside data flow task?

Ans:  By setting the property – EngineThreads.

4) Which service maintains transaction in SSIS?

Ans: Microsoft Distributed Transaction Coordinator

5. How To Handle Late Arriving Dimension Or Early Arriving Facts?

Ans: Late-arriving dimensions sometimes get unavoidable ‘coz delay or error in Dimension ETL or may be due to a logic of ETL. To handle Late Arriving facts, we can create a dummy Dimension with natural/business key and keep the rest of the attributes as null or default. And as soon as the Actual dimension arrives, the dummy dimension is updated with Type 1 change. These are also known as Inferred Dimensions. 

6. What kind of variables can you create?
Ans: You can create global variables and task level variables in SSIS. For programmers, these variables are the same as global and function level variables. A global variable is available to all tasks across the entire job. Variables created in tasks are only available within that task.

7. Difference between lookup and merge join

Ans:Lookup returns the first match value but merge join returns all matches. Merge join requires two inputs but lookup requires one input with one lookup source. Merge join has one output but lookup has match and no match. There is no caching option in merge join but we can cache the lookup data in lookup transformation