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 green arrow. The way the precedence constraint is evaluated can be based on outcome of the initial task. Also, we can add expression to evaluate such outcome. 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 SSIS package.
Are you Looking for SSIS Training? Please Enroll for Demo SSIS..! |
What is a project and Package in SSIS?
Project is a container for developing packages. 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 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 is a Control flow and Data Flow elements in SSIS?
Control Flow:
Control flow element is one that performs any function or provides structure or control 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-flows as a series of operations in order to achieve a 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 stores one or more tasks. The primary purpose of container is grouping logically related tasks. Once the task is placed into the containers, we can perform various operations such as looping on container level until the desired criterion is met. Nesting of container is allowed. Container is placed inside the control flow.
What are the 3 different types of control flow elements in SSIS?
Structures provided by Containers
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 need for data conversion transformations?
This transformation converts the datatype of input columns to different datatype and then route the data to output columns.
This transformation can be used to:
- Change the datatype
- If datatype is string then for setting the column length
- If datatype is numeric then for setting decimal precision.
This data conversion transformation is very useful where you want to merge the data from different source into one. This transformation can remove the abnormality of the data. Example à The Company’s offices are located at different part of world. Each office has separate attendance tracking system in place. Some offices stores data in Access database, some in Oracle and some in SQL Server. Now you want to take data from all the offices and merged into one system. Since the datatypes in all these databases vary, it would be difficult to perform merge directly. Using this transformation, we can normalize them into single datatype and perform 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 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 event of any error through SMTP Task in Event handler. This is quite useful in event of any failure in office non-working hours. In Data flow, we can handle errors for each connection through following 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 or flat file or lot of other options that SSIS provides. 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 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
Explain what is 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 in SSIS and what are the types of variables in SSIS?
Variable in SSIS is basically used to store values. In SSIS, there are two types of variables system variable and user variable.
Explain what is a checkpoint in SSIS?
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.
Explain what is connection managers in SSIS?
While gathering data from different sources and writing it to a destination, connection managers are helpful. Connection manager facilitates the connection to the system that include information’s 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 business intelligence development studio during troubleshooting or development of an SSIS package.
Are you Looking for SSIS Online Training? Please Enroll for Demo SSIS..! |
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 parent’s setting of the component