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.

An Overview of SSIS

Most of us have been using Data Transformation Services (DTS) in the previous versions of SQL Server in order to manipulate or move data. With the introduction of SQL Server 2005, Microsoft has introduced a completely re-written version of DTS with a lot more new features which is now known as Integration Services. This new ETL platform is nothing but a set of utilities, applications, designers, components, and services all wrapped up into one powerful software application suite. The most attracting feature of SSIS is that the data movement and transformation is separate from the package control flow and management. There are two different engines that handle these tasks. Integration Services Data Flow engine takes care of the data movement and transformation whereas the Integration Services Run-time engine manages the package control flow. The motivation behind this article is that the SSIS API is not well documented in MSDN though you get an overall idea of what is going on. This article assumes that the reader is aware of the fundamental components of an SSIS Package.

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!!!

SSRS Approach

If you’re a seasoned writer of SRSS reports (or any type of RDBMS reporting system), you’ll be used to trawling through databases, tables, fields and indexes to deliver that right combination of headings and columns which present the underlying data as your users want to see it. Well, here’s a report that doesn’t involve any of that. OK, there’s a database, but only as a place to store the code and the only table is a temporary one. I am preparing a detailed note on SSRS and will post it soon.

Sync data using SSIS

If you aren't aware of the power of SharePoint custom lists yet, the topic is worth investigating. SharePoint custom lists combine the ease-of-use that has made Excel Spreadsheets and Access databases so successful among "knowledge workers" in many organizations with the benefits of traditional databases. They offer Access-like simplicity of both administration (creating and modifying lists) and data access, while overcoming one of Access' biggest challenges: decentralization.

In addition to centralizing data on a server, SharePoint lists benefit from other traditional database features such as referential integrity, indexes, and views. They also benefit from SharePoint-provided features such as native support for workflows and automatic versioning.

And if that isn't enough to convince you of the likelihood that you'll interact with them in the near future, consider how seriously Microsoft is pushing the technology by integrating SharePoint and Microsoft Office 2007. Even if you aren't using lists today, the chances are good you will be before long.

Create a SSIS Package

Sql Server Integration Services is a new ETL (Extraction Transformation and Loading) platform that ships with sql server 2005. In this blog, I will explain how to create a simple SSIS package which takes data from one table(source) which resides in database X to another table (destination) which resides in database Y. Both these databases can be in different system which can be in different locations and can also be in different domain.

You need to have proper access rights to these databases. Before creating the package, you first need to create a project that can host the package. If you have Sql server 2005 installed, Open Sql server Business Intelligence Development studio or go to Visual studio 2005 and open Integration Services project from Business Intelligence project. Specify a name for the project and click ok. Once project is created you will be able to see the package designer window of the default package called as Package.dtsx.

In solution explorer, right click the data source and click New data source. Give a data source name (for source), add the proper connection string and click ok. Repeat the same step for creating a data source for destination. Data sources are created with .ds extension. There is a connection managers section below the Package.dtsx section. Connection managers section will allow you to create connections which are needed for your package. You need to add the data sources which we have created to this section.

Right click on the connection manager and click a new connection from data source. Add the two connections which we have created in the data source. Drag and drop a data flow task from the tool box on to the control flow section. Data flow tasks helps you to move data between sources and destinations, providing the facility to transform, clean, and modify data as it is moved. Double click on data flow task --> drag and drop an oledb source and oledb destination.

Double click on oledb source, specify the connection manager ('source data source name' which we have created), data access mode (in our case its select Table or View), and the table name. Once you have selected the connection, the table dropdown will be automatically filled with all the tables in your DB. Now select the column tab and select the required columns in the table and click ok. Drag the oledb source output connector (indicated by green arrow) to the olebd destination. Open oledb destination, give the destination data source in connection manager, data access mode will be Table or View - fast load . Select the table into which the data has to be inserted. Select the mappings tab, and properly map the source columns to the destination columns... Thats it... Run the package, and if all went fine your data will be copied from the source table to destination table.