Top 45+ Practice SSIS Interview Questions [ANSWERED] in 2020
SSIS Interview Questions and Answers

Top 45+ Practice SSIS Interview Questions [ANSWERED]

Last updated on 20th Jun 2020, Blog, Interview Questions

About author

Prabhu (Sr Technical Project Manager )

Highly Expertise in Respective Industry Domain with 7+ Years of Experience Also, He is a Technical Blog Writer for Past 4 Years to Renders A Kind Of Informative Knowledge for JOB Seeker

(5.0) | 15212 Ratings 19006

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications.

1. What does SSIS Mean?

Ans:

SSIS or in other words, SQL Server Integration Services, is a significant part of Microsoft SQL Server, which can be utilized to achieve an extensive range of data transformation and migration jobs.

2. What are the significant parts of the SSIS package?

Ans:

The significant parts in the SSIS package are: Data flow,   Package Explorer, Event handler and Control flow.

3. What is Solution Explorer in SSIS?

Ans:

 Solution Explorer in SSIS stands for a screen where you can take a look and access all the data sources, projects, and other sorts of related files.

4. What is the meaning of data flow in SSIS?

Ans:

Data flow in SSIS stands for the flow of data from the matching sources to the anticipated destinations.

5. What is “task” in SSIS?

Ans:

A task in SSIS is nothing but the technique of any programming language that stands for an individual component of work.

6. What is an SSIS package?

Ans:

An SSIS package is a prearranged set of connections like data flow elements, event handlers, variables, control events, and configurations. You collect them by the tools provided by SSIS.

7. What are different types of connections that support SSIS?

Ans:

Different types of connection that support SSIS are: ODBC, Flat File, XML, Excel, OLEDB, .net SLClient.

8. How many kinds of containers are present in SSIS?

Ans:

In SSIS, a container represents a reasonable grouping of tasks, and it allows dealing with the scope of a task collectively. The kinds of containers in SSIS are Sequence container and Task host container.

9. What is the meaning of Precedence Constraint in SSIS?

Ans:

Precedence Constraint in SSIS allows you to identify the reasonable sequence of tasks, and in the arrangement, they should be implemented.

10.  What are the kinds of variables in SSIS?

Ans:

Variables in SSIS are utilized to store values as the system variable and user variable.

11. What does a checkpoint in SSIS mean?

Ans:

Checkpoint in SSIS permits the project to start again from the point of breakdown. Checkpoint stores the details about the package execution; if the package runs effectively the checkpoint file is removed.

12. What are connection managers in SSIS?

Ans:

While collecting data from different sources and writing it for a purpose, connection managers are useful.  Connection manager eases the connection to the system that incorporates information like data provider, server name, etc.

13. What does SSIS breakpoint mean?

Ans:

SSIS breakpoint allows you to suspend the implementation of the package in the business intelligence expansion stage.

14. What is event logging in SSIS?

Ans:

Event logging in SSIS permits you to choose any particular event of a job and it is advantageous at what time you are getting trouble in your package.

15. What does logging mode property mean?

Ans:

SSIS packages and all the connected jobs have a property known as logging mode.   This property allows three possible values in the shape of Enabled, Disabled and Parent Setting.

16. What is a data flow buffer?

Ans:

SSIS operates by means of buffers; it is a sort of an in-memory practical table to hold data.

17. Data checkpoint data is not saved for what?

Ans:

Checkpoint data is not saved for Loop containers.

18. What do conditional split transactions in SSIS mean?

Ans:

Conditional split transformation in SSIS verifies the prearranged condition, on the basis of the condition assessment.

19. What are the different types of Data viewers in SSIS?

Ans:

Different types of data viewers are Grid, Column Chart, Histogram, and Scatter Plot.

20. What are the possible locations for saving the SSIS package?

Ans:

SSIS package can be saved at SL Server, File System or Package Store.

    Subscribe For Free Demo

    21. What is the Event Handlers tab in SSIS?

    Ans:

    With the aid of the Event Handlers tab in SSIS, workflows can be easily configured to act in response to package events.

    22. How can you inform your workforce about package failure?

    Ans:

    You can add a Send Mail Task in the event handlers. Also, you can set the warning in the SL Agent at what time the package fails to run.

    23. How is logging done in SSIS?

    Ans:

    Logging in SSIS can be done by means of events like on Warning, on Error, etc. to the manifold options like a SQL server table, XML, etc.

    24. How is the SSIS package deployed on production?

    Ans:

    In order to deploy the SSIS package, we have to implement the manifest files and need to settle on whether to deploy this onto SL Server or into File System.

    25. How to manage Late Arriving Dimension?

    Ans:

    Late Arriving Dimension is inevitable; to manage these we need to build a model dimension with business key and maintain the remaining of the attributes as default.

    26. How to perform incremental load?

    Ans:

    The greatest and top way to perform incremental load is by utilizing a Timestamp column in the supply table and storing the final ETL timestamp.

    27. What are three data flow constituents in SSIS?

    Ans:

    Three data flow constituents are SSIS are Source, Transformation, and Destination.

    28. Why checkPoints are utilized in SSIS?

    Ans:

    Checkpoint utilized in SSIS permits a package to start again at the point of breakdown.

    29. What are different alternatives for dynamic configuration in SSIS?

    Ans:

    Different alternatives for dynamic configuration are   Customer variables, XML file, and Database per set with the variables

    30. What is Data conversion Transformation?

    Ans:

    Data conversion is the most excellent method to transmit the data from one category to another.  On the other hand, you need to ensure that you have well-matched data in the column.

    Course Curriculum

    Get SSIS Certification Course to Get Best JOBs in Top MNCs

    Weekday / Weekend BatchesSee Batch Details

    31. What other tasks can SSIS handle?

    Ans:

    Some of the other tasks that SSIS handles are data profiling, effectively streamline batch operations and system filing.

    32. What do you understand by SSIS control flow system integration?

    Ans:

    This factor consists of using the logical connectors between the tasks and thus graphically control the flow of data in the system.

    33. What do you mean by data transformation?

    Ans:

    When a series of rules or functions are applied to the extracted data from the source so that it can be loaded onto the end result, it is known as data transformation.

    34. Can variables be created in SSIS?

    Ans:

    Yes, variables can be created in SSIS.

    35. Mention the two types of variables that can be created in SSIS?

    Ans:

    The two variables that can be created in SSIS are task level variables and global variables.

    36. What types of containers can be used with SSIS packages?

    Ans:

    The types of containers that can be used are sequence containers and loop containers.

    37. What do you mean by the Deployment process in SSIS?

    Ans:

    When a package is transformed from the development mode into the executables mode, it is known as the Deployment process.

    38. What do you understand about the Manifest file in SSIS?

    Ans:

    When the package is transferred using wizard on the file system and the SL server, it is known as the Manifest file.

    39. Explain the ‘Control Flow option’ in SSIS.

    Ans:

    Control Flow option allows data administrators to link and program tasks graphically and logically.

    40. What is meant by data profiling task?

    Ans:

    When source data is analyzed to understand it better in terms of hygiene, patterns, numbers, etc it is known as a data profiling task.

    41. When is the task of data profiling usually done?

    Ans:

    The task of data profiling is generally carried out at the start of the development cycle so that any teething concerns can be immediately addressed.

    42. What do you understand by a transaction in the SSIS package?

    Ans:

    In order to maintain data integrity, packages use processes called transactions to bind the database actions.

    43. What is the purpose of the config file in SSIS?

    Ans:

    This is mainly used to provide different input to the connection manager about the different properties.

    44. What is the difference between control flow and data flow?

    Ans:

    Control flow is what determines the flow or process of the package. Data flow is a subset of the control flow. Without a control flow, the data flow cannot function.

    45. What are the 4 elements (tabs) that you see on a default package designer in BIDS?

    Ans:

    Control Flow, Data Flow, event Handler, and package explorer. (Parameters – 2012 Data Tools)

    46. What is the main use of the SSIS package?

    Ans:

    This is an upgraded version that allows the seamless merging of data from numerous sources. It is also used to clear up and analyze the existing data.

    47. Name 4 important components of the SSIS package?

    Ans:

    The four important components of the SSIS package are… Data Flow, Control Flow, Package Explorer, Event Handler

    48. What is a breakpoint in SSIS? How is it set up? How do you disable it?

    Ans:

    A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an Opportunity to review the status of the data, variables and the overall status of the SSIS package.

    10 unique conditions exist for each breakpoint. Breakpoints are set up in BIDS. In BIDS, navigate to the control flow interface. Right-click on the Object where you want to set the breakpoint and select the ‘Edit Breakpoints…’ option. 

    49. What is the use of the Bulk Insert Task in SSIS?

    Ans:

    Bulk Insert Task is used to upload a large amount of data from flat files into Sql Server. It supports only OLE DB connections for the destination database.

    50. How many categories of tasks are there in SSIS?

    Ans:

    There are two units of Tasks in SSIS… Control Flow tasks and Database Maintenance Tasks

    Course Curriculum

    Attend Hands-on SSIS Training from Real-Time Experts & Build Your Skills

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum

    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 function of the Package explorer tab in SSIS?

    Ans:

    This tab provides an explorer view of the package. You can see what is happening in the package. The Package is a container at the top of the hierarchy.

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

    Ans:

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

    55. Explain Copy column Transformation?

    Ans:

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

    56. 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.

    57. 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:

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

    58. Which are the three data flow components in SSIS?

    Ans:

    The three data flow components in SSIS are Source, Transformation and Destination

    59. 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.

    60. Mention some disadvantages of SSIS?

    Ans:

    Some disadvantages of SSIS are that the vision and strategy are sometimes unclear. It can create problems in a non-Windows environment. It also does not yet provide support for alternative data integration methods.

    61. Different types of debugging in SSIS?

    Ans:

    There are 3 types of debugging

    • Execute Package partially
    • By using breakpoints
    • By using Data Viewers

    62. 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.

    63. What is package configuration?

    Ans:

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

    64. What are the types of configurations in SSIS?

    Ans:

    • XML file configuration
    • SQL server DB configuration
    • Parent package variable configuration
    • Environment variable
    • Registry entry.

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

    Ans:

    • DTEXEC
    • DTEXECUI
    • DTUTIL

    66. 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

    67. 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.

    68. How to schedule the package?

    Ans:

    By using SQL Server Agent.

    69. 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.

    70. Difference between DTS and SSIS?

    Ans:

    Everything is different except both are a product of Microsoft.

    SSIS Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

    71. 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

    72. Types of Loggings?

    Ans:

    • Flat file
    • SQL Server table
    • XML
    • SL Profiler

    73. What is CDC Splitter?

    Ans:

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

    74. What is OLEDB Command T\R?

    Ans:

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

    75. What is the Dimension table?

    Ans:

    It is a table which contains detailed data.

    76. Different types of transformation in SSIS?

    Ans:

    There are two different types of transformations

    • Synchronous transformations
    • Asynchronous transformations

    77. What is the use of CDC control tasks?

    Ans:

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

    78. 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.

    79. 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.

    • Onerror
    • Onwarning
    • OnPreexecute
    • OnPostExecute
    • OnVariableValueChanged
    • OnqueryCancelled

    80. Explain How Can You Do An Incremental Load?

    Ans:

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

    81. Different types of package storage mechanisms

    Ans:

    • File system
    • MSDB

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

    Ans:

    • Flat file
    • Excel
    • Ole DB source
    • ADO.NET

    83. How Would You Do Error Handling?

    Ans:

    A SSIS package could mainly have two types of errors

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

    84. 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.

    85. 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.

    86. 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.

    87. 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.

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

    Ans:

    Snapshot and Serializable

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

    Ans:

    This can be achieved by using the SL statement

    • SET TRANSACTION ISOLATION LEVEL
    • { READ UNCOMMITTED
    • | READ COMMITTED
    • | REPEATABLE READ
    • | SNAPSHOT
    • | SERIALIZABLE
    • }

    90. 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

    • USE Master;
    •   GO
    •   ALTER DATABASE demo_db SET READ_COMMITTED_SNAPSHOT ON;
    •   GO
    •   USE demo_db;
    •   GO

    91. 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.

    92. 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.

    93. 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.

    94. 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.

    95. 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.

    96. Name a few blocking transformations?

    Ans:

    Sorting and aggregation.

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

    Ans:

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

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

    Ans:

    • Varchar – > dt_str
    • Nvarchar – > dt_wstr

    99. What are the deployment models in SSIS?

    Ans:

    Package deployment and project deployment models.

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

    Ans:

    MSDB.sysdtspackages90 stores the actual content and subcategories, sysdtslog90, sysdtspackages90, sysdtspackages, sysdtssteplog, and sysdate tasklog do the supporting roles.

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free