SSIS interview questions and answers

SSIS interview questions and answers for freshers and experienced candidates. These interview questions and answers on SSIS will help you strengthen your technical skills, prepare for the interviews and quickly revise the concepts. Many candidates appear for the interview for one role - many of the them give the right answers to the questions asked. The one who provides the best answer with a perfect presentation is the one who wins the interview race. The set of SSIS interview questions here ensures that you offer a perfect answer to the interview questions posed to you.
          

SSIS interview questions and answers


Part 1   Part 2   Part 3   Part 4
SSIS interview questions posted by Swati Parakh

Difference between control flow and data flow
If you want to send some data from Access database to SQL server database. What are different component of SSIS will you use?
Difference and similarity between merge and merge join transformation

Difference between control flow and data flow

Control flow deals with orderly processing of individual, isolated tasks, these tasks are linked through precedence constraints in random order. Also the output for task has finite outcome i.e., Success, Failure, or Completion. A subsequent task does not initiate unless its predecessor has completed. Data flow, on the other hand, streams the data in pipeline manner from its source to a destination and modifying it in between by applying transformations. Another distinction between them is the absence of a mechanism that would allow direct transfer of data between individual control flow tasks. On the other hand, data flow lacks nesting capabilities provided by containers.

Control Flow Data Flow
Process Oriented Data Oriented
Made up of Tasks and Container Source, Transformation and Destination
Connected through Precedence constraint Paths
Smallest unit Task Component
Outcome Finite- Success, Failure, Completion Not fixed
 

If you want to send some data from Access database to SQL server database. What are different component of SSIS will you use?

In the data flow, we will use one OLE DB source, data conversion transformation and one OLE DB destination or SQL server destination. OLE DB source is data source is useful for reading data from Oracle, SQL Server and Access databases. Data Conversion transformation would be needed to remove datatype abnormality since there is difference in datatype between the two databases (Access and SQL Server) mentioned. If our database server is stored on and package is run from same machine, we can use SQL Server destination otherwise we need to use OLE DB destination. The SQL Server destination is the destination that optimizes the SQL Server.  

Difference and similarity between merge and merge join transformation

Merge Transofrmations Merge Join Transformation
The data from 2 input paths are merged into one The data from 2 inputs are merged based on some common key.
Works as UNION ALL JOIN (LEFT, RIGHT OR FULL)
Supports 2 Datasets  1 Dataset
Columns

Metadata for all columns needs to be same

Key columns metadata needs to be same.

Pre-requisites

Data must be sorted.

Merged columns should have same datatype i.e. if merged column is EmployeeName with string of 25 character in Input 1, it can be of less than or equal to 25 characters for merging to happen.

Data must be sorted.

Merged columns should have same datatype i.e. if merged column is EmployeeName with string of 25 character in Input 1, it can be of less than or equal to 25 characters for merging to happen.

Limitations

Only 2 input paths can be merged.

Does not support error handling.

Does not support error handling.
Use

Merging of data from 2 data source

Can create complex datasets using nesting merge transformation,

When data from 2 tables having foreign key relationship needs to present based on common key.

Write your comment - Share Knowledge and Experience


More links
SQL Server 2008 interview questions

Explain inline variable assignment in sql server 2008 with an example.
What is Compound Operators in sql server 2008? Explain with an example
SQL Server 2008 introduces automatic auditing. Explain its benefits.............

Explain how to send email from database.

SQL Server has a feature for sending mail. Stored procedures can also be used for sending mail on demand. With SQL Server 2005, MAPI client is not needed for sending mails................. 

Interview questions
 
Home | Want a Job? Submit Key Skills | Employer login | My key skills | About us | Sitemap | Contact us