Define SSIS?
SQL Server Integration Services — commonly known as SSIS is the new platform that was introduced in SQL Server 2005, for data transformation and data integration solutions. This replaced the DTS in SQL Server 2000.
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 precedence constraint?
A precedence constraint is a link between 2 control flow tasks and lays down the condition on which the second task is run. They are used to control the workflow of the package. There are 3 kinds of precedence constraint – success (green arrow), failure (red arrow), or Completion script task (blue arrow). By default, when we add 2 tasks, it links by the green arrow. The way the precedence constraint is evaluated can be based on the outcome of the initial task. Also, we can add expressions to evaluate such outcomes. Any expression that can be judged as true or false can be used for such purpose. The precedence constraint is very useful in error handling in the SSIS package.
Are you Looking for SSIS Training? Please Enroll for Demo SSIS..! |
What is a project and Package in SSIS?
The 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.
Can we add our custom code in SSIS?
We can customize SSIS through code by using Script Task. The main purpose of this task is to control the flow of the package. This is very useful in the scenario where the functionality you want to implement is not available in the existing control flow item.
What are the 4 elements (tabs) that you see on a default package designer in BIDS?
Control Flow, Data Flow, event Handler, and package explorer. (Parameters – 2012 Data Tools)
What are the Control flow and Data Flow elements in SSIS?
Control Flow:
A control flow element performs any function that provides structure or controls the flow of the elements. There must be at least one control flow element in the SSIS package. In SSIS a workflow is called a control flow. A control flow links together our modular data flow as a series of operations to achieve the desired result.
A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow
Data Flow:
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.
Explain the use of containers in SSIS and also their types.
Containers can be defined as objects that store one or more tasks. The primary purpose of a container is to group logically related tasks. Once the task is placed into the containers, we can perform various operations such as looping on the container level until the desired criterion is met. Nesting of the container is allowed. The container is placed inside the control flow.
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 executables, containers, and tasks into an ordered control flow.
What are the 3 data flow components in SSIS?
Source
Transformation
Destination
Why is the a need for data conversion transformations?
This transformation converts the datatype of input columns to a different datatype and then routes the data to output columns.
This transformation can be used to:
- Change the data type
- If the datatype is a string then for setting the column length
- If the datatype is numeric then for setting decimal precision.
This data conversion transformation is very useful when you want to merge data from different sources into one. This transformation can remove the abnormality of the data. Example à The Company’s offices are located in different parts of the world. Each office has a separate attendance tracking system in place. Some offices store data in Access database, some in Oracle and some in SQL Server. Now you want to take data from all the offices and merge them into one system. Since the datatypes in all these databases vary, it would be difficult to perform a merge directly. Using this transformation, we can normalize them into a single datatype and perform the merge.
Error Handling in SSIS?
An error handler allows us to create flows to handle errors in the package in quite an easy way. Through the event handler tab, we can name the event on which we want to handle errors and the task that needs to be performed when such an error arises. We can also add sending mail functionality in the event of any error through SMTP Task in the Event handler. This is quite useful in the event of any failure in office non-working hours. In the Data flow, we can handle errors for each connection by following the failure path or red arrow.
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 flat file or a lot of other options that SSIS provides. A connection manager is a logical representation of a connection.
Explain what is Solution Explorer in SSIS?
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.
Explain what is a container? How many types of containers are there in SSIS?
In SSIS, a container is a logical grouping of tasks, and it allows one 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
Related Courses
| Course Name | Enroll Now |
|---|---|
| MSBuild Training | Enroll Now |
| ETL Testing Training | Enroll Now |
| SSAS Training | Enroll Now |
| SSRS Training | Enroll Now |
Explain the Precedence Constraint in SSIS?
Precedence Constraint in SSIS enables you to define the logical sequence of tasks in the order they should be executed. You can connect all the tasks using connectors- Precedence Constraints.
Explain what variables are in SSIS and what are the types of variables in SSIS?
Variable in SSIS is used to store values. In SSIS, there are two types of variables system variable and user variable.
Explain what is a checkpoint in SSIS?
A checkpoint in SSIS allows the project to restart from the point of failure. The checkpoint file stores the information about the package execution, if the package runs successfully the checkpoint file is deleted or else it will restart from the point of failure.
Explain what is connection managers in SSIS?
While gathering data from different sources and writing it to a destination, connection managers are helpful. The connection manager facilitates the connection to the system that includes information like data provider information, server name, authentication mechanism, database name, etc.
Explain what is SSIS breakpoint?
A breakpoint enables you to pause the execution of the package in the business intelligence development studio during the troubleshooting or development of an SSIS package.
Explain what is event logging in SSIS?
In SSIS, event logging allows you to select any specific event of a task or a package to be logged. It is very helpful when you are troubleshooting your package to understand the performance package.
Explain what is logging mode property?
SSIS packages and all the associated tasks have a property called LoggingMode. This property accepts three possible values
Disabled: To enable logging of the component
Enabled: To disable logging of the component
UseParentSetting: To use the parent’s setting of the component
Related Articles:
🎯 Alteryx Designer vs. Microsoft Power BI – 2023 Comparison
🎯 Automating processes with Microsoft Power Automate
🎯 Career Opportunities on Power BI
🎯 Comparison between Microsoft Dynamics and Oracle CRM
🎯 Key Concepts on Microsoft Dynamics AX
🎯 Latest Microsoft Azure Fabric Interview Questions – 2025
🎯 Microsoft Azure Features & Architecture