Workday Frequently Asked SSIS Interview Questions Answers

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

Explain What Is A Data Flow Buffer?

SSIS operates using buffers; it is a kind of an in-memory virtual table to hold data.

For What Data Checkpoint Data Is Not Saved?

Checkpoint data is not saved for ForEach Loop and ForLoop containers.

Mention What Are The Important Components Of Ssis Package?

The important component in SSIS package are

Data flow
Control flow
Package explorer
Event handler

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.

Workday Frequently Asked SSIS Interview Questions Answers

Explain What Does It Mean By Data Flow In Ssis?

Data flow in SSIS is nothing but the flow of data from the corresponding sources to the target destinations.

Define What Is “task” In Ssis?

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

Explain What Is Ssis Package?

A package in SSIS is an organized collection of connections like data flow elements, control events, event handlers, parameters, variables, and configurations. You assemble them by either building it programmatically or by graphical design tools that SSIS provides.

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 Conditional Split Transactions In Ssis?

Conditional split transformation in SSIS is just like IF condition, which checks for the given condition based on the condition evaluation.

List Out The Different Types Of Data Viewers In Ssis?

Different types of data viewers in SSIS include

Grid
Histogram
Scatter Plot
Column Chart

Mention How Would You Deploy An Ssis Package On Production?

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.

Explain How To Handle Early Arriving Facts Or Late Arriving Dimension?

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.

Explain How Can You Do An Incremental Load?

The best and fastest way to do incremental load is by using Timestamp column in the source table and storing the last ETL timestamp.

How Would You Do Logging In Ssis?

Logging Configuration provides an inbuilt feature which can log the detail of various events like onError, onWarning etc to the various options say a flat file, SqlServer table, XML or SQL Profiler.

Mention What Are The Possible Locations To Save Ssis Package?

You can save SSIS package at

SQL Server
Package Store
File System

What Will Be Your First Approach If The Package That Runs Fine In Business Intelligence Development Studio (bids) But Fails When Running From An Sql Agent Job?

The account that runs SQL Agent Jobs might not have the required permission for one of the connections in your package. In such cases, either you can create a proxy account or elevate the account permissions.

Explain What Is The Role Of Event Handlers Tab In Ssis?

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.

Explain How You Can Notify The Staff Members About Package Failure?

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.

How Can An Ssis Package Be Scheduled To Execute At A Defined Time Or At A Defined Interval Per Day?

You can configure a SQL Server Agent Job with a job step type of SQL Server Integration Services Package, the job invokes the dtexec command line utility internally to execute the package. You can run the job (and in turn the SSIS package) on demand or you can create a schedule for a one time need or on a reoccurring basis. Refer to this tip to learn more about it.

How Would You Do Error Handling?

A SSIS package could mainly have two types of errors

a) Procedure Error: Can be handled in Control flow through the precedence control and redirecting the execution flow.

b) Data Error: is handled in DATA FLOW TASK buy redirecting the data flow using Error Output of a component.

How To Pass Property Value At Run Time? How Do You Implement Package Configuration?

A property value like connection string for a Connection Manager can be passed to the pkg using package configurations.Package Configuration provides different options like XML File, Environment Variables, SQL Server Table, Registry Value or Parent package variable.

If You Want To Send Some Data From Access Database To Sql Server Database. What Are Different Component Of Ssis Will You Use?

In the data flow, we will use one OLE DB source, data conversion transformation and one OLE DB destination or SQL server destination. OLE DB source is data source is useful for reading data from Oracle, SQL Server and Access databases. Data Conversion transformation would be needed to remove datatype abnormality since there is difference in datatype between the two databases (Access and SQL Server) mentioned. If our database server is stored on and package is run from same machine, we can use SQL Server destination otherwise we need to use OLE DB destination. The SQL Server destination is the destination that optimizes the SQL Server.

What Is Sql Server Integration Services (ssis)?

SQL Server Integration Services (SSIS) is component of SQL Server 2005 and later versions. SSIS is an enterprise scale ETL (Extraction, Transformation and Load) tool which allows you to develop data integration and workflow solutions. Apart from data integration, SSIS can be used to define workflows to automate updating multi-dimensional cubes and automating maintenance tasks for SQL Server databases.

How Does Ssis Differ From Dts?

SSIS is a successor to DTS (Data Transformation Services) and has been completely re-written from scratch to overcome the limitations of DTS which was available in SQL Server 2000 and earlier versions. A significant improvement is the segregation of the control/work flow from the data flow and the ability to use a buffer/memory oriented architecture for data flows and transformations which improve performance.

What Is The Control Flow?

When you start working with SSIS, you first create a package which is nothing but a collection of tasks or package components. The control flow allows you to order the workflow, so you can ensure tasks/components get executed in the appropriate order.

What Is The Data Flow Engine?

 The Data Flow Engine, also called the SSIS pipeline engine, is responsible for managing the flow of data from the source to the destination and performing transformations (lookups, data cleansing etc.).  Data flow uses memory oriented architecture, called buffers, during the data flow and transformations which allows it to execute extremely fast. This means the SSIS pipeline engine pulls data from the source, stores it in buffers (in-memory), does the requested transformations in the buffers and writes to the destination. The benefit is that it provides the fastest transformation as it happens in memory and we don't need to stage the data for transformations in most cases.