Friday, November 14, 2014

SSIS INTERVIEW QUESTIONS

General

  1. What is ETL? (Answer)
  2. What are the important components of an ETL Tool ?
  3. What is Business Intelligence ( BI) ?
  4. Which ETL Tool have you used as a Developer ?
  5. Which version of SSIS have you worked with?
  6. What is DTS, and how is it different from SSIS?
  7. Name some of the features available in SSIS 2008 that were not available in SSIS 2005?
  8. What additional features are available in SSIS 2012 that were not in SSIS 2008? (Answer )
  9. What is the difference between Control  Flow and Data Flow in SSIS? (Answer)
  10. What is parallel execution in SSIS, and how many Data Flow Tasks can a package run in parallel?(Answer)
  11. What is the MaxConcurrentExecutables property on a Package level? (Answer)
  12. What is the Engine Thread property of Data Flow Task?
  13. What are the Precedence Constraints in SSIS, and where and why have you used them?  (Answer)
  14. What is the difference between the Success and the Completion value of Precedence Constraint? (Answer)
  15. What is the DelayValidation property of Data Flow Task? Why does one use this property? ( Answer )
  16. What is RetainSameConnection Property on Connection? Why is it used? (Answer
  17. If we create a temp table in SSIS Package and want to use it in other tasks, which properties do we need to use? (Answer)
  18. What is data Viewer in SSIS? Is data viewer available in Control Flow or Data Flow? (Answer)
  19. I am running my package for debugging and I do not want to load data into any destination. Which transformation can I use to ensure that the data goes nowhere else?  (Answer)
  20. What is the difference between Checkpoint and Breakpoint in SSIS? (Answer-BreakPoint)
  21. Will my package run successfully by using SQL Server Agent if I have data viewers and Breakpoint enabled?
  22. What are different ways to execute your SSIS Package? Can I run a SSIS Package by using a Stored Procedure?
  23. What types of deployment are available for a SSIS Package? Explain all.
  24. What is the difference between Package Deployment and Project Deployment?
  25. Which version of SSIS can track versions of a SSIS Package deployed to the Server? (Answer)
  26. To run your SSIS Package, the Integration Services Server and SQL Server should be installed on the same server. Is this a correct statement?
  27. What are the different ways to run your SSIS package on a schedule?
  28. What are Event Handlers in SSIS? On which events can you run the event handler part? Name a few of the Events available?
  29. Let’s say you have configured Event Handler to send an email to report an error for Data Flow Task inside For Each Loop. If error occurred in a data flow task, you will get multiple emails. Why is that? How we can prevent those series of emails coming for one error? ( Answer )
  30. How do you debug your SSIS package?
  31. Explain the important steps for performance tuning of your SSIS Package.
  32. If your package is scheduled to run every night at 10 P.M., and the package fails in production, where will you begin your search for the error details? (Answer )
  33. You have created a variable in SSIS, and you have used that variable in Data Flow Task in Row Count Transformation. If you want to display or see that value of variable after Data Flow execution, how will you do that? ( Answer )
  34. You are looking at Control Flow Item, and you cannot find Execute SQL task.  How will you bring it back to Control Flow Items? (Answer)
  35. Your company is using some third party transformations and tasks, and you need to add them to Control Flow Item and Data Flow Items.  How will you do that? (Answer)
  36. What are Attunity Drivers and why do we use them with SSIS?
  37. If you do not want to use Slowly Changing Transformation, Which T-SQL statement can help you to load  an SCD type table in one statement? ( Answer )
  38. What is Annotation?  Is it only available in Control Flow Pane or available in other Panes as well? (Answer)
  39. Let’s say we have two Execute SQL Tasks in a Sequence container.  If any of them fail, we want to roll back the transaction. How can we achieve this? (Answer)
  40. What is a TransactionOption Property at the Package level?  Is this property only available at the package level, or it is also available at the Container Level or Task level?
  41. Can you create  an SSIS Package without using BIDS? (Answer)
  42. You have an excel file and you want to clear the first cell of sheet 1, which task will you?
  43. A third-party software is available that you need to execute by using SSIS.  Which task can be used to run EXE file?  (Answer)
  44. You need to load 500 million records in a table; this table has cluster and non-cluster indexes already created. What will be your approach to load the data?
  45. What are Asynchronous and Synchronous data flow components?
  46. What are Non-blocking, Semi-blocking and Fully-blocking components in SSIS?
  47. What is Balanced Data Distributor( BDD)?
  48. What is Error Output? Can you redirect rows from Sources, Transformations and Destinations in SSIS? (Answer)
  49. If you need to check that a file exists in a folder, which task(s) will you use? (Answer)
  50. Let’s say you have created an Excel File by using Excel Destination. If you have to make the Header row bold, how will you do that in SSIS? (Answer)
  51. If you need to send an HTML email, can you use the built-in Send Mail Task? What other options do you have? (Answer)
  52. If you are using SQL Server Log Provider, can you make the SQL Server connection dynamic?
  53. If you need to watch a directory for a specific file to be added, which Task will you use?
  54. What is For-each Item Enumerator in For-each Loop Container?  Why would you use it?
  55. If you need to move a file and rename it at the same time, how will you do that? (Answer)
  56. Which Package property will help you to encrypt package to restrict access to its contents?
  57. We have received Excel Source File with multiple sheets but with the same meta data.  How will you design your package to load all of the sheets to same table?  (Answer)
  58. You have redirected records due to Truncation or data conversion errors from some transformation.  How will you find which column created the error?  How do you detect the name of the column?  (Answer)
  59. You have created different types of SSIS Packages.  How do you determine all the variable names, connection managers, logging information or expression used in those Packages?  (Answer)
  60. You have received a big Excel or flat file, but you only want to load the first 100 records in a table.  How will you  do that?  (Answer)
  61. You have received an Excel Source file, but the first 10 rows have only company information; actual data starts from Row 11.  How will you skip the first 10 rows and start reading from row 11?  (Answer)
  62. If you need to read only one cell value from an Excel file in SSIS, which task will you use? (Answer)
  63. You are extracting data from a view.  The definition of View can change anytime. We want to load this data to an Excel file by using this view.  How do you create a dynamic package so that you don't have to re-do the mapping?   (Answer)
  64. You are running different SSIS Packages on your server. How do you determine how much time each package took for execution?   (Answer)
  65. Is it possible to run the SSIS Package from Excel by using Excel Macros? (Answer)
  66. If you need to purge old files, which tasks will you use in your SSIS Package ? (Answer)
  67. After developing your SSIS Package, you want to write a technical document.  What are important contents would you like to cover in that document?  (Answer)
  68. There are so many files in our folders, we want to save the file name, file created date and size of each file in an Audit table.  Which tasks will you use to do that?  (Answer)
  69. We have .sql files sitting in a folder, and we want to execute all of them.  How can you run them by using SSIS Package? (Answer) 
  70. We have a table which contains different types of files that we want to export to folder.  Which transformation do we need to use? (Answer)
  71. There are 100 files in a directory. All have the same structure. You need to load the most recent file to table. How will you do that? (Answer)
  72. Is backwards compatibility possible for an SSIS Package? In other words, if  you have created your SSIS Package in SSIS 2008, can you downgrade to SSIS 2005? (Answer)
  73. What is the difference between Package Level Connection Manager and Project Level Connection Manager?
  74. How would you get the oldest file from a Folder? (Answer)
  75. How would you find which SSIS Packages are used by SQL Server Agent Jobs? (Answer)
  76. How would you find out if a Stored Procedure is used in SSIS Package? Answer)
  77. How would you find and replace object name in SSIS Package or SSIS Packages in Solution? Answer)
  78. How would you create Load Summary Email logic in SSIS Package? (Answer )
  79. You have different folders sitting in Parent Folder. You want to delete old folder which are older than 7 days. How would you do that in SSIS Package? (Answer)
  80. How would you make your SSIS Package dynamic? 
  81. Sometime when you execute your SSIS Package, You get an error " File is used by another process". Why did you get this error? and How can you avoid this error?
  82. How would you implement data validation in your SSIS Package?
  83. What are the best practices to test SSIS Package? 
  84. We can load data from one database tables to another database tables by using TSQL, Why do we use SSIS instead ?
  85. What approach do you follow to troubleshoot your SSIS Package?
  86. How would you change the value of variable in SSIS Package during debugging to test different scenarios? ( Answer )
  87. Is it possible to save Stored Procedure Output Parameter value to SSIS variable ? (Answer )
  88. What is the purpose of naming convention in SSIS ? (Answer)
  89. Which Control Flow Task you will use if you need to convert excel file to csv file? (Answer)
  90. You need to delete Top N Rows from Flat File, How would you do that? ((Answer)
  91. You have different SSIS Packages in one of the folder, How would you find the version of each SSIS Package? (Answer)
  92. Your SSIS Package consist of multiple Data Flow Tasks. You need to execute only first 2 Data Flow Task, How would you do that? (Answer)
  93. Explain an example where you had to build your Query dynamically in SSIS Package and how did you do that? (Answer)
  94. If you have to perform Case Statement on one of the column data, Which Transformation would you use and how will the expressions look like? (Answer)
  95. If you need to copy all the tables from a SQL server Database to flat files, how would you do that?

Sources

1.       Name a few of the Sources available in SSIS.
2.       If we need to read an XML Source file, which Source will we use in SSIS?
3.       What problems have you faced when you used Excel Source?
4.       Why do we add IMEX=1 to extended properties of Excel Connection Manager?
5.       If one of the Excel columns has alphanumeric data and another developer has changed the registry setting to TypeGuessRow=0, what 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 limitations to use this property?
8.       Can I read a csv file with Raw File Source?  If not, what type of files can I read by using Raw File Source?
9.       Can we use Stored Procedure in OLE DB Source?  If yes, how do you map the parameters?
10. If you need to connect to TeraData, Which Data Source will you use?
11. How would you load fixed width flat file to SQL server table? (Answer)


Transformations

1.       What is a SSIS Transformation?
2.       If you need to get the Username, Package Name and Package Start Time, which transformation will you use?
3.       Which Transformation can perform operations such as Sum, Count and Group By? (Answer)
4.       If you need to convert Data Type from String to Integer, which transformation will you use?
5.       If you need to add a new column(s), which transformation will you use?
6.       You are reading data from a source by using a SSIS Package. If some records have Null values and you want to replace Null values to Unknow before inserting the data into a table, which Transformation will you use?
7.       What is syntax for writing IF ELSE in Derived column Transformation? (Answer)
8.       What is Lookup Transformation, and why do 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 the correct mode?
10.   Can we insert record in Lookup Table by using Lookup Transformation?
11.   Should you use a drop down to choose our reference table in Lookup?  Is this a good practice or not?
12.   What is multicast Transformation in SSIS?  (Answer)
13.   What is conditional Split Transformation in SSIS?  (Answer)
14.   What is the major difference between Conditional Split and Multicast Transformation? ( Answer )
15.   What is OLE DB Command Transformation?  Where would you use it and what are the disadvantages/advantages of using the OLE DB Command Transformation?  (Answer)
16.   What alternative methods would you like to use for OLE DB Command Transformation?
17.   If you need to get the Count for the number of records loaded from Source to Destination, which transformation will you use?  (Answer)
18.   If you need to create a sequence number for input records, which transformation will you use?  (Answer)
19.   Which transformation can be used as Source, Destination or Transformation?
20.   What is Slowly Changing Dimension, and which transformation can you use to load SCD tables?
21.   How will you load an SCD1 type table by using SSIS? (Answer)
22.   How will you load an SCD2 type Table by using SSIS?
23.   What are the best practices when you load huge SCD type tables?  (Answer)
24.   Which Transformation requires 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 sources?
26.   What is IsSorted Property, and why do we use it?
27.   How will you remove duplicate records in SSIS?  Which transformation can help with this task? (Answer)
28.   Let’s say we have some reference data in Excel, and we want to use that Excel data in Lookup Transformation.  How can we achieve that without loading that data into a staging table or temp table?
29.   What is Cache Transformation, and which transformation can use Cache Transformation-loaded data?  (Answer- Cache Transformation)
30.   Why do we need to use Cache Transformation? (Answer)
31.   Which transformation can be used to change column data to Lower Case or Upper Case? (Answer)
32.   What is the difference between Copy Column Transformation and Derived Column Transformation?
33.   Let’s say my table contains images, and I am reading data from a table in Data Flow task. Which transformation can help me to save those images to files?
34.   Which transformation can I use in SSIS to import image files to a Table?  (Answer)
35.   I have a source file that contains 1000 records, and I want to insert 15% of those records in TableA and the remaining records in TableB.  Which transformation should I use?
36.   What is the 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 a text-input column?
38.   How is Term Lookup different from Lookup Transformation?
39.   To Pivot or Unpivot input data, which transformations are available in SSIS?  (Pivot Answer) - Unpivot Answer
40.   There is no Union Transformation in SSIS.  How do you perform UNION operation using built-in Transformation? (Answer)
41.   If we have source S1 and Source S2, and we need to merge them so that we get sorted output, which transformation would we use?
42.   What is the difference between Merge and Union All Transformation?
43.   In Merge Join Transformation, we can use Inner Join, Left Join and Full Outer Join.  If I have to use Cross Join, which transformation would I use?
44.   What is FindString function in Derived Column Transformation?
45.   Do we have MonthName and DayName functions available in SSIS which can be used in expressions or in Derived Column Transformation? (Answer)
46. How would you split single column data into multiple columns in SSIS Package? (Answer)

Destinations

1.       What is Raw File Destination, where and why do we use it?
2.       What is the difference between OLE DB Destination and SQL Server Destination?
3.       What is Recordset Destination and where do we need use?
4.       Can we redirect rows from OLE DB Destination? If yes, what are the important things need to remember while we configure that? ( Answer )
5.       What does “Keep Identity”,”Keep Nulls”,”Table lock” and “Check Constraints” Check box means on OLE DB Destination? (Answer)
6.       What is "Rows Per batch" and "Maximum Insert Commit Size" mean to you in OLE DB Destination? How do we use them and why do we need to change values from default values?
7.       If there is trigger on a 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 which 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) can we  choose to load data and Trigger get fire as well?
8. How would you create Fixed Width column Text file by using SSIS ? (Answer)

Variables and Expressions

1.       What is variable in SSIS, what are data types available for variable?
2.       What is Scope of Variable? How can we change Scope of variable in SSIS 2008 and SSIS 2012? (Answer)
3.       What are expressions in SSIS? Where and Why do we need to use them?
4.       Can we write expression on Variable? If yes, how and where can you write them? (Answer)
5.       Can we write expression on Connection Managers? If yes, where would you need to do that?
6.       What are System Variables in SSIS? Name few of them those you have used.
7.       Can we write expression on Precedence Constraint? if yes, how would you do it?
8.       What is the maximum length of expressions in SSIS 2008 and SSIS 2012? (Answer )
9.       What is new Expression Task in SSIS 2012? (Answer )
10.   What are Parameters in SSIS 2012? how are they different from Variables?
11. Can you create two variables with same in name in SSIS Package?


Logging

1.       What is logging in SSIS? How many types of logging available in SSIS? Which one have you used? (Answer)
2.       If you need to create a text type log file with timestamp, how would you do that? (Answer)
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 is it 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 log file? (Answer)
9.       If we are using SQL Server Logging, how often the records get deleted from syssislog table? or do we have to create some purge process?
10- Junior ETL developer stops to your desk and asked your suggest or best practice which Logging type he/she should use? Explain which logging type in SSIS package you will suggest to him/her and why?
11- Why don't you suggest to use Windows Event Log in Production for SSIS Package?
12- Is it possible to have two types of logging e.g. text file logging and SQL Server Logging in same SSIS Package?
13-Explain different methods to make the log information created by SSIS Packages available to your developers?

Configuration

1.       What is Configuration in SQL Server Integration Services(SSIS)?
2.       Why do we even need Configuration?
3.       What are configuration types available for us to use?
4.       What is the difference between Direct and Indirect Configuration?
5.       If we use SQL Server Configuration, what is table name 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 do we need to use that?
9.       Explain the best practices for Integration Services Configuration.
10.   Let’s say we have SQL Server Connection Name ‘MyConnection’,  For configuration which properties of  Connection manager will you choose (Description,EvaluateAsExpression,Expression,IncludeInDebug,Name,NameSpace,RaiseChangedEvent,ReadOnly,Value) ?


Control Flow Task

1.       What is Data Flow Task? ( Answer )
2.       If you need to run some SQL script( Stored Procedure, DML,DDL ). Which task will you use in Control Flow? (Answer)
3.       If you have created object type variable that you want to use in script task later, how will you load data into Object Type variable in Control flow? Which task will help? (Answer)
4.       If your database in Full Recovery mode, can you use Bulk Insert Task to load data? What are the requirements to use Bulk Insert Task?
5.       If your company is using Ftp site to receive daily data files, which task will you use to download/upload/Delete files on FTP Site? (Answer)
6.       What is the 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 the 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 can you 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 them into archive folder. Which task would you need to use to Zip them? (Answer)
13.   Which task can you use to delete, rename, move files and folders?
14.   Can you rename and move file by using one task? which is that task and how will you do that? ( Answer )
15.   If your data flow task fails in Control Flow, which task will you use to send email on Error? (Answer)
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 will you 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 will you use to do that?
22.   Name few of the tasks 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) ?
24. Which task would you use to execute dot batch files in SSIS (Answer)
25. What type of Containers are available in SSIS Package? Explain two of them in detail.
26. You need to create a directory with Date in SSIS Package, Which Task would you use that? (Answer)

 Event Handlers

  1. What are Event Handlers?
  2. What type of Tasks we can use in Event Handler pane?
  3. What is the relationship between Executable and Event Handler in Event Handler Pane?
  4. If your SSIS Package failed on any task, You want to send an email, how would you use the event handler to do this?
  5. You have multiple Tasks in your SSIS Package such as Task1,Task2 and Task 3. If Task 1 or Task 2 fails, You want to send an email to users but if Task 3 fails then you want to truncate all the tables which are loaded by your SSIS Package, How would you do that?
  6. We can run any tasks in Control Flow Task on Failure by using Precedence constraint. What are the advantages of using Event Handler over Precedence Constraint in Control Flow Task?
  7. Name couple of Events which are available in Event Handler on which we can perform different actions.

3 comments:

  1. Excellent ! I am truly impressed that there is so much about this subject that has been revealed and you did it so nicely.
    SQL Server Training in Chennai

    ReplyDelete
  2. Really it was an awesome article...very interesting to read..You have provided an nice article....Thanks for sharing..
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete