SSIS interview questions and answers

My Learning: Log in Learning Report [Log in]

Remove Ads or Log in
Remove Ads or Log in
+3  |  -0

Here you will find "Mixed" level SSIS Interview questions and answers.

Questions are split into 3 levels:

  • For Freshers (Beginner Level),
  • For Experienced (Intermediate Level - 2 to 3 years of experience) and
  • Scenario based questions

SSIS Questions For Freshers

Question 1: What is the difference between Control Flow and Data Flow? [Answer]

Question 2: Name all types of containers in SSIS? [Answer]

Question 3: What is Precedence Constraint and give all available options. [Answer]

Question 4: Name all types of connections you have worked with in SSIS. [Answer]

SSIS Questions for Experienced

Question 5: What is the following SQL Server connections: OLEDB, ODBC and SQLClient? [Answer]

Question 6: Name all types of transformation you have worked with in SSIS. [Answer]

Question 7: What is the difference between Union and Merge? [Answer]

Question 8: What is the difference between Lookup and Merge Join? [Answer]

Question 9: What is a data flow buffer? [Answer]

Question 10: How can you ensure your package works when you move it to another server? [Answer]

Question 11: How can you deploy SSIS Package (or Project). [Answer]

SSIS Scenario based Questions

Visitors have asked for scenario based real life questions and we have done just that. Visit Scenario Based SSIS Questions.

Remove Ads or Log in

We need you!

Time of free websites that are supported by ads only is over, due to increasing use of ad blocking.

Our website would die probably within a year; as there is no point investing free time and later on own money in something that is doomed.

Our only hope to survive (without any ads) is to ask our visitors like yourself to support it by becoming a member.

We know it's only going to be a tiny % of visitors who will do that but we hope that will be enough to keep our website for free and within a year switch off all ads. (54% page ads are switched off already for visitors from low income countries and 15% video ads are switched off for everyone).

Become a proud member

Visitor Voting

We are thinking about re-introducing webinars (SQL, SSIS, SSRS, SSAS) but this time for members only (due to effort).
Is that something you might be interested in?
Visitor Voting: Yes +32  |  No -7
Add comment (no email required).

Add Comment
Name: mike
Date Posted: 6/2/2011 2:24:52 AM
Comment: could you add about one real time cleaning package with whole description? maintenance tasks in SSIS and uses? Star and snow flak schema? package dynamic?using XML?Custom variable? Most Important SP usage in SSIS?

Name: Emil
Date Posted: 6/2/2011 6:48:02 AM
Comment: Hi Mike These are very good questions. I will add them to the the questions and give best answers (best to my knowledge) p.s. I have moved your comment to ssis interview questions page (from ssrs). I'm off soon so it will probably take me several weeks before I add the new questions and answers

Name: Emil
Date Posted: 6/26/2011 9:08:53 AM
Comment: I added extra questions as requested. Not all but I'm happy to add new ones (or mentioned before) if there are more requests or I find time.

Name: Andy
Date Posted: 8/8/2011 10:11:12 AM
Comment: Nice article

Name: mani
Date Posted: 9/17/2011 7:40:11 AM
Comment: they r gud questions

Name: Naveen Raja
Date Posted: 9/27/2011 6:39:07 AM
Comment: Need some more questions which will cover all the concepts.

Name: Ramdeep Garg
Date Posted: 11/7/2011 5:10:00 AM
Comment: Usefull article.. please add more such type of questions. thanks..

Name: sasi
Date Posted: 11/7/2011 9:41:27 AM
Comment: its very good for us...!

Name: Sujatha
Date Posted: 11/22/2011 9:23:22 AM
Comment: Good Questions

Name: Ananth
Date Posted: 11/30/2011 7:03:58 AM
Comment: It's Useful

Name: Saurabh
Date Posted: 12/13/2011 8:45:49 AM
Comment: nice questions and comment as well, Could you please refer any book for the advance feature of SSIS and SSRS

Name: Emil
Date Posted: 12/13/2011 3:07:58 PM
Comment: Hi Saurabh, I usually use amazon to find good books. With kindle option you can review within 7 days and return which is what I often (I then buy hard copy if I like it). For advanced SSRS I recommend "Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports." and I also recommend Stephen Few "Show Me the Numbers: Designing Tables and Graphs to Enlighten" Regards Emil

Name: Nisha
Date Posted: 12/14/2011 6:08:17 AM
Comment: Actually I am new to SSIS. PLs let me know which book i can refer. but i worked on SSRS. in SSRS we can do manually deployemnt. is there is a way we can do automation of report deployment. means we can create some installer.

Name: Emil
Date Posted: 12/21/2011 8:38:33 AM
Comment: Hi Nisha, You can deploy SSRS using scripts. It is not straight forward especially with data sources but it is possible. For more information visit my colleague blog Regards Emil

Name: Nimesh
Date Posted: 12/22/2011 4:00:09 AM
Comment: Good question set. Many Thanks!

Name: Sai
Date Posted: 12/30/2011 7:20:19 PM
Comment: Hi, Good questions and comments. I worked on SSIS and SSRS for the last one year. But I worked on developing the packages(SSIS),creating reports(design, stored procedures)for a client. but I haven't deployed them, client has taken care. So I am not aware of the deployment stuff. Just I executed package on my local machine only. IN SSIS: we used to do extract data from Flat files/Excel Files and transform few changes for each fixed length field and formate things and load to SQL tables. i dont know what data warehouse here. I Used various components in SSIS (ADO/OLE connection mngrs, OLEDB source/dest, Flat File Sourc/dest/ connection mngrs, script task compont, Derived column, Lookup, sort, merge,Multi join, Row count, File system tasks, send mail) IN SSRS: I used to write stored procedure and calling that from report and using the dataset assigning to the fields in the table, and doing grouping based on the requirement. I want to be excel in this field. What should I do? Going through articles/ books? Do I need to join course as well? Please suggest. For MS BI, how can I learn SSAS which is the one of the major along with SSIS and SSRS?

Name: Emil
Date Posted: 1/3/2012 11:38:28 PM
Comment: Hi Sai. You seem to have good starting point to futher develope your SSIS and SSRS skills. I suggest to look on amamzon for new books. Learning from books that are written by experts in the field is usually the fastest way to learn. Regarding SSAS. The tool itself on basic level is very easy however SSAS involves data warehouse knowledge. I use Kimball Data warehouse methodologies and these take time to learn. Again I suggest to buy a book. You may find "The microsoft data warehouse toolkit" very useful. It is very nice book especially for those with some experience like yours. Best approach for SSAS is to create a cube from scratch. Create a simple fact table and two dimension, deploy and play with in SSMS or excel. I suggest to use microsoft adventureworks samples DW database for that. Best luck in your carrer. Emil

Name: venkat
Date Posted: 1/16/2012 12:05:37 PM
Comment: Hi, Its a nice artical be elaborate the issues and solutions as well then the viewers easily identifies

Name: Promila Mehta
Date Posted: 1/27/2012 3:52:39 PM
Comment: I am primarily an Oracle Database person. SSIS is new to me. Suggest how to start & go forward to take up a project in SSIS

Name: Dr Jacqui
Date Posted: 1/30/2012 2:33:56 PM
Comment: The reason I prefer stored procedures to SSIS packages is that you can export stored procedures to text files and search for column and table names using a simple text editor. This is extremely useful if you are making a change to an existing data warehouse and need to do an impact analysis on the change. All calculations using a particular column will show up wherever they occur in the import. If you have a big pile of SSIS packages you need to open up each one and look at each transformation to see if it will be affected. This is extremely time consuming and less certain than a text search of the code.

Name: Emil
Date Posted: 1/30/2012 10:17:41 PM
Comment: Dr Jacqui You can use SPs but you lose SSIS functionality like redirects etc which are for some (Kimball authors) considered to be one of the most important feature. UNLESS you use SPs just to perform simple extract which in this case is fine. I have experience mainly in building data warehouses using SSIS so in my case impact analysis is not required because each package is independent and because it is not SP there are much fewer dependancies inside one package.

Name: dinesh
Date Posted: 2/7/2012 4:11:39 AM
Comment: simply great ...

Name: darshan
Date Posted: 2/24/2012 8:53:55 PM

very helpful

Name: sravan
Date Posted: 3/13/2012 9:06:18 AM


Really this site is very helping to us and Pls add ssis  real time scenarios with answers, it can be useful to learners.

Name: Arun
Date Posted: 3/14/2012 6:50:56 PM

Very nice and very helpful. Keep up the good work!

Name: Rahul Trivedi
Date Posted: 3/15/2012 8:56:54 AM


You can add question about when to use transaction and checkpoint

and how can we use them together

Name: Sai
Date Posted: 3/22/2012 9:05:52 AM

Hi Emil,

Please add the performance tuning (SSIS) related question answer.

Especially during transformations like instead of what we can use like that.



Name: pavan kumar ganganapalli
Date Posted: 6/3/2012 12:27:11 PM

good queries wish to have more with examples and images so that it will be clear for new users

Name: harsha
Date Posted: 9/13/2012 5:55:42 PM

thank u .Give more questions from each task in ssis

Are you a member?

If yes than please log in

Learning Map is for members with Bronze, Silver and Gold Membership.
For more information visit membership options.

Close this window , log in or learn more about membership benefits