SSIS Interview Questions and Answers

Are you going to shape the career as an SSIS professional? You have reached the right place then; here we are going to cover all of the possible questions that can be asked to an experienced or fresher candidate for SSIS. Many reputed companies are offering jobs to these professionals. So, you can still move ahead in your career by learning SSIS. We have included a complete list of questions that can be asked to the professionals by interviewers in 2020. They can help you in cracking the interview for the SSIS profile

1. How will you describe SSIS?

Ans:

SSIS or SQL Server Integration Services (SSIS) is a part of Microsoft SQL Server, which can be utilized to achieve an expansive scope of information change and information relocation tasks.

2. Explain the term data transformation.

Ans:

The procedure that permits you to remove explicit information out of its source is called data transformation. When that is done, it at that point oversees and moves it to the record based on your personal preference (usually, it is at the end of the file).   

3. Can SSIS handle any other tasks? If yes then list them.

Ans:

Some of the different assignments that SSIS handles are 

• information profiling
• successfully streamlining the group activities
• framework document

4. Differentiate between DTS and SSIS.

Ans:

SSIS can be referred to as an evolved version of DTS (Data Transformation Services) and has been re-composed without any preparation to beat the confinements of DTS which was accessible in SQL Server 2000 and prior renditions. A huge improvement is an isolation of the control/workstream from the information stream and the capacity to utilize a cushion/memory arranged engineering for information streams and changes which improves the overall execution.

5. Do you know what data flow in SSIS is?

Ans:

Presently, in case you’re simply beginning with SSIS, comparative inquiries may put on a show of being precarious – particularly when the past inquiry spun around information change. 

Similarly, as in most different cases, data flow in SSIS alludes to the procedure of data going starting with one point then onto the next. At the end of the day, information streams from a source to the objective area.

6. What do you understand by the term SSIS Expressions?

Ans:

On the off chance that you are inexperienced with SSIS Expressions, it undoubtedly implies that you are not a specialist and your SSIS experience is essential, so it is imperative to turn out to be comfortable with this element. SSIS Expressions can be utilized to channel data, to work with parameters, connect data, and make conditions. As such, it assists with working with dynamic associations, different conditions as well as working with loops.

7. Do you know what control flow integration is in SSIS?

Ans:

SSIS control stream permits you to program in your own way as to how the assignments will run by utilizing the consistent connectors between undertakings. You have an option of three fundamental sensible connectors that you can utilize: complete, success and failure. Besides, you can utilize the FX (articulation) to deal with increasingly complex conditions that are beyond your control.

8. Name the different components of data flow. (OR) Which are the three data flow components in SSIS?

Ans:

There are three components of data flow in SSIS

1. 1. Source
2. 2. Transformation
3. 3. Destination

9. What are the various types of SSIS packages that you can use?

Ans:

In the SSIS environment you get an option to choose among three types of containers, they are : sequence, for loops and each loop, respectively.

A for loop container is the thing that you can use to execute your undertakings to a specific number of times. For instance, you have to refresh records multiple times, you can put the errand that refreshes the records inside this for loop container and indicates 10 as the finish of the loops. by utilizing the for loop container, you don’t need to make either ten unique bundles do a similar assignment or need to run the entire bundle multiple times when you plan your activity. 

A sequence container is a basic method to bunch comparative errands. Think about a succession container as an association holder for increasingly complex SSIS bundles.

A for each loop container will be helpful when you don’t know early how often an errand ought to perform. for example, suppose that you need to erase all the records inside an organizer, however, you don’t have the foggiest idea what number of documents are there at a specific time, by utilizing for each circle, it can experience the assortment of records and erase them for you once the assortment is purged out, it realizes the time as to when to end.

10. What do you understand by the process of the data flow task in SSIS?

Ans:

The SSIS data flow task refers to the siphoning instrument that moves information from the origin to goal. In any case, on account of SSIS, you have significantly more command over what occurs all the way. Truth be told, you have a lot of out of the case change parts that you click on together to clean and control the information while it is in the information pipeline. 

Similarly, as the control stream handles the principle work process of the bundle, the Data Flow handles the change of information. Nearly anything that controls information falls into the Data Flow Category.

11. How will you explain the Data Profiling task?

Ans:

The data profiling task is the way toward examining the source information to all the more likely comprehend what condition the information is in, regarding neatness, examples, numbers or nulls, etc. Information profiling tasks generally are utilized toward the start of the advancement cycle to help the structure of the goal database blueprint. Note that this errand isn’t utilized when you build up the ordinary repeating ETL bundles.

12. How will you explain the multicast Transformation in SSIS?

Ans:

In SSIS Multicast transformation, as the name suggests, is the process that can send solitary data input to numerous yield ways without any problem. You might need to utilize this chance to send away numerous goals cut in various manners. The multicast change is like the Split Transformation because both send information to different yields. In any case, you cannot determine the conditions for which some portion of the information will be in which yield in the Multicast transformation

13. Merge and Union All- What differences do you need to know?

Ans:

In SSIS Merge transformation can blend information from two ways into a solitary yield. The Transform is helpful when you desire to break out your Data Flow into a way that manages certain blunders and afterward consolidate it again into the fundamental Data Flow downstream after the mistakes have been taken care of. It’s likewise helpful if you wish to blend information from two Data Sources. 

However, in SSIS the Union All Transformation works similarly as the Merge Transformation, yet it doesn’t require the information to be arranged. It takes the yields from numerous sources or changes and joins them into a solitary outcome set.

14. What do you understand by Merge Transformation in SSIS?

Ans:

In SSIS merge transformation is known to consolidate two paths into a solitary path. It is valuable when you need to break out information into a way that handles blunders after the mistakes are dealt with, the information is converged once more into downstream or you need to combine 2 information sources. 

15. What are the limitations of Merger transformation in SSIS?

Ans:

Merge transformation has a few limitations and they are as follows:

1. 1. First is that it needs the Data to be properly sorted in an order
2. 2. Secondly, the attributes of Data type, data length, and other Metadata should be similar to each other before they can be merged.

16. Define what OLE DB Command Transform is.

Ans:

The OLE DB Command Transform is a segment intended to execute a SQL Statement for each line in an information stream. This errand is practically equivalent to an ADO Command Object being made, arranged, and executed for each line of an outcome set. The info stream gives that information to parameters that can be set into the SQL Statement that is either an Inline proclamation or a put away methodology call.

17. What do you understand about the package task in SSIS?

Ans:

The package task is also known as the Execute Package task in SSIS refers to the one of the most essential elements of SSIS as this component is responsible for giving the developer the functionality to call the other SSIS packages right from the origin of their workflow. We can use this at any given time.

18. What do you understand by the Precedence Constraints?

Ans:

In SSIS a task undertaking will possibly execute if the condition that is set by the priority imperative goes before the errand is met. By utilizing these limitations, we will pick diverse execution ways relying upon the achievement or disappointment of different tasks.

Success – Workflow will continue when the first compartment executes effectively. It is shown in control flow by a strong green line.

Failure – Workflow will continue when the former compartment’s execution brings about disappointment. It is demonstrated in control flow by a strong red line.

Completion – Workflow will continue when the first compartment’s execution finishes, paying little mind to progress or disappointment. It is shown in charge by a strong blue line.

Expression/Constraint with logical AND – work process will continue when determined articulation and imperatives assess to valid. It is demonstrated in charge stream by a strong shading line alongside a little ‘fx’ symbol close to it.

19. What do you understand by a Checkpoint?

Ans:

A checkpoint is a property in SSIS which empowers the venture to restart from the purpose of disappointment. At the point when we set the property to a genuine bundle, make the checkpoint document that stores the data about bundle execution and use it to restart the bundle from the purpose of disappointment. On the off chance that the bundle runs effectively, the checkpoint document is erased, and afterward re-made whenever the package runs.

20. What is the task?

Ans:

In SSIS a task is something that you would issue to the database, to get certain ideal outcomes. Altogether, there are two kinds of tasks in SSIS –

1. 1. control stream assignments
2. 2. database support ones

21. What is the process to fail a package manually in Integration Services?

Ans:

I am running an Execute SQL Task articulation in my SSIS package. The Execute SQL Task is running SQL and watching that the tables have over 1000 lines. If they have fewer than 1000 lines, the package will fail. 

22. What do you understand by ‘deployment’?

Ans:

The procedure of deployment is where you would move the records from the “sending stage” to the “execution stage”. You could state that it resembles taking records from their areas and delivering them off for execution.

23. Explain the data flow engine.

Ans:

It’s critical to know however much as could be expected about the information in the SSIS inquiries questions. As the name likely suggests, a data flow engine is liable for the progression of information through should SSIS. The engine uses what are known as supports to make a consistent and overly quick progression of information from it’s a source to the goal.

24. What is a Container? How many SSIS containers can you name?

Ans:

SSIS Container is a legitimate gathering of tasks that permits dealing with the extent of a task together. The ideal answer is naming every one of them.

25. What is a solution Explorer in SSIS?

Ans:

In SSIS solution explorer is the screen where you can view and access all the information sources, information sources perspectives, ventures, and different random documents.

26. What do you understand by the connection managers in SSIS?

Ans:

While gathering information from various sources and composing it to a goal, association chiefs are useful. Association supervisor encourages the association with the framework that incorporates data that resembles information supplier data, server name, verification system, database name, and so forth.

27. Explain an SSIS breakpoint.

Ans:

An SSIS breakpoint empowers you to stop the proper implementation of the bundle in the business insight advancement studio during the investigating or the vital advancement of an SSIS package.

28. Explain the event logging in SSIS.

Ans:

In SSIS, the process of event logging permits the developer to choose a particular occasion of an assignment or a bundle that requires logging. It is gainful at times if you are investigating your bundle to comprehend the presentation package.

29. What is the data flow buffer?

Ans:

The tasks in SSIS operate by utilizing buffers;  a data flow buffer is like an in-memory sort of a virtual table that is utilized to hold data where it is supposed to be.

30. What is ETL?

Ans:

In the computing world, extract, transform, the load is the general methodology of duplicating information from at least one source into a goal framework that speaks to the information uniquely in contrast to the source or an unexpected setting in comparison to the source.

31. When can you escape saving the data for a data checkpoint?

Ans:

Usually, it is not recommended to not save the data, however, in the normal course, the Checkpoint data does not get saved for the For Loop containers and the For Each Loop container. 

32. Explain about the phase of a conditional split transaction in SSIS.

Ans:

The phase of conditional split transformation is just as similar to the IF condition, that is used to check for all the listed out conditions that are based on the conditional evaluation.

33. What are the various kinds of Data viewers that are available in SSIS?

Ans:

The various kinds of data viewers that are available in SSIS include

• Grid
• Column Char
• Histogram
• Scatter Plot

34. What role does the Event Handlers tab perform in SSIS?

Ans:

Right on the event handlers tab, work processes can be designed to react to bundle occasions.

For example, you can arrange the work process when any task stops, comes up short or starts.

35. What is the process to notify the staff members about a package failure?

Ans:

It is possible to do so in two ways- 

1. 1. inside the package, you could include a Send Mail Task in the occasion handlers
2. 2. you can even set the notice in the SQL Agent when the package runs

36. What are the different types of files or connections that can be supported in SSIS?

Ans:

Different types of connection that support SSIS are:

• ODBC
• Flat File
• XML
• Excel
• OLEDB
• .net SQLClient

37. Can you schedule SSIS packages for a specific time or a specific interval; of a day?

Ans:

You can design a SQL Server Agent Job with a work step kind of SQL Server Integration Services Package, the activity summons the dtexec order line utility inside to execute the bundle. You can run the activity (and thus the SSIS bundle) on request or you can make a calendar for a one time need or on a reoccurring premise. Allude to this tip to become familiar with it.

38. How can you Handle the Early Arriving Facts Or Late Arriving Dimensions?

Ans:

To be honest, Late Arriving Dimension is unavoidable, to deal with these we can make fake measurements with normal/business keys and keep the remainder of the properties as invalid or default. So when real measurement shows up, the fake measurement is refreshed with Type 1 change. This likewise alludes as Inferred Dimensions.

39. Can you use SSIS objects in a Visual Studio as well as extract data without SSIS?

Ans:

Yes, you can. You can have a Visual Studio venture in C# like a web application, a Windows work area application, a support application and conjure SSIS items to program errands without SSIS ventures. As such, you could make your control streams, information streams, associations utilizing just C# code or Visual Studio code. In the vast majority of the cases, it isn’t down to earth to do it, since it might take a longer time. In any case, it very well may be vital if the undertaking in the SSIS venture has some usefulness restriction that could be shrouded by the code in C#.

40. What do you mean by an Execution Tree?

Ans:

Execution trees show how the package utilizes buffers and strings. At run time, the information stream motor separates Data Flow task activities into execution trees. These execution trees indicate how supports and strings are assigned in the bundle. Each tree makes another cradle and may execute on an alternate string. At the point when another support is made, for example, when an incompletely blocking or blocking change is added to the pipeline, extra memory is required to deal with the information change and each new tree may likewise give you an extra worker string.

41. What is the process for an incremental load?

Ans:

The quickest approach to do gradual burden is by utilizing the Timestamp section in the source table and afterward putting away last ETL timestamp, In the ETL process pick all the lines having Timestamp more prominent than the put-away Timestamp to pick just new and refreshed records.

42. As a developer what do you think are the best practices for using SSIS?

Ans:

Here is a list of some best practices of SSIS,

• You ought to abstain from performing logged tasks 
• You should make an understood arrangement for asset use. 
• Optimize the information source, query change, and destination

43. What is the use of an XML Task in SSIS?

Ans:

An XML task in SSIS permits the user to split, merge, or reformat any kind of XML file.

44. On the off chance that you Want To Send Some Data From Access Database To SQL Server Database. What Are The Different Components Of SSIS That You Will Use?

Ans:

In the information stream, we will utilize one OLE DB source, information change, and one OLE DB goal or SQL server goal. OLE DB source is an information source that helps peruse information from Oracle, SQL Server, and Access databases. Information Conversion change would be expected to expel datatype anomaly since there is a contrast in datatype between the two databases (Access and SQL Server) referenced. On the off chance that our database server is put away and the bundle is run from the same machine, we can utilize the SQL Server goal else we have to utilize the OLE DB goal. The SQL Server goal is the goal that improves the SQL Server.

45. What do you mean by Data conversion Transformation?

Ans:

In the SSIS environment data conversion transformation is the best strategy to pass on the information starting with one sort then onto the next. In any case, you have to ensure that you have COMPATIBLE information in the column.

46. How can you stop a package from running forever?

Ans:

It depends. On the off chance that you are running the bundle in the SQL Agent, you can slaughter the procedure utilizing T-SQL. Be that as it may, if the bundle is running in the SSIS inventory, you can stop it utilizing Active Operations window or the stop activity stored technique.

47. What is the purpose of using the Ignore Failure option in SSIS?

Ans:

By using the Ignore Failure option, the error will be disregarded and the information line will be coordinated to proceed with the following change. Suppose you have some JUNK information (wrong kind of information or JUNK information) spilling out of source, at that point utilizing this choice in SSIS we can REDIRECT the garbage information records to another change as opposed to FAILING the bundle. This assists with moving just legitimate information to the goal and JUNK can be caught into a different file package. 

48. What is the function of the Package explorer tab in SSIS?

Ans:

This tab gives an explorer’s view on the package. You can perceive what’s going on in the bundle. The Package is a container at the highest point of the chain of importance.

49. What do you gather by the term Solution Explorer?

Ans:

Solution Explorer is a spot in SSIS Designer where all the tasks, Data Sources, Data Source Views, and different incidental documents can be seen and gotten to for alteration.

50. What is Multicast Transformation?

Ans:

The multicast transformation sends yield to numerous yield ways with no contingent as Conditional Split does. Takes ONE Input and makes the COPY of information and passes similar information through numerous yields. In basic, give one data command and take numerous yields from similar information.

51. How do you eliminate quotes from being uploaded from a flat file to SQL Server?

Ans:

This can be done using TEXT QUALIFIER property. In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.

52. What is the function of the Event handlers tab in SSIS?

Ans:

On the Event handlers tab, workflows can be configured to respond to package events.

For example, we can configure Workflow when ANY task Fails or Stops or Starts.

53. What is the use of Execute SL task in SSIS?

Ans:

Execute SL helps you to execute a SQL statement against a relational database.

54. Explain Copy column Transformation?

Ans:

This component simply copies a column to another new column. Just like ALIAS Column in T-Sql.

55. Explain Derived column Transformation?

Ans:

Derived column creates a new column or puts manipulation of several columns into a new column. You can directly copy existing or create a new column using more than one column also.

56. Explain Merge Transformation?

Ans:

Merge transformation merges two paths into a single path. It is useful when you want to break out data into a path that handles errors after the errors are handled, the data are merged back into downstream or you want to merge 2 data sources. It is similar to Union All transformation, but Merge has some restrictions:

1. 1. Data should be in sorted order
2. 2. Data type, data length, and another Metadata attribute must be similar before merged.

57. Where Are Ssis Packages Stored In The Sql Server?

Ans:

MSDB.sysdtspackages90 stores the actual content and subcategories,

• sysdtslog90
• sysdtspackages90
• sysdtspackages
• sysdtssteplog
• sysdate tasklog do the supporting roles

58. Explain Merge Join Transformation?

Ans:

Merge Join transformation will merge output from 2 inputs and do an INNER or OUTER join on the data. But if you the data come from 1 OLEDB data source, it is better you join through SQL query rather than using Merge Join transformation. Merge Join is intended to join 2 different data sources.

59. Mention some disadvantages of SSIS?

Ans:

Some disadvantages of SSIS are

1. 1. The vision and strategy are sometimes unclear.
2. 2. It can create problems in a non-Windows environment.
3. 3. It also does not yet provide support for alternative data integration methods.

60. Different types of debugging in SSIS?

Ans:

There are 3 types of debugging are

1. 1. Execute Package partially
2. 2. By using breakpoints
3. 3. By using Data Viewers

61. Explain Multicast Transformation?

Ans:

This transformation sends output to multiple output paths with no conditional as Conditional Split does. Takes ONE Input and makes the COPY of data and passes the same data through many outputs. In simple terms, give one input and take many outputs of the same data.

62. What is package configuration?

Ans:

The configuration provides user interaction to the package, so a user can control the package without editing the package from the solution explorer.

63. What are the types of configurations in SSIS?

Ans:

1. 1. XML file configuration
2. 2. SQL server DB configuration
3. 3. Parent package variable configuration
4. 4. Environment variable
5. 5. Registry entry.

64. What are the different types of command-line utilities in SSIS?

Ans:

• DTEXEC
• DTEXECUI
• DTUTIL

65. What is the difference between merge and Union All?

Ans:

Merge will combine only two sorted dataset values.

Union ALL will combine data from multiple data sets

66. What are the important components of SQL Server Integration Services (SSIS)?

Ans:

The main and important components are four things which are SSIS runtime engine, data flow pipeline engine, SSIS object model, SSIS windows service. The runtime engine provides management of the workflow of a package. The data flow pipeline engine provides the transformation of data from source to destination and also in-memory transformations.

67. How to schedule the package?

Ans:

By using SQL Server Agent.

68. Difference between Full cache, partial cache and no cache?

Ans:

FULL cache – gets the complete reference data set from the database and places in memory and compares it with source data.

Partial Cache – Here we can specify how much memory that the reference dataset has to occupy.

No Cache – Here data will not be cached. It is not recommended.

69. Difference between DTS and SSIS?

Ans:

Everything is different except both are a product of Microsoft.

70. How to achieve parallelism in SSIS?

Ans:

Using the MaxConcurrent Executable property of the package. Its default is -1 and it is calculated as a number of processors +2

71. Types of Loggings?

Ans:

1. 1. Flat file
2. 2. SQL Server table
3. 3. XML
4. 4. SL Profiler

72. What is CDC Splitter?

Ans:

After data has been read out of a table with CDC enabled, this transform sends data that should be deleted, inserted and updated down a different path.

73. What is OLEDB Command T\R?

Ans:

Execute an OLEDB command for each row in the data flow.

74. What is the Dimension table?

Ans:

It is a table which contains detailed data.

75. Different types of transformation in SSIS?

Ans:

There are two different types of transformations

• Synchronous transformations
• Asynchronous transformations

76. What is the use of CDC control tasks?

Ans:

Using CDC we can maintain and interact with the change capture feature from SQL Server.

77. Difference between Merge join and lookup?

Ans:

In Merge join, we can implement left outer join, right outer join and full outer. Whereas in lookup, we can perform inner join only.

78. What is the event handler?

Ans:

An event handler is like a trigger in our SL databases which will be executed automatically when an action takes place.

1. 1. Onerror
2. 2. Onwarning
3. 3. OnPreexecute
4. 4. OnPostExecute
5. 5. OnVariableValueChanged
6. 6. COnquer Cancelled

79. Explain How Can You Do An Incremental Load?

Ans:

The best and fastest way to do incremental load is by using the Timestamp column in the source table and storing the last ETL timestamp.

80. Different types of package storage mechanisms

Ans:

• File system
• MSDB

81. List out different types of source systems you have used in your current project

Ans:

1. 1. Flat file
2. 2. Excel
3. 3. Ole DB source
4. 4. ADO.NET

82. How Would You Do Error Handling?

Ans:

A SSIS package could mainly have two types of errors

1. 1. Procedure Error: Can be handled in Control flow through the precedence control and redirecting the execution flow.
2. 2. Data Error: Can be handled in DATA FLOW TASK buy redirecting the data flow using Error Output of a component.

83. How Does Ssis Differ From Dts?

Ans:

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.

84. What Is an Execution Tree?

Ans:

Execution trees demonstrate how packages use buffers and threads. At run time, the data flow engine breaks down Data Flow task operations into execution trees. These execution trees specify how buffers and threads are allocated in the package. Each tree creates a new buffer and may execute on a different thread. When a new buffer is created such as when a partially blocking or blocking transformation is added to the pipeline, additional memory is required to handle the data transformation and each new tree may also give you an additional worker thread.

85. How does sort transformation work in the ssis, how does it impact the performance?

Ans:

Sort transformation sorts the data by reading all the rows and sorting them completely. When performed on a large data set is slower. It is better to have the data sorted by the “order by” in the SQL query.

86. What is Multithreading?

Ans:

It is a process of processing the data-parallel to reduce the time of execution of the package and to process a large amount of data faster.

87. In which isolation level phantom read does not occur?

Ans:

In 2 levels,

1. 1. Snapshot
2. 2. Serializable

88. How to change the isolation level in SQL server?

Ans:

This can be achieved by using the SL statement

89. When does the activation of the rcsi take place?

Ans:

The activation of this has to be performed manually. It will not be created during the creation of the Database.

It can be done by using the query

90. What does the master database hold?

Ans:

It has information such as logins, linked servers, endpoints, system configuration settings. Information about the other databases on their instances and their physical file locations.

91. How to perform data validation in SSIS?

Ans:

This can be performed by using a script task, and writing code in c# or VB to validate the data and give the result.

92. Can the SSRS RDL in a report server be exported to EXCEL in from a SSIS package?

Ans:

Yes, it can be performed by using a Script task.

93. Define the limitation and advantage in using the SL command as an input source when input in the SL data source in a data flow task?

Ans:

Using a SL command to extract the data from the SQL server is efficient in reducing the load on the site and reduces the execution time. it helps us prevent the use of separate ssis tasks for merging and joining and union. Its limitation is that the typing space is limited to a certain number and the query cannot be bigger. In that instance, it can be defined as a stored procedure and can be called in the SL command.

94. How do we create an excel file and the sheet with defined column and data type in SSIS?

Ans:

It can be done by using the create table statement to execute the SL task component and connecting it to an empty excel connection.

95. Name a few blocking transformations?

Ans:

• Sorting
• Aggregation

96. Can the data be viewed as between data tasks?

Ans:

Yes data can be viewed by using “enable data viewer”

97. Name the ssis data types for the varchar and nvarchar data types of SQL server?

Ans:

1. 1. Varchar – > dt_str
2. 2. Nvarchar – > dt_wstr

98. What are the deployment models in SSIS?

Ans:

• Package deployment
• Project deployment models