This is just a starting point for you to prepare for your interview so make sure not to limit yourself to what you find here. These answers could easily vary but generally someone who is interviewing is looking not only for a correct answer but confidence in your answer. If you have any questions about my answers or have something to add feel free to do so, but I did try to keep the answers short and sweet.
SSIS Interview Questions1. What does a control flow do?
A control flow manages the workflow of the package. Pretty self-explanatory it controls the flow of how the package is run. Sorry to use the words control flow in the definition, but this is a real easy one.
2. Generically explain what happens inside a data flow task?
It moves data from nearly any source, performs different transforms to the data, then sends to nearly any destination.
3. Explain what ETL is?
Extract, Transform, and Load. Basically pulls the data from a location, change/transforms the data somehow, and then loads it in a new location.
4. Which task would you use to copy, move or delete files?
File System Task (This is just an example know all control flow tasks)
5. Which transform would you use to split your data based on conditions you define?
Conditional Split (This is just an example know all data flow transforms)
6. Explain the pros and cons of deploying to a file system vs msdb?
Functionality Best in File System Best in MSDB
Security X
Backup and Recovery X
Deployment X
Troubleshooting X
Execution Speed X X
Availability X
7. If you did not know the answer to a question what would be your next step to find the answer?
Search online, read a book, or ask for help. I generally would suggest it in this order.
