Question 1 : What kind of variables can you create in SSIS?
Answer : You can create global variables and task level variables in SSIS. For programmers, these variables are the same as global and function level variables. A global variable is available to all tasks across the entire job. Variables created in tasks are only available within that task.
Learn SSIS to Unleash a Modern Career
Question 2 : What is the difference between Merge and Union All?
Answer : The Merge transformation can merge data from two paths into a single output. The Transform is useful when you wish to break out your Data Flow into a path that handles certain errors and then merge it back into the main Data Flow downstream after the errors have been handled. it’s also useful if you wish to merge data from two Data Sources.
Note that the data must be sorted before using the Merge Transformation. you can do this by using the sort transformation before the merge or by specifying an ORDER By clause in the source connection. Also, the metadata must be the same for both paths. For example, the CustomerID column cannot be a numeric column in one path and a character column in the other path.
The Union All Transformation works much the same way as the Merge Transformation, but it does not require the data to be sorted. It takes the outputs from multiple sources or transforms and combines them into a single result set.
Question 3 : What is Data Transformation?
Answer : The data transformation stage applies a series of rules or functions to the extracted data from the source to derive the data for loading into the end target.
Question 4 : Explain the difference between Execute TSQL Task and Execute SQL Task?
Answer : In SSIS there is one task Execute TSQL task which is similar to the Execute SQL task. We will see what is the difference between the two.
Execute the TSQL Task:
Pros: Takes less memory, faster performance
Cons: Output into variable not supported, only supports ADO.net connection
Execute SQL Task:
Pros: Support output into variables and multiple types of connection, parameterized query possible.
Cons: Takes more memory, slower performance compared to the TSQL task.
Question 5 : What is the difference between Control Flow and Data Flow?
Answer : The control flow is for designing the flow of the package. Data flow is for the ETL process.
Data Flow is the subset of control flow
There will be only one control flow while multiple dataflow can exists.
Data flow cannot work without a control flow
All process base tasks are part of control flow while ETL related tasks are the part of Dataflow which is again a subset of control flow.
Question 6 : What is the SSIS breakpoint?
Answer : A breakpoint enables you to pause the execution of the package in the business intelligence development studio during troubleshooting or development of an SSIS package.
Question 7 : What is logging mode property?
Answer : SSIS packages and all the associated tasks have a property called LoggingMode. This property accepts three possible values.
Disabled: To disable logging of the component
Enabled: To enable logging of the part
Use Parent Setting: To use the parent’s setting of the component
Question 8 : Explain two disadvantages of SSIS
Answer :
- SIS sometimes create issues in non-windows environments
- SSIS doesn’t provide support for alternative data integration styles
Question 9 : What is the SSIS Catalog?
Answer : The SSIS catalog is a database to store all the deployed packages. It is widely used for security reasons to store and handle the deployed packages.
Question 10 : How would you stop a package that is running forever?
Answer : It depends. If you are running the package in the SQL Agent, you can kill the process using T-SQL. However, if the package is running in the SSIS catalog, you can stop it using the Active Operations window or the stop operation stored procedure.
Learn SSIS to Unleash a Modern Career
Question 11 : Define the SSIS.
Answer : SSIS is known as SQL Server Integration Services that is a component of Microsoft SQL Server. It can be used to accomplish a wide range of data migration tasks. It is an ETL tool that is mostly used to handle data extraction, transformation, and load operations. Apart from ETL tasks, it can also handle many other tasks like data profiling, file system manipulation, etc. Many batch operations can be performed in SSIS by using VB.Net or C#.Net languages.
Question 12 : What is a project and Package in SSIS?
Answer : Project is a container for developing packages. Package is nothing but an object. It implements the functionality of ETL — Extract, Transform and Load — data.
Question 13 : Explain the control flow integration of SSIS.
Answer : Through the control flow option, you can logically and graphically link the tasks and program them graphically. The three logical connectors that are used in SSIS are the success, failure and complete. By using FX (expression) you can handle more complex conditions of the control flow.
Question 14 : What is Manifest file in SSIS?
Answer : Manifiest file is the utility which can be used to deploy the package using wizard on file system and SQL Server database.
Question 15 : Enlist important SSIS components of the package.
Answer : Important SSIS components of the package are listed below:
- Data Flow
- Control Flow
- Event Handler
- Package Explorer
Question 16 : Define what is “task” in SSIS?
Answer : Task in SSIS is a very much similar to the method of any programming language that represents or carries out an individual unit of work. Tasks are categorized into two categories
- Control Flow Tasks
- Database Maintenance Tasks
Question 17 : What is a checkpoint in SSIS?
Answer : Checkpoint in SSIS allows the project to restart from the point of failure. Checkpoint file stores the information about the package execution, if the package run successfully the checkpoint file is deleted or else it will restart from the point of failure.
Question 18 : What is conditional split transactions in SSIS?
Answer : Conditional split transformation in SSIS is just like IF condition, which checks for the given condition based on the condition evaluation.
Question 19 : List out the different types of Data viewers in SSIS?
Answer : Different types of data viewers in SSIS include
- Grid
- Histogram
- Scatter Plot
- Column Chart
Question 20 : Explain the data flow in SSIS.
Answer : Data flow from the corresponding source to the desired destination is known as the data flow.
Learn SSIS to Unleash a Modern Career
Question 21 : What are the possible locations to save SSIS package?
Answer : You can save SSIS package at
- SQL Server
- Package Store
- File System
Question 22 : What is the role of Event Handlers tab in SSIS?
Answer : On the event handlers tab, workflows can be configured to respond to package events. For instance, you can configure workflow when any task stops, fails or starts.
Question 23 : How would you do logging in SSIS?
Answer : Logging in SSIS can be done by logging various events like onError, onWarning, etc. to the various options like a flat file, XML, SQL server table, etc.
Question 24 : What are the connection managers in SSIS?
Answer : Connection managers are found much helpful at the time when data has to be gathered from various sources to write it to any desired destination. Connection managers provide much useful information to the system like server name, data provider information, database name, and authentication mechanism, etc.
Question 25 : Explain the Precedence Constraint in SSIS.
Answer : The logical task sequence can be defined by precedence constraint, logical sequence is basically the order of the tasks in which they should be executed. All tasks can be connected by using precedence constraints.
Question 26 : What is data profiling task in SSIS?
Answer : The process of analyzing the source data for better understanding and organizing it properly is known as data profiling. In this process, various tasks related to data like cleaning of data, identification of data patterns and numbers or nulls in data is known as data profiling.
Data profiling step is performed when a project starts or at the beginning of the project development cycle and it supports database destination design schema. While developing normal recurring ETL packages this task is not used usually.
Question 27 : What is event logging mode property?
Answer : All tasks and SSIS packages have a property that is known as Logging Mode. The three values that are accepted by these properties are:
- Enabled: Is used to logging of the component
- Disabled: Is used to disable the components
- UseParentSetting: Is used to optimize the parent’s setting
Question 28 : Mention how would you deploy an SSIS package on production?
Answer : To deploy SSIS package we need to execute the manifest files and need to determine whether to deploy this into File System or onto SQL Server. Alternatively you can also import package from SSMS from SQL Server or File System.
Question 29 : Explain what is a container? How many types of containers are there in SSIS?
Answer : In SSIS, a container is a logical grouping of tasks, and it allows to manage the scope of a task together.
Types of containers in SSIS are
- Sequence container
- For loop container
- Foreach loop container
- Task host container
Question 30 : List out different types of connection or files that support SSIS?
Answer : Different types of connection that work within SSIS are
- ODBC
- OLEDB
- .net SQLClient
- Flat File
- Excel
- XML
Learn SSIS to Unleash a Modern Career
Question 31 : What is Solution Explorer in SSIS?
Answer : Solution Explorer in SSIS Designer is a screen where you can view and access all the data sources, data sources views, projects, and other miscellaneous files.
Question 32 : What is a data flow buffer?
Answer : SSIS operates using buffers; it is a kind of an in-memory virtual table to hold data.
Question 33 : How you can notify the staff members about package failure?
Answer : Either inside the package you could add a Send Mail Task in the event handlers, or you can even set notification in the SQL Agent when the package runs.
Question 34 : How to handle Early Arriving Facts or Late Arriving Dimension?
Answer : Late Arriving Dimension are unavoidable, to handle these we can create a dummy dimensions with natural/business key and keep the rest of the attributes as null or default. So when actual dimension arrives, the dummy dimension is updated with Type 1 change. This is also referred as Inferred Dimensions.
Question 35 : How can you do an incremental load?
Answer : The best and fastest way to do incremental load is by using Timestamp column in the source table and storing the last ETL timestamp.