Interview Questions on SQL with SSIS Developer Position

I have listed down some tricky and complicated questions asked during the written test ( for the position of Sr.SQL Developer with SSIS Developer in MNC Company)

1. List out all the department which are not assign to any location and any employee master(using joins and where clause)

Expected O/P

DeptIdName
4Marketing
5BMS

Answer:-

SELECT D.*
FROM Departments as D
LEFT JOIN Employee as E on E.DeptId =D.Id
LEFT JOIN Locations as L on L.DeptId =D.Id
WHERE (E.DeptId  is null Or L.DeptId is null)

2. Department wise Employee list

Expected O/P

Answer:

SELECT D.Id,DeptName,
 Stuff((SELECT ','+ Name
FROM Employee AS E
WHERE D.Id=E.DeptId
FOR XML PATH('')),1,1,' ') AS Employees
FROM Departments AS D

3. Create Procedure “GETEMPLOYEEBYDEPID” with @Deptid input parameter and return following o/p while pass the Department id.

Expected O/P

1. When we pass @Deptid=0, it will return all employees

2. When we pass @Deptid=3, it will return all employees which are assign to depatid=3

Answer:-

CREATE PROCEDURE GETEMPLOYEEBYDEPID  
  
@DEPTID TINYINT=0  
  
AS  
  
BEGIN  
  
SELECT E.Id,E.Name  
  
FROM Employee AS E  
  
WHERE (@DEPTID=0 OR E.DeptId=@DEPTID)  
  
END  

4. How to reverse any string in SQL? For Example: When we pass “INDIA” in a variable, it will return “AIDNI”

DECLARE @str VARCHAR(15)= 'INDIA'    
select REVERSE (@str)  

5. How many ways to deploy SSIS package?

  • SQL Server
  • File System
  • SSIS Package Store
  • DTUTIL Command line tool

6. How to avoid Early Arriving Facts and late arriving dimensions in SQL Server Integration Services SSIS?

Ref Link:

https://www.mssqltips.com/sqlservertip/1446/handle-early-arriving-facts-in-sql-server-integration-services-ssis/

7. Provide list of synchronous and asynchronous components in ssis.

Answer:

Synchronous

Audit, Cache Transform, Character Map, Conditional Split, Copy Column, Data Conversion, Derived Column, Lookup, Multicast, Percent Sampling, Row Count, DQS Cleansing, Export Column, Import Column etc.

Asynchronous

Data Mining Query, Merge, Merge Join, Pivot, Term Lookup, Unpivot, Union All, Aggregate, Fuzzy Grouping, Fuzzy Lookup, Row Sampling, Sort etc.

8. Types of Cache Mode in Lookup Component in SSIS

a. Full Cache
b. Partial Cache
c. No Cache

9. What is use of SSIS Logging?

Ref Link: https://docs.microsoft.com/en-us/sql/integration-services/performance/integration-services-ssis-logging?view=sql-server-2017

10. How to debug SSIS package in production environment?

Ref Link: https://stackoverflow.com/questions/15004109/can-you-monitor-the-execution-of-an-ssis-package-in-bids-as-it-runs-on-the-ser

About the author

Nirav Gandhi

Hi, My name is Nirav Gandhi, this blog is dedicated to providing unlimited database solutions and helping people to learn about Database Technology.

View all posts

Leave a Reply

Your e-mail address will not be published. Required fields are marked *