SSIS Interview Questions - Basics

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.

Differentiate SSIS and 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 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 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.

What is Transformation?

•  A transformation simply means bringing in the data in a desired format. For example you are pulling data from the source and want to ensure only distinct records are written to the destination, so duplicates are  removed.  Anther example is if you have master/reference data and want to pull only related data from the source and hence you need some sort of lookup. There are around 30 transformation tasks available and this can be extended further with custom built tasks if needed.

What is Task?

• A task is very much like a method of any programming language which represents or carries out an individual unit of work. There are broadly two categories of tasks in SSIS, Control Flow tasks and Database Maintenance tasks. All Control Flow tasks are operational in nature except Data Flow tasks. Although there are around 30 control flow tasks which you can use in your package you can also develop your own custom tasks with your choice of .NET programming language.

What is a Precedence Constraint and what types of Precedence Constraint are there?

• SSIS allows you to place as many as tasks you want to be placed in control flow. You can connect all these tasks using connectors called Precedence Constraints. Precedence Constraints allow you to define the logical sequence of tasks in the order they should be executed. You can also specify a condition to be evaluated before the next task in the flow is executed.
• These are the types of precedence constraints and the condition could be either a constraint, an expression or both 
  • Success (next task will be executed only when the last task completed successfully) or
  • Failure (next task will be executed only when the last task failed) or
  • Complete (next task will be executed no matter the last task was completed or failed).

What is a container and how many types of containers are there?

• A container is a logical grouping of tasks which allows you to manage the scope of the tasks together.
• These are the types of containers in SSIS:
  • Sequence Container - Used for grouping logically related tasks together
  • For Loop Container - Used when you want to have repeating flow in package
  • For Each Loop Container - Used for enumerating each object in a collection; for example a record set or a list of files.
• Apart from the above mentioned containers, there is one more container called the Task Host Container which is not visible from the IDE, but every task is contained in it (the default container for all the tasks).

What are variables and what is variable scope?

• A variable is used to store values. There are basically two types of variables, System Variable (like ErrorCode, ErrorDescription, PackageName etc) whose values you can use but cannot change and User Variable which you create, assign values and read as needed. A variable can hold a value of the data type you have chosen when you defined the variable.
• Variables can have a different scope depending on where it was defined. For example you can have package level variables which are accessible to all the tasks in the package and there could also be container level variables which are accessible only to those tasks that are within the container.

A Good Day @ Work

Dear bloggers, it is a great start of the month. Had a very awesome morning. I say this because its the salary day as usual and this time I have got something extra. Also, they have calculated and have given me so called an interest amount. On the work front, it was a hectic day which made me sit in my chair for almost 11 hours. Oh! how many issues? Huh!!! Mostly, Friday's will be full of fun and frolic and this time it was full of work.

Today is a great day to be remembered for the reason that it was my favorite lead Mr.Ramanan's last day in our organization. Just a great person he has been. He had taught me a number of technical stuffs and I myself got confidence in developing applications only because of him. Though I had not spent much time with him, he has always been an inspiration for me. For everyone of us, someone would induce the spark within ourselves. I would gladly say it's Ramanan in my case. When it comes to workplace, everyone peeps in, everyone will look for a better opportunity and peep out. Though it is quite natural, somewhere my heart cries for missing such a wonderful person in my workplace. This post is a dedication to you sir!!!

Keep in touch :) All the very best in watev@ U do!!!

PIVOT TABLE ~ SQL SERVER

What is a Pivot Table ?

A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values. Pivot tables are easily created by office users using Microsoft Excel or MS Access.
Since pivot table enables report builders and BI (Business Intelligence) specialists empower their presentation of reports and increase the visibility and unserstandability of mined data, pivot tables are common and preferred widely. Pivot tables display data in tabular form. The pivot table formatting is not different than a tabular report formatting.

But the table columns are formed by the report data itself. I mean as a pivot table example, your report creator can build a report with years and months in the left side of the table, the main product lines are displayed as columns, and total sales of each product line in the related year and month is displayed in the cell content.

Actually you can easily answer what is pivot table question, if you have build OLAP reports or if you are familiar with OLAP reporting. Pivot Table in sql grants the ability to display data in custom aggregations just like OLAP reports in SQL Server. Simply pivot tables can be thought of transforming a table with its data into another table format. Just as building a sales report in months and product lines from Sales Orders table.

Difference between JOINS and UNION in SQL

In this post, I have given the difference between the use of Joins and the use of Union keyword in SQL.

I have a table named Table1 with Severity, Response SLA and Resolution SLA as its columns. Now I need to take the number of issues for both Response SLA and Resolution SLA based on Severity.

1. Using Inner join:

SELECT A.[Severity],A.[ResponseSLA],A.[Count],B.[ResolutionSLA],B.[Count] FROM
(
SELECT [Severity],[ResponseSLA], Count(1) As [Count] FROM Table1
WHERE GroupID = 4 AND [Month] = 'Mar - 2010'
GROUP BY [Severity],[ResponseSLA]
) A
INNER JOIN
(
SELECT [Severity],[ResolutionSLA], Count(1) As [Count] FROM Table1
WHERE GroupID = 4 AND [Month] = 'Mar - 2010'
GROUP BY [Severity],[ResolutionSLA]
) B
ON A.[Severity] = B.[Severity]
Order By A.[Severity]

The above query fetches the following result:

Severity	ResponseSLA	Count	ResolutionSLA	Count
Sev2	Not Met SLA	1	Not Met SLA	1
Sev3	Met SLA	29	Met SLA	31
Sev3	Not Met SLA	2	Met SLA	31
Sev4	Met SLA	597	Met SLA	613
Sev4	Not Met SLA	16	Met SLA	613

Note: You can replace "SELECT A.[Severity],A.[ResponseSLA],A.[Count],B.[ResolutionSLA],B.[Count] FROM" With "SELECT * FROM" and fetch all the columns.

2. Using Union:

Consider you need to calculate the total count. Then you should query as below.

SELECT * FROM
(
SELECT [Severity],[ResponseSLA], Count(1) As [Count] FROM Table1
WHERE GroupID = 4 AND [Month] = 'Mar - 2010'
GROUP BY [Severity],[ResponseSLA]
) A
UNION
(
SELECT 'Total' [Severity],'' [ResponseSLA], Count(1) As [Count] FROM Table1
WHERE GroupID = 4 AND [Month] = 'Mar - 2010'
)

The above query fetches the following result:

Severity	ResponseSLA	Count
Sev2	Not Met SLA	1
Sev3	Met SLA	29
Sev3	Not Met SLA	2
Sev4	Met SLA	597
Sev4	Not Met SLA	16
Total		645

Note: In UNION, the number of columns and the column names should match. In this case, the columns of both A & B should match each other.

Hope you understood the difference. Joins always add the columns to the left and the union statements add the columns to the bottom of the result grid.