10 Most asked SSIS Questions in SQL Job Interview 2022
This article is about the topic: “10 Most asked SSIS Questions”. We will discuss some of the very important question related to SSIS which are most asked by the interviewers.
Q1). What is SSIS?
- Tasks
- variables
- Connections
- Events handlers
Q2. How you can deploy SSIS package in to SQL server?
There are the two methods to install SSIS package into SQL server.
- SSIS can be deployed using Microsoft Visual Studio, after installing the SSIS use the deploy option of the project which will be deployed on SSIS DB under the Integration Services Catalogs folder.
- You can deploy SSIS using SQL Server Management Studio
Q3. What are Expressions & variables that you can create in SSIS?
Like the programming languages, variables in SQL Server Integration Services are used to store values. There are two types of variables in SSIS:
- System variable: Variables, which are defined by Integration Services.
- User variable: These variables are define by package developers
Purpose of variables:
- Updating properties of package-elements at run-time.
- Building expressions that include variable values.
- Creating variables with data-values or with an integer.
- Creating an in-memory lookup table.
- Populating parameter_values for SQL statements at run-time.
SSIS expression is a code which may be a combination of parameters, variables or functions & it returns values.
Normally, expressions are complex, use multiple operators &functions, referencing multiple-columns and variables. In SSIS, expressions can be used to define conditions for CASE statements, to create & update values in data-columns, assign value to a variable, update or populate properties at run time etc. Expressions in SSIS are based on an expression-language, and the expression-evaluator.
Q4. Difference between Merge and Union All?
- The first different is that you can combain only two data-sets with Merge while with Union All you can have more than two datasets for input.
- The another difference is that Union All does not require sorted datasets while Merge requires both datasets to be sorted orders. In Merge the rows from each data-set are inserted in to output based on values in their key-columns.
- Both transformations are considered to be partially blocking. The Union All transformation combines multiple inputs into one output. The transformation inputs are added to the transformation output one after the other and no reordering of rows occurs.
The Merge-transformation is close to the Union All. It is better to use Union-All transformation instead of the Merge in the following circumstances:
- The combined output does not require to be sorted.
- More than 2 input datasets.
- The transformation-inputs are not sorted.
Q5. What is the use of config file in SSIS?
In SSIS a Config-file is used to give inputs to connection-manager different-properties which package use to assign values at run-time dynamically.
The advantage of using config-file is that you only require to make changes in config-file & package will take automatically at rudiment. In presence of config-file you do not need to make changes in the packages every time. There are many methods in which configuration values can be stored.
- You can store the config-file as an XML-file.
- Registry entry Store the config file in registry.
- S Q L Server Store the config-file in a table on SQL Server
- Environment variable Store the config in on of the environment variables.
- Parent package variable Store the config as a variable in the package that contains the tasks.
Q6. Explain the possible locations to save the SSIS package?
You can save the SSIS packages in to following locations:
- Package-store
- SQL Server
- File System
Q7. What is a checkpoint in SSIS?
SSIS checkpoints are used to restart the packages from the point of failure. Instead of re running the complete package or all tasks again, you can start it from point of failure. This is very helpful for loading large data, or importing or exporting an image. SSIS Checkpoints can be very useful in case where the task prior to the point of failure takes a long time to execute package.
How to Configure SSIS checkpoint:
Select the package in which you need to configure checkpoints, & goto its properties windows to check the Checkpoint_property.
- CheckpointFileName: You need to select the file-name. SSIS will use this file to save the information related to a checkpoint.
- CheckpointUsage: This checkpoint’s property has three options:
- IfExists: Package will look for the checkpoint info, & if there is any information found then it will use that info, If not then it ll skip using it.
- Never: Package will not use the Checkpoint.
- Always: Package will always look for the checkpoint information, and use that information.
- SaveCheckpoint: Using this property you can save the checkpoint.
Q8. What type of containers you can use with SSIS?
Server Integration Services’s containers are objects that gives structure to SSIS-packages. SSIS containers provides repeating control or flows in packages. They group tasks and containers into meaningful units of work. Types of containers are as ollowing:
- A for loop container is use to execute a tasks to a certain number of times. If you are required to change the records 5 times, you can use the task that updates the records inside this for loop-container and specifies 5 as the end of the loops. by using the for loop container, you don’t have to create either 5 different packages to do the same task.
- A for each loop container will be helpful when you don’t know a head of time how many times a task should perform. for instance, let’s say that you want to delete all the files inside a folder, but you don’t know how many files are there. With for each loop, it calculate all files and delete them for you.
- A sequence container is a simple way to group similar tasks together. Think of a sequence container as an organization container for more complex SSIS packages.
Q9. What is a Control flow and Data Flow elements in SSIS ?
- Success
- Failure
- Complete
The package control flow must have a Data Flow task, before you can add a data flow to package. A separate instance of the data flow engine is opened for each Data Flow task in a package.The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. Data flow from the corresponding source to the desired destination is known as the data flow.
Q10. List various types of files or connections that support SSIS.
Various connection types that work within SSIS are:
- Excel
- OLEDB
- .NetSQLClient
- XML
- ODBC
- Flat File
