SQL Server BI DEV / DBA Q and A

This page contains SQL Server Business Intelligence Developer Interview Questions. Since it is just the beginning days of this section. I would be having many more in the coming days, suggest you to bookmark it, I’m sure you will have lots of Interview questions that are useful for your Interview preparation.

SSIS Interview Questions

1. What do you mean by Microsoft Business Intelligence and what components of SQL Server supports this?

  • Microsoft defines its BI solution as a platform to provide better and accurate information in a easily understandable format for quicker and better decision making. It consists of BI tools from SQL Server, SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), Microsoft SharePoint and its Office products.

2. What is SQL Server Integration Services (SSIS)?

  • SSIS is a powerful platform to build high performing, scalable packages to Extract, Transform and Load data, supporting various heterogeneous data sources as both Source and Destination of the ETL process.

Added on Nov 9th 2011

3. What is an SSIS Package?

  • SSIS package is a collection of variety of tasks to perform the Extract, Transform and Load data. Though the primary functionality of SSIS packages is for ETL, they can be used for other maintenance tasks such as database backups, Index rebuild, delete old backups etc.

4. How do you create SSIS Packages? OR What tools do you use for creating SSIS Packages?

  • You can create SSIS packages using Business Intelligence Development Studio in short “BIDS”. It can be installed using the same SQL Server installation media that is used to Install SQL Server. If all the features are selected when choosing the features at the the time of installation, then BIDS is installed along with Management Studio, Books Online and other tools..
Added on Jan 2nd 2012

5. What is Breakpoint and Checkpoint in SSIS Package? 

  • Breakpoints in SSIS packages enables us to review the values of the variables, or other components of an SSIS package. 
  • Checkpoints in SSIS packages enables us to rerun a SSIS package from the point of  failure, so that you do not have to rerun that portion of the package which was successfully run.

6. What is the various tabs available in a SSIS Project at Design time?

  • Control Flow.
  • Data Flow.
  • Event Handlers.
  • Package Explorer.

7. What are some of the events on which you can add an Event Handler in an SSIS Package?

  • OnError 
  • OnPostExecute
  • OnProgress
  • OnTaskFailed
  • OnWarning

8. I need to have more than 1 destination in a Data Flow task, how can that be achieved?

  • Using Multicast Data Flow Transformation, it is possible to direct the output to more than 1 destination.

9. How to you deploy an SSIS Package?

10. What are the different destinations SSIS packages can be saved / stored for deployment?

  • SSIS packages can be saved inside the SQL Server or File System destination.

11. Where inside a SQL Server are the SSIS stored?

  • SSIS packages are stored inside MSDB database.

12. If I need a Stored Procedure inside a SQL Database, to be run in an SSIS package what is the way?

  • You can use Execute SQL Control Flow Task to run a Stored Procedure.

SSAS and SSRS Interview Questions will be added soon.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty

5 thoughts on “SQL Server BI DEV / DBA Q and A”

  3. hi ,

    nice Questions and answers dude, Keepitup
    Regards
    Lakshmi Narayana

Leave a Reply