Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SSIS Interview Questions - Part 1

MSSQLTips author Arshad Ali By:   |   Read Comments (14)   |   Related Tips: 1 | 2 | 3 | 4 | More > Interview Questions Developer
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


Last Update: 11/23/2011


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Saturday, December 22, 2012 - 4:21:35 AM - chaitanya Read The Tip

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


Wednesday, February 13, 2013 - 12:10:36 PM - Paul Read The Tip

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


Sunday, February 17, 2013 - 12:50:07 AM - Arshad Read The Tip

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/


Thursday, March 07, 2013 - 5:41:17 AM - Atul Read The Tip

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.

 

 


Monday, March 11, 2013 - 11:32:36 AM - A.pradeep Read The Tip

 

 

Hi,

I've a little idea about SSIS and SSRS.

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


Monday, March 11, 2013 - 2:48:27 PM - Jeremy Kadlec Read The Tip

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


Wednesday, May 22, 2013 - 3:19:40 AM - Anshuman Read The Tip

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


Wednesday, May 22, 2013 - 8:41:02 AM - Arshad Read The Tip

Hi Anshuman,

 

Please have a look on this:

 

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

 


Tuesday, June 18, 2013 - 6:36:48 AM - Anshuman Read The Tip

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.

 

 


Tuesday, June 18, 2013 - 9:03:28 AM - Arshad Read The Tip

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


Tuesday, January 14, 2014 - 1:15:02 PM - Syed Hayath Read The Tip

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.


Wednesday, January 15, 2014 - 3:14:43 AM - Arshad Read The Tip

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.


Thursday, July 10, 2014 - 8:03:45 AM - swapna Read The Tip

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 


Friday, July 25, 2014 - 5:15:44 AM - Akhil Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.