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!

By becoming a proud member we can:
  • Switch off annoying ads
  • Create more articles and videos
  • Develop more useful features supporting your learning
  • Provide FREE webinars (for members)
  • Feel our hard work since 2011 is appreciated
Become a proud member and get exclusive features.

If 2% of our 64,943 visitors (Last 30 days) become a proud member than we can quit our jobs and work full time on this website. We started membership in May 2014 and so far 0.07% of our visitors are members and we would like to say THANK YOU to those who become a proud member and support our efforts.

Thanks to members:
  • Education is for FREE and we create more FREE articles and videos
  • We provide new features (members) that can speed up learning.
  • 53% of our page views (82,000 each month) do not have page ads.
  • We switched video ads from 14% of our videos (50 out of 345).
We have invested 4 years of our time in this website and we estimate it will take about 5 years before we can quit our jobs and dedicate all our time to develop considerably our website which should help to truly achieve our vision of free education for everyone; all thanks to support of our members only. Become a proud member

Visitor Voting

Do you enjoy your job?
Visitor Voting: Yes +15  |  No -5
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 http://phil-austin.blogspot.com/2009/02/deploying-reporting-services.html 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
Comment:

very helpful




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

Hi,

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

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




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

Hi,

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

Hi Emil,

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

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

Thanks

Sai.




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

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

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
  Become a Proud Member