http://sqlage.blogspot.in/search/label/SSIS%20INTERVIEW%20QUESTIONS

Here are some SSIS interview questions those you can expect as ETL Developer. I will be working on below list to add more questions and different small scenarios. Good luck with yourSQL Server Integration Services interview:)

General

What is ETL
What are important components of ETL Tool
Which ETL Tool you have used as ETL Developer
Which version of SSIS you have worked
What is DTS and how it is different from SSIS
What are few different features you have in SSIS 2008 and those were not available in SSIS 2005
What features are new in SSIS 2012 those were not in SSIS 2008
What is difference between Control Flow and Data Flow in SSIS
What is parallel execution in SSIS and how many Data Flow Tasks a package can run in parallel
What is MaxConcurrentExecutables property on Package level
What is Engine Thread property of Data Flow
What is Precedence Constraints in SSIS and where you have used them and why
What is difference between Success and Completion value of Precedence Constraint
What is DelayValidation properties of Data Flow Task , Why we need to use this property
What is RetainSameConnection Property on Connection, Why we use that
If we create temp table in SSIS Package and want to use further in other Tasks, which properties we need to set to be able to use temp table.
What is data Viewer in SSIS, Is data viewer available in Control Flow or Data Flow
I am running my package for debugging, I do not want to load data into any destination, which transformation I can use for test purpose as destination without writing data to anywhere
What is difference between checkpoint and breakpoint in SSIS
Will my package run successfully by using SQL Server Agent if I have data viewers enabled and breakPoint
What are different ways to execute your SSIS Package, Can I run SSIS Package by using a Stored Procedure
What type of deployment available for SSIS Package, explain all
What is difference between Package deployment and Project Deployment
Which version of SSIS track versions of SSIS Package deployed to Server
To run your SSIS Package, the Integration Services Server and SQL Server should be installed on same server, Is this correct statement?
What are different ways to run your package on schedule
What are Event Handlers in SSIS , On which Events you can run the event handler part, Name few of the Events available
Let’s say you have configured Event handler to send an email on error for data flow inside For each Loop, If error occurred in data flow you will get multiple emails, why is that? How we can handle those series or email coming for one error
How do you debug your package
Explain important steps for performance tuning of your SSIS Package
If your package is scheduled to run every night at 10 PM. If package fails in production , where will you start to look for error
You have created a variable in SSIS, You used that variable in Data Flow task in Row Count transformation, if you want to display or see the value of variable after Data Flow execution how will you do that
You are looking at Control Flow Item and you cannot find Execute SQL task, how will you bring it back to Control Flow Items.
Your company is using some third party transformations and task, you need to add them to Control Flow Item and Data Flow Items , how will you do that
What are Attunity Drivers and why do we use them with SSIS
If you do not want to use Slowly Changing Transformation, Which T-SQL statement can help you to load SCD type table in one statement( insert, update, delete)
What is Annotation, Is it only available in Control Flow Pane or available in other Panes as well
Let’s say we have two Execute SQL Tasks in Sequence container, If any of them fail we want to roll back transaction. What setting we need to configure to achieve this.
What is TransactionOption property at Package level, Is this property only available on package level or It is also available on Container level,Task level
Can you create SSIS Package without using BIDS
You have excel file and you want to clear first cell of sheet1, which task you will use to do that
A third party software is available that you need to execute by using SSIS, which task can be used to run .EXE file
You need to load 500 million records in table; Table has cluster and non-cluster indexes already created. What will be your approach to load data
What are Asynchronous and Synchronous data flow components
What are Non-blocking, Semi-blocking and Fully-blocking components in SSIS?
What is Balanced Data Distributor( BDD)
What is Error Output? Can you redirect rows from Sources, Transformations and Destinations in SSIS?
If you need to check If File exists in Folder which Tasks you will use to find out that in SSIS
Let’s way you have created an Excel File by using Excel Destination. If you have to make the Header row bold, how you will do that in SSIS?
If you need to send HTML email, Can you use built in Send Mail Task? What other options you have?
If you are using SQL Server Log Provider, Can you make the SQL Server connection dynamic?
If you need to watch a directory for a specific file to be added which Task you will use?
What is For-each Item Enumerator in For-each loop and where would you need to use this?
If you need to move a file and rename as the same time how will you do that?
Which Package property will help you to encrypt packaged to restrict access to the contents of a package?

Sources

1. Name few of the Sources available in SSIS

2. If we need to read XML Source file which Source we will use in SSIS

3. What problems you have faced when you used Excel Source

4. Why we add IMEX=1 to extended properties of excel connection manager

5. If one of the excel column has alphanumeric data and other developer has changed the registry setting TypeGuessRow=0what does that mean to you

6. How will you handle a column such as comments that has more than 255 characters in excel at row number 1000.

7. What is fast parse property in Flat File Source? What are the requirements and limitation to use this property

8. Can I read csv file with Raw File Source, if not then what type of files I can read by using Raw File Source

9. Can we use Stored procedure in OLE DB Source, if yes how do you map the parameters

Transformations

1. What is a SSIS Transformation

2. If you need to get Username, Package Name, Package Start Time which transformation you will use

3. Which Transformation can perform operations such as Sum, count, Group by

4. If you need to convert Data Type from String to Integer which transformation you will use

5. If you need to Add new column which transformation you will use

6. Let’s say you have column Name, some of the record for Name column are null, if you want to replace Null with “Unknown” which transformation you will use

7. What is syntax for writing IF ELSE in Derived column Transformation

8. What is lookup transformation and why we use this transformation when we load Fact Table

9. What are three modes of Lookup transformation and what criteria should be used to choose

10. Can we insert record in Lookup Table by using Lookup Transformation

11. Should you use drop down for Table choose in Lookup and how good/bad it is for performance

12. What is multicast Transformation in SSIS

13. What is conditional Split Transformation in SSIS

14. What is major difference between Conditional Split and Multicast

15. What is OLE DB Command Transformation, Where you use it and what are disadvantages/Advantages of using OLE DB Command Transformation

16. What alternative methods you would like to use for OLE DB Command Transformation

17. If you need to get the Row Count for record those loaded from Source to Destination, which transformation you will use.

18. If you need to create sequence number with input records which transformation you will use

19. Which transformation can be used as Source, Destination Or transformation

20. What is Slowly Changing Dimension and which transformation can help you to load those tables

21. How will you load SCD1 type table by using SSIS

22. How will you load SCD2 type Table by using SSIS

23. What are the best practices when you load huge SCD type table

24. Which transformation require us to use SORT Transformation with it

25. What are the alternatives of SORT Transformation if our source is SQL Server and we need to use Merge Join for multiple source

26. What is IsSorted Property and why we use it

27. How will you remove duplicate records in SSIS, which transformation can help with this task

28. Let’s say if we have some reference data in Excel and we want to use that excel Data in Lookup Transformation , how we can achieve that without loading into staging or temp table

29. What is Cache Transformation and which transformation can use Cache Transformation loaded data

30. Why we need to use Cache Transformation

31. Which transformation can be used to change column data to Lower case, Upper case

32. What is difference between Copy Column Transformation and Derived Column Transformation

33. Let’s say my table contains images, I am reading data from table in Data Flow task, Which transformation can help me to save those images to files

34. Which transformation I can use in SSIS to import image files to Table

35. I have a source file that contains 1000 records, I want to insert 15% records in TableA and remaining in TableB which transformation I can use

36. What is difference between Row Sampling and Percent Sampling transformations

37. Which transformation can be used to extract nouns only, noun phrases only, or both nouns and noun phases from text input column

38. How different is Term Lookup from Lookup Transformation

39. To Pivot or Unpivot input data which transformations are available in SSIS

40. There is no Union Transformation in SSIS , How to perform UNION operation by using built-in Transformation

41. If we have source S1 and Source S2, we need to merge them and want to get sorted output , which transformation we can use

42. What is difference between Merge and Union All Transformation

43. In Merge Join Transformation, I see we can use Inner Join, Left Join and Full Outer Join, What If I have to use Cross Join which transformation I can use to perform that

44. What is FindString function in Derived column

45. Do we have MonthName and DayName functions available in SSIS so we can use them in expression, in Derived Column Transformation?

Destination

1. Raw File Destination , where and why we use it

2. What is difference between OLE DB Destination and SQL Server Destination

3. What is Recordset Destination and where we need that to use

4. Can we redirect rows from OLE DB Destination, if yes what are the important things need to remember while we configure that

5. What does “Keep Identity”,”Keep Nulls”,”Table lock” and “Check Constraints” Check box means on OLE DB Destination

6. What does Rows Per batch and Maximum insert commit Size mean to you in OLE DB Destination, how we use them and why we need to change values from default

7. If there is trigger on table and we are loading data in that table by using SSIS, Access Mode properties is set to “ Table or View-fast Load” , Will trigger fire? If not then what Mode(

Table or View,

Table or View-Fast Load,

Table Name or view name variable,

Table name or view name variable –fast load,

SQL Command) we can choose to load data as well trigger get fired

Variable and Expressions

1. What is variable in SSIS, What data types are available if we can declare a variable in SSIS

2. What is Scope of Variable, How we can change Scope of variable in SSIS 2008 and SSIS 2012

3. What are expressions in SSIS, Where and Why we need to use them

4. Can we write expression on Variable? If yes how and where you write them

5. Can we write expression on Connection Managers, If yes where did you use them and why

6. What are system variable in SSIS, Name few of them you have used

7. Can we write expression on Precedence Constraint, if yes how you do it

8. What is the maximum length of expressions in SSIS 2008 and SSIS 2012

9. What is new Expression Task in SSIS 2012

10. What are Parameters in SSIS 2012, how are they different from Variable

Logging

1. What is logging in SSIS , How many type of logging available in SSIS, Which one you have used

2. If you need to create a text type log file with timestamp, how you will do that

3. If you use SQL Server Logging, which table will be used to store log information

4. What are the few column names that sysssislog have to store log information

5. Do you log all the events or prefer to choose few of them?

6. Name few of Events you like to use in logging

7. What is custom Logging and how it is different from built in Logging

8. If you use timestamp in your text file logging , it creates multiple log files with each execution, how to avoid that and why single execution create more than one file

9. If we are using SQL Server Logging, how often the records got deleted from syssislog table or we have to create some purge process

Configuration

1. What is Configuration in SSIS

2. Why we even need configuration

3. What configuration types are available for us to use

4. What is difference between Direct and Indirect Configuration

5. If we use SQL Server Configuration, what table is created by configuration Wizard

6. How many columns configuration table do have, which one are important for us

7. For indirect configuration, do you create user variable or system variable to hold connection string?

8. What is parent child configuration and where we need to use that

9. Explain best practices for Integration Services Configuration

10. Let’s say we have SQL Server Connection Name ‘MyConnection’, when you will enable configuration which properties of Connection manager you will choose (Description,EvaluateAsExpression,Expression,IncludeInDebug,Name,NameSpace,RaiseChangedEvent,ReadOnly,Value)

Control Flow Task

1. What is Data Flow Task

2. If you need to run some SQL script( stored procedure, DML,DDL ) which task you will use in Control Flow

3. If you have create object type variable that you want to use in script task later, how you will load data into Object Type variable in Control flow, which task will help

4. If your database in Full Recovery mode cans you use Bulk Insert Task to load data? What are requirements to use Bulk Insert Task

5. If your company is using Ftp site to receive daily data files, which task you will use to download/upload/Delete files on FTP Site

6. What is difference between Script task and Script component

7. What script languages are available for you to do scripting in Script task and script component

8. What is difference between ReadOnlyVariables and ReadWriteVariable in Script task

9. Can you do debugging in Script task or Script component in SSIS 2012

10. You have create 5 packages, you want to call all of them in one package, which task you can use to call them

11. You have deployed your SSIS Package to Integration Services Server, Can you still call them in Parent Package

12. If you load set of big files, after each load you want to zip them and put into archive folder. Which task you need to use to Zip them

13. Which task you can use to delete, rename, move files and folders

14. Can you rename and move file by using one task, which task is that and how you will do that

15. If your data flow task fails in Control Flow, which task you will use to send email on Error

16. Before you create your SSIS Package and load data into destination, you want to analyze your data , which task will help you to achieve that

17. What is WSDL and in which task you have to use it

18. You have loaded email addresses in Object type variable, now you want to send email to each of the address you have in object type variable, which task you will use to loop through and send email one by one

19. There are 10 files in folder, we want to load them one by one, which tasks are required to perform this job

20. You have a lot of Tasks sitting in Control Flow task , you want to group them, which container can help you to achieve that

21. You got this assignment where you want to perform the same task 10 times, which loop you will use to do that

22. Name few of the task those are available to create Maintenance plan or those can perform DBA tasks such as Create indexes, take backup etc.

23. In Execute SQL Task what is Result Set( Single Row, Full Result Set,XML)

SQLSolutions and SSIS

Friday, October 4, 2013

SSIS Interview Questions