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
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
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
