Here are SSIS interview questions and answers below:
- What is SSIS?
SSIS was first introduced with SQL Server in 2005, which was the next generation of SQL Server software after SQL Server 2000. SSIS is an ETL tool that handles data Extraction, Transformations and load. In addition, it can handle more tasks besides the ETL tasks such as data profiling, file system manipulation etc. If you know C#.net or VB.net, you can use the script task to perform much more efficient batch operations.
- Name a few SSIS components?
- Integration Services Projects
- Integration Services Packages
- Control Flow Elements
- Data Flow Elements
- Integration Services Connections
- Integration Services Variables
- Integration Services Event Handlers
- Integration Services Log Providers
- What is SSIS control flow integration?
SSIS control flow allows you to plan how to manage the functionality by using meaningful links between functions. There are three key combinations you can use: success, failure, or completeness. Additionally, you can use the FX (statement) to take the form that is best suited to your control.
- What is a project and Package in SSIS?
A project is a container for developing packages. A package is nothing but an object. It implements the functionality of ETL — Extract, Transform and Load — data.
- What are a Control Flow and Data Flow elements in SSIS?
Control flow element is the one who performs a function or creates a system controls the flow of the elements. You must have at least one of the control keys in the SSIS section. In the SSIS, the operating system has been utilized. Controllers are connected with the list of events in the data list to achieve the desired results.
A control flow consists of one or more tasks that are implemented when the package runs. To manage the processor determine the mode in which the next task or task is to be administered in the package control
All ETL tasks related to data are done by data flow elements. It is not necessary to have a data flow element in the SSIS package. A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. Before you can add a data flow to a package, the package control flow must include a Data Flow task. The Data Flow task is the executable within the SSIS package that creates, orders and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package.
- What is Data Transformation?
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.
- What kind of variables can you create?
You can create global changes with levels in SSIS. For programmers, these variables are consistent with the global and function level variables. There is a global problem for all functions across the project. The difference created in the services is only available in this function.
- What are the 3 different types of control flow elements in SSIS?
- Structures provided by Containers
- The functionality provided by Tasks
- Precedence constraints that connect the executable, containers, and tasks into an ordered control flow.
- What are the 3 data flow components in SSIS?
- What are connections and connection managers in SSIS?
Connection, as its name suggests, is a component to connect to any source or destination from SSIS — like a SQL server or flat file or lot of other options that SSIS provides. The connection manager is a logical representation of a connection.
- What is the use of Check Points in SSIS?
SSIS provides a Checkpoint capability which allows a package to restart at the point of failure.
- What are the command line tools to execute SQL Server Integration Services packages?
- DTSEXECUI – When this command line tool is run a user interface is loaded in order to configure each of the applicable parameters to execute an SSIS package.
- DTEXEC – This is a pure command line tool where all of the needed switches must be passed into the command for successful execution of the SSIS package.
- What kind of containers can you use with SSIS packages?
There are three types: sequence, for loops and for each loop.
A sequence is an easy way to integrate such synchronization. Think of a sequence container as an organization container for more complex SSIS packages.
Here is a loop lock that you can use to carry out your activities at other times. For example, you need to re-publish ten times, you can customize the task that updates the files in this resource source and inserts 10 as the end of the loops. Using a looped source, you do not have to create 10 different organizations to do this, or you must complete the whole package ten times when you plan your service.
For each case checkbox, it will be useful if you do not know the time period for a while. For example, let’s say that you want to delete all files in a folder, but you do not know the files at any time, using any loop, it can log into the collection of files and delete them For you, after the collection, Unfortunately, he knows that when he should stop.
- What is a Manifest file in SSIS?
A manifest file is a utility which can be used to deploy the package using wizard on file system and SQL Server database.
- What is File system deployment?
File system deployment means to save package file on local or network drive. Then you can use SQL Agent job to schedule when the packages will run.
- How to back up or retrieve the SSIS packages?
If your package is deployed on SQL Server then you can back up the MSDB database as all the package on SQL server deploys at MSDB.
- What is the multicast Transformation in SSIS?
The Multicast transform, as the name suggests, can send one single entry into simple ways. You probably want to use this change to send the way to many places to slice in different ways. Changing changes are similar to Transforming because all data is sent too many samples. However, you cannot verify the condition that any part of the data will be in the export at the Multicast station.
- Difference between Merge and Union All?
The Merge transformation can merge data from two paths into a single output. This exchange is useful if you want to break Data Flow into the path that manages some errors and then connect it to the Big Data Flow from the ground after errors are used. It is also useful if you want to compile information from two sources.
Please note that the transmitter must be separated after using the Change Changes. You can do this by using such changes before connecting or submitting the ORDER through the link into the link. Also, my metadata standards must be equal in two ways. For example, Client Partner cannot be a contact key in the same manner and also applies in a different way.
The All Transformation Association works extensively in the same way as Changing Change but does not require the formatted information. It takes samples from many sources or changes and connects them to the same setup function.
- What is the use of config file in SSIS?
Config file in SSIS is used to provide inputs to connection manager different properties which package use to assign values at run time dynamically.
Using config file user need to make changes to the config file which package will take automatically at run time because of using it you don’t need to every time make changes in the packages in case you are deploying package on multiple servers or locations. There are multiple ways in which configuration values can be stored.
XML configuration file: Store the config file as an XML file.
Environment variable Store the config in on of the environment variables.
Registry entry Store the config in the registry
Parent package variable Store the config as a variable in the package that contains the tasks.
SQL Server Store the config in a table in SQL Server.
- How the DTR works in SSIS?
- The DTR consists of the DTR engine and the DTR components.
- DTR components are objects that enable you to govern the execution of SSIS packages.
- The primary DTR components are packages, containers, and tasks.
- DTR engine stores package layout; runs packages; and provides debugging, logging, and event handling services.
- The DTR is accessed using the DTR object framework. The DTR run-time object framework is the API that supports the Integration Services Import/Export Wizard and the Integration Services Designer in addition to the command-line dtexec tool.