SSIS Interview Questions

Control Flow

Question: Difference between Dataflow Task and Control Flow Task?
Question: What is the control flow Task?
Question: What is the difference between for Loop and for each loop container?
Question: What are different iterations in For Each LOOP container?
Question: What is Sequence Container and how it works?
Question: Difference between File System and FTP Task?
Question: What is Execute SQL Task?
Question: Difference between Execute SQL Task and Execute TSQL-Statement Task?


Variable

What is variable in SSIS, what are data types available for variable?
What is Scope of Variable? How can we change Scope of variable in SSIS 2008 and SSIS 2012?
What are expressions in SSIS? Where and Why do we need to use them?
Can we write expression on Variable? If yes, how and where can you write them?
Can we write expression on Connection Managers? If yes, where would you need to do that?
What are System Variables in SSIS? Name few of them those you have used.
Can we write expression on Precedence Constraint? if yes, how would you do it?
What is the maximum length of expressions in SSIS 2008 and SSIS 2012?
What is new Expression Task in SSIS 2012?
What are Parameters in SSIS 2012? how are they different from Variables?
Can you create two variables with same in name in SSIS Package?


Data Flow

Question: What is data flow Task?
Question: What is the different type of data sources in Dataflow task?
Question: What is Difference between Ado.net Destination and Record set Destination?
Question: Explain XML data source and how it works?
Question: What all the transformation you have used in dataflow task?
Question: Explain alternative method to use XML as a source?
Question: What is fuzzy Lookup and FuzzyGrouping? Explain difference?
Question: DataConversion and Derived column explain?
Question: What is Multicast?
Question:Difference between Union All and Merge Join?
Question: Difference between OLEDB Destination, SQL Server Destination and SQL Server Compact Destination?
Question:Difference between Merge and Merge Join?
Question: What is Cache Transformation? How to use it Explain?
Question: What isOledb Command transformation in SSIS used for?
Question: Explain the Lookup Transformation?
Question: Difference between the data type DT_STR and DT_WSTR?
Question: What is slowly changing dimensions?
Question: What isAnalysis Services Execute DDL task?
Question: What is Data profiling Task?

Question: What is data mining Query Task?
Question: What is Script Task?
Question: What is Script Component?
Question: How to use script component as Source and Destination?
Question: What are the languages available for coding in Script Task?
Question: How to Handle Object Variable in Script Task?
Question: What are the steps to use a package Variable in Script Task?


Difference between SSIS and DTS?
During an SSIS package execution, how are SQL database errors handled? Givean example of a handled and unhandled error event in a well designed package.
What is a data viewer in SSIS and its uses?
What to insert a new column in the dataset data flow?
What is a multicast?
Multicast More Detail


Explain star schema, snowflake schema, fact and dimension table?

What is lookup transformation?

Connection Manager

Question: What is Config File in SSIS?
How to configure package properties at runtime?
Question: What are the different types of Config options available in SSIS package?
Question: Explain Connection Manager?
Question: How to change the connection manager values at run time?
Question: How to add logging in SSIS?
Question: What are some ways in which a connection task can be set dynamically within an SSIS package?
Question: What are the ways to store logs in SSIS packages?
Question: What is annotation in SSIS Package?

The SSIS Designer provides annotations, which you can use to make packages self-documenting and easier to understand and maintain. You can add annotations to the control flow, data flow, and event handler design surfaces of SSIS Designer. The annotations can contain any type of text, and they are useful for adding labels, comments, and other descriptive information to a package. Annotations are a design-time feature only. For example, they are not written to logs.


Package Fundamentals


Transaction in SSIS
Question: How to implement Transaction in SSIS package?
Question: Can we implement transactions in SSIS package?
Question: What are different transaction Options in SSIS?
Question Which control flow tasks doesn't support transactions?
Question: How to implement isolation level in SSIS package?
Question: What is chaos in isolation level options in SSIS?


Checkpoint

Question: What are the checkpoints in SQL Server?
Question: What is checkpoint file?
Question: How do checkpoints behave in case of For Loop and For Each loop?
Question: Case study how isolation works in SSIS?


Package Deployment

Question: How to deploy the packages SSIS?
Question: Where a package does deploys in Integrationservice database?
Question: What is Manifest file in SSIS?
Question: What is File System Deployment?
Question: how to back up or retrieve the SSIS package?
Question: What are the different databases in Integration services while login through SSMS?


Package Security

Name the two Integration Services Security Properties.
Question: How to implement the security in SSIS package?
Question: What is the protection Level in SSIS package and what are the different types of protection available?
Question: How to implement database level package security?
Question: What is event handler Tab in Design window?
Question: What is SSIS package?
Question: What are the other part tabs of package design window?


Performance of SSIS Package
Question:Explain the performance tuning tips for SSIS Package?
Explain parallel Execution and no of package can participate
Question: What areBlocking, Partial blocking and Row version transformation?
Question: What is parallelism in SSIS how to implement it?
Question: What are the properties which can be modified to maintain buffersize?
Question: Properties to modified packet size on network?
Question: What is Execution Tree?


Error Handling

Scenario Based Questions


Which task will you use in a package to find corrupt or incomplete information in data?
What is the alternative for ‘Data driven query’ task from DTS in SSIS?
How can we remove last row from a CSV file in SSIS in data flow task ?
What is a UDL and what advantages does it have?
What is ‘ragged right’ format in flat files?
Difference between Checkpoint and Breakpoint?
How to run your SSIS package on a schedule?
What is the difference between Conditional Split and Multicast Transformation?
How to use Stored Procedure in SSIS package in dataflow task?
How to remove duplicate record in SSIS?


While you use sort transformation there is check box which says remove duplicates.
How can a “Data driven query” task from DTS beimplemented using the SSIS transform objects?
What are the four (4) migration tools available to Integration Services package designers that aid in migrating SQL Server2000 DTS packages?


New feature in SSIS 2008 vs SSIS 2005?


New feature in SSIS 2012 vs SSIS 2008?