SSIS Interview Questions - Part 1

By:   |   Updated: 2011-11-23   |   Comments (21)   |   Related: 1 | 2 | 3 | 4 | More > Professional Development Interview Questions BI


Problem

When preparing for a SQL Server interview, it is helpful to understand what questions may be asked related to SSIS.  In this tip series,  I will try to cover as much as I can to help you prepare.

Solution

What is SQL Server Integration Services (SSIS)?

  • SQL Server Integration Services (SSIS) is component of SQL Server 2005 and later versions. SSIS is an enterprise scale ETL (Extraction, Transformation and Load) tool which allows you to develop data integration and workflow solutions. Apart from data integration, SSIS can be used to define workflows to automate updating multi-dimensional cubes and automating maintenance tasks for SQL Server databases.

How does SSIS differ from DTS?

  • SSIS is a successor to DTS (Data Transformation Services) and has been completely re-written from scratch to overcome the limitations of DTS which was available in SQL Server 2000 and earlier versions. A significant improvement is the segregation of the control/work flow from the data flow and the ability to use a buffer/memory oriented architecture for data flows and transformations which improve performance.

What is the Control Flow?

  • When you start working with SSIS, you first create a package which is nothing but a collection of tasks or package components.  The control flow allows you to order the workflow, so you can ensure tasks/components get executed in the appropriate order.

What is the Data Flow Engine?

  • The Data Flow Engine, also called the SSIS pipeline engine, is responsible for managing the flow of data from the source to the destination and performing transformations (lookups, data cleansing etc.).  Data flow uses memory oriented architecture, called buffers, during the data flow and transformations which allows it to execute extremely fast. This means the SSIS pipeline engine pulls data from the source, stores it in buffers (in-memory), does the requested transformations in the buffers and writes to the destination. The benefit is that it provides the fastest transformation as it happens in memory and we don't need to stage the data for transformations in most cases.

What is a Transformation?

  •  A transformation simply means bringing in the data in a desired format. For example you are pulling data from the source and want to ensure only distinct records are written to the destination, so duplicates are  removed.  Anther example is if you have master/reference data and want to pull only related data from the source and hence you need some sort of lookup. There are around 30 transformation tasks available and this can be extended further with custom built tasks if needed.

What is a Task?

  • A task is very much like a method of any programming language which represents or carries out an individual unit of work. There are broadly two categories of tasks in SSIS, Control Flow tasks and Database Maintenance tasks. All Control Flow tasks are operational in nature except Data Flow tasks. Although there are around 30 control flow tasks which you can use in your package you can also develop your own custom tasks with your choice of .NET programming language.

What is a Precedence Constraint and what types of Precedence Constraint are there?

  • SSIS allows you to place as many as tasks you want to be placed in control flow. You can connect all these tasks using connectors called Precedence Constraints. Precedence Constraints allow you to define the logical sequence of tasks in the order they should be executed. You can also specify a condition to be evaluated before the next task in the flow is executed.
  • These are the types of precedence constraints and the condition could be either a constraint, an expression or both 
    • Success (next task will be executed only when the last task completed successfully) or
    • Failure (next task will be executed only when the last task failed) or
    • Complete (next task will be executed no matter the last task was completed or failed).

What is a container and how many types of containers are there?

  • A container is a logical grouping of tasks which allows you to manage the scope of the tasks together.
  • These are the types of containers in SSIS:
    • Sequence Container - Used for grouping logically related tasks together
    • For Loop Container - Used when you want to have repeating flow in package
    • For Each Loop Container - Used for enumerating each object in a collection; for example a record set or a list of files.
  • Apart from the above mentioned containers, there is one more container called the Task Host Container which is not visible from the IDE, but every task is contained in it (the default container for all the tasks).

What are variables and what is variable scope?

  • A variable is used to store values. There are basically two types of variables, System Variable (like ErrorCode, ErrorDescription, PackageName etc) whose values you can use but cannot change and User Variable which you create, assign values and read as needed. A variable can hold a value of the data type you have chosen when you defined the variable.
  • Variables can have a different scope depending on where it was defined. For example you can have package level variables which are accessible to all the tasks in the package and there could also be container level variables which are accessible only to those tasks that are within the container.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-11-23

Comments For This Article




Saturday, September 9, 2017 - 1:01:59 AM - kiran Back To Top (66081)

Thanks for sharing very useful interview questions on sql server. I would like to add one more interview question here, which was asked in an interview, 

Q) What is OLE DB Command Transform and What is the purpose of it?


Ans) OLE DB Command Transform is a component designed to execute a SQL Statement for each row in an input stream. This task is analogous to an ADO Command Object being created, prepared, and executed for each row of a result set. The input stream provides that data for parameters that can be set into the SQL Statement that is either an In – line statement or a stored procedure call.

The input stream provides that data for parameters that can be set into the SQL Statement that is either an Inline statement or a stored procedure call. Want to learn SQL Server? Get register for free demo here - https://mindmajix.com/sql-server-training

 

 

Tuesday, May 23, 2017 - 9:02:53 AM - charan Back To Top (55977)

 

 what command i can use other than commit

Wednesday, March 8, 2017 - 1:30:43 AM - veera Back To Top (47552)

 Hi Sir,

 

Can you please send me checkpoints details questons and answers ,

 

what are the questions are asking in ssis for MSBI developer and sql server tipes means how to lean SQL server and T-Sql Server Questions and Answers pls

 

thanks,

Veera

 

Wednesday, September 2, 2015 - 5:55:11 AM - neeraja Back To Top (38585)

Any limitation in number of variables we use in single ssis package?

Can we create 100s of variables?

Thursday, December 4, 2014 - 2:05:00 AM - Mahesh Back To Top (35498)

The tutorial which is posting by you is very useful. I am working as Windows Administrator with MNC organization. Now I want move to SSIS, I have 3yrs of exp on windows. I have learned SSIS, I can rate my self 3/5. Can you tell me the better approach to prepare for the interviews ?

Thursday, September 25, 2014 - 1:04:07 PM - santoshkumar Back To Top (34719)

please send some msbi interview tips...............

Thursday, September 4, 2014 - 1:43:26 PM - Nagaraju Back To Top (34386)

Hi Arshad,

How are you?

Last 4 yrs i am working an MS SQL Server DBA support.

I want to move in MS BI and i am learning SSIS and SSRS.

Could you please guide me how can i move and interview technics.

 

Friday, July 25, 2014 - 5:15:44 AM - Akhil Back To Top (32877)

Hi Swapna

1.Yes, we can load data from one dataflow to next Dataflow. Scenario: Validating Data,which is coming from Multiple Sources to single destination.

2.when ever we are loading data from source to destination first we have to validate whether the destination having that processed record

Thursday, July 10, 2014 - 8:03:45 AM - swapna Back To Top (32629)

Hi, im facing some issues while answering interview like this
1.can we transfer data from one dataflow task to another dataflow task? if it possible how it will be work and what is the perpus ?
2. if work on daily base which field will be refreshed explain with scenarios..

plzz help me anyone who knows im not able to answer this kind of questions 

Wednesday, January 15, 2014 - 3:14:43 AM - Arshad Back To Top (28081)

Hi Syed,

You can use RowCount Transformation before loading data to destination in the data flow task. It gives you row count passing through the data flow pipeline engine.

Tuesday, January 14, 2014 - 1:15:02 PM - Syed Hayath Back To Top (28071)

Hi Arshad

I am working in small company , I need your help i have an issue i.e. I have many files in my local machine that files i want to dump to table between this process I need a count of records how many deployed to destination and I have to notify that information to my Team Lead.

Tuesday, June 18, 2013 - 9:03:28 AM - Arshad Back To Top (25464)

The SSIS Object model \ library contains methods to work with existing package as well

Tuesday, June 18, 2013 - 6:36:48 AM - Anshuman Back To Top (25462)

I want to read Dtsx file not make the DTSx file from from C# but i want to read the DTSx to read the column name of source and destination of that.

 

 

Wednesday, May 22, 2013 - 8:41:02 AM - Arshad Back To Top (24075)

Hi Anshuman,

 

Please have a look on this:

 

http://www.solidq.com/sqj/JournalDocuments/2011-April-Issue/Generating-SSIS-Packages-Programmatically-Part-I.pdf

 

Wednesday, May 22, 2013 - 3:19:40 AM - Anshuman Back To Top (24073)

Hi Arshad,

I am having one issue and i am working on that i need your help , I have lots of packages in SSIS. in thosse we have Ole Db source and Ole Db Destionation. in that we are transferrinf the data from source to destination.  so i have to  make the column mapping sheet for those column.

 

I have tried to read the SSIS package file(dtsx)  from Dot net , i can read the only package variable but i am not getting that how can read the other part like OLE db source and destionation and relationship between those. 

 

Please help me out.

 

Regards,

Anshuman Saini

Monday, March 11, 2013 - 2:48:27 PM - Jeremy Kadlec Back To Top (22711)

A.pradeep,

I would check out these resources:

http://www.mssqltips.com/sqlservertutorial/200/sql-server-integration-services-ssis/

http://www.mssqltips.com/sqlservertutorial/222/sql-server-reporting-services-ssrs/

http://www.mssqltips.com/sql_server_business_intelligence_tips.asp

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

Monday, March 11, 2013 - 11:32:36 AM - A.pradeep Back To Top (22706)

 

 

Hi,

I've a little idea about SSIS and SSRS.

Can anyone guide me how i can proceed on further with this ?

Thursday, March 7, 2013 - 5:41:17 AM - Atul Back To Top (22625)

Hi Arshad,

How are you?

Last 3 yrs i am working in application and database support.

I want to move in MS BI and i am learning SSIS and SSRS.

Could you please guide me how can i move and inerview technics.

 

 

Sunday, February 17, 2013 - 12:50:07 AM - Arshad Back To Top (22182)

Chaitanya

All Control Flow tasks are operational in nature except Data Flow tasks. - It does mean that data flow task is the only task which moves data from source to destination and does the transformation required in between. Rest other task just perform an operation, like SQL Execute task execute a T-SQL command etc.

Paul,

Thanks for pointing this out, more information about checkpoint in SSIS which restart the package from the point of failure can be found here:

http://www.mssqltips.com/sqlservertip/1408/integration-services-checkpoints-to-restart-package-from-failure/

Wednesday, February 13, 2013 - 12:10:36 PM - Paul Back To Top (22101)

Thanks for this collection of SSIS questions. It should perhaps also include a question on checkpoints.

Saturday, December 22, 2012 - 4:21:35 AM - chaitanya Back To Top (21090)

Hi Arshad, The tips are very nice, can you explain the meaning of "All tasks are operatiional in nature except the control flow task"? Thq














get free sql tips
agree to terms