We have a few SSIS Interview Questions and Answers on our website but frequently people asked for common examples of scenario based interview Q&As which is not the same; so in this article I will give you several real life tasks. It won't be simple examples but will be real life so prepare yourself!
Before I begin let me clarify that SSIS is frequently either Data Migration, Data Warehousing or Reporting (which may not involve a data warehouse) and I mainly do Data Warehouse projects so I will focus on these type of situations. I will only provide scenarios and I will not provide answers as
You got a job as a SQL/SSIS Developer to help XYZ Company with their reporting. The company currently uses SQL + Excel to do reporting from multiple sources of data. The maintenance effort and complexity increases at rapid pace and the company decided to employ you to sort out all their problems.
Phase 1 Objectives
You are lucky and you have been given objectives for phase 1 of the projects which are:
- Eliminate manual data manipulation in excel.
- All reporting should be possible using SQL only.
- You need to capture source systems changes in the fastest possible way.
It's only you and you have 4 weeks to make major improvements for key reports that are produced on a daily or weekly basis.
Key Design Decisions
The company had a consultant for several days before they employed you and agreed that building a proper data warehouse would be ideal but would take months, cost a lot of money and essentially the company said they cannot justify this investment and cost and have money only for one permanent employee with salary below market rate... that's potentially you :)
The consultant suggested that best approach with one SQL/SSIS Developer is to consolidate all data in one database called Operational Data Source (ODS) + snapshot tables to capture history in a very quick way (small amount of data) and suggested that after a few weeks or months the company should re-consider a data warehouse.
Your Real Life Tasks
Now that you have key information about the project it's time to give you some more specific scenarios so you can provide your answers to the person who interviews you.
Scenario one - Kick off
Imagine you arrive at work, you get a standard laptop, you switch it on and realize you only have SSMS Client and nothing else. You go to your line manager and with a big surprise on his face he asks you so what do you need?
What would you requests?
..... For now I will only provide scenarios and one I find more time I will provide answers.
Scenario two - More preparations
You will be developing a solution using SQL Sever 2014 and Visual Studio 2013 Professional Edition. The company uses only Microsoft products.
Which tool would you use for version control and which tool / project would you use for database development?
Scenario three - First SSIS request
The biggest reporting challenge is that some data exists in files and spreadsheets and Data Analysts are not able to query it using SQL. Your first task to put all data into SQL Tables.
- What kind of questions would you ask Data Analysts to meet their requirements?
- Describe high level design of SSIS Packages you would develop.
- Describe how would test it.
- Describe how you would communicate the progress and when you would know when it is ready to be deployed to production server.
- Describe how you would deploy it.
Scenario four - Single and Multiple files load
The company downloads files from a website on usually a daily basis and they want an easy way to append data into SQL Tables.
The data is usually download daily but occasionally someone is off sick or also this is not done during the weekend so on Monday they are 3 files to load (Friday, Saturday and Sunday's data).
- Describe on high level SSIS Design.
- How would you ensure data is appended without duplications?
- How would you ensure data is not lost (failures) or forgotten (user error)?
- How would you ensure files are not accidently overwritten by users.
- How would you ensure files are for valid date?
- If something goes wrong and invalid file is loaded how can a user reload it and ensure data in table is reliable?
That's all for now I hope you have found it useful.