SSIS Interview Questions

1- What is SSIS?

SSIS was first introduced with SQL Server 2005, which was the next generation of SQL Server software after SQL Server 2000.  SSIS is an ETL tool that handles data Extraction, Transformation and load. In addition, it can handles more tasks besides the ETL tasks such as data profiling, file system manipulation etc....  If you know C#.net or VB.net, you can use the script task to perform much more efficient batch operations.

2- What is SSIS control flow integration?

SSIS control flow allow you to program graphically how the tasks will run by using the logical connectors between tasks.  there are three basic logical connectors that you can use: success, failure, or complete.  Further more, you can use the FX (expression) to handle more complex conditions in your control flow.

3- What is Data Transformation?

The data transformation stage applies a series of rules or functions to the extracted data from the source to derive the data for loading into the end target.

4- What can you do in an SSIS task?

SSIS tasks can be broken up into different categories below:

a- Data Preparation Tasks: for example, move file to different locations, validate data, and analyze data.

b- Process Communication Tasks: for example, Interact with services, processes, or other Integration Services packages.

c- SQL Server Tasks: for example, Execute bulk insert, SQL Statements, transfers of database objects. 

d- Database Maintenance Tasks: for example, Perform backups, maintain indexes, and other miscellaneous tasks.

5- What kind of variables can you create?

You can create global variables and task level variables in SSIS. For programmers, these variables are the same as global and function level variables. A global variable is available to all tasks across the entire job. Variables created in tasks are only available within that task.

6- What kind of containers can you use with SSIS packages?

There are three types of containers: sequence, for loops and for each loops.

A sequence container is a simple way to group similar taks together. Think of a sequence container as an organization container for more complex SSIS packages.

A for loop container is what you can use to execute your tasks to a certain number of times. For example, you need to update records ten times, you can place the task that update the records inside this for loop container and specify 10 as the end of the loops.  by using the for loop container, you don't have to create neither ten different packages to do the same task, or have to run the whole package ten times when you schedule your job.

A for each loop container will be useful when you don't know a head of time how many times a task should perform. for instance, let's say that you want to delete all the files inside a folder, but you don't know how many files are there at any particular time, by using for each loop, it can go through the collection of files and delete them for you, after the collection is emptied out, it knows that when it should stop.

7- How to create the deployment utility?

Deployment is the process in which packages converts  from development mode into executables mode. For deploying the SSIS package, you can directly deploy the package by right clicking Integration Services project and build it.  This will save the package.dtsx file on the project\bin folder.  Also, you can create the deployment utility using which the package can be deployed at either SQL Server or as a file on any location.

For creating deployment utility, follow these steps:

1. Right click on project and click on properites. 

2. Select "True" for createDeploymentUtiltiy Option.  Also, you can set the deployment path.

3. Now close the window after making the changes and build the project by right clicking on the project.

4. A deployment folder will be created in BIN folder of you main project location. 

5. Inside the deployment folder, you will find .manifest file, double clicking on it you can get options to deploy package on SQL Server.

6. Log in to SQL Server and check in MSDB on Integration Services.

8- What is Manifest file in SSIS?

Manifiest file is the utility which can be used to deploy the package using wizard on file system and SQL Server database.

9- What is File system deployment?

File system deployment means to save pacakge file on local or network drive. Then you can use SQL Agent job to schedule when the packages will run. 

10- How to back up or retrive the SSIS packages?

If your package is deployed on SQL Server then you can back up the MSDB database as all the package on SQL server deploys at MSDB.

11- What is data flow task in SSIS?

Data flow task is the pumping mechanism that moves data from source to destination. However, in the case of SSIS, you have much more control of what happens from start to finish.  In fact, you have a set of out of the box transformation components that you snap together to clean and manipulate the data while it is in the data pipeline.  Just as the control flow handles the main workflow of the package, the Data Flow handles the transformation of data. Almost anything that manipulates data falls into the Data Flow Category. 

12- What is data Profiling task?

Data profiling is the process of analyzing the source data to better understand what condition the data is in, in terms of cleanliness, patterns, numbers or nulls, and so on. data profiling task usually be used at the beggining of the development cycle to support the design of the destination database schema. Note that this task is not used when you develop the normal recurring ETL packages.

13- What is the multicast Transformation in SSIS?

The Multicast transform, as the name implies, can send single data input to multiple output paths easily. You may want to use this transformation to send a path to multiple destinations sliced in different ways.  The multicast transformation is similar to the Split Transformation because both send data to multiple outputs. However, you can not specify the conditions for which part of the data will be in which output in the Multicast transformation.

14- Differnce between Merge and Union All?

The Merge transformation can merge data from two paths into a single output.  The Transform is usefull when you wish to break out your Data Flow into a path that handles certain errors and then merge it back into the main Data Flow downstream after the errors have been handled.  it's also useful if you wish to merge data from two Data Sources.

Note that the data must be sorted before using the Merge Transformation. you can do this by using the sort transformation prior to the merge or by specifying an ORDER By clause in the source connection.  Also, the metadata must be the same for both paths. For example, CustomerID column cannot be a numeric column in one path and a character column in the other path.

The Union All Transformation works much the same way as the Merge Transformation, but it does not require the data to be sorted. It takes the outputs from multiple sources or transforms and combines them into a single result set.

15- OLE DB Command Transform?

The OLE DB Command Transform is a component designed to execute a SQL Statement for each row in an input stream. This task is analogous to an ADO Command Object being created, prepared, and executed for each row of a result set. The input stream provides that data for parameters that can be set into the SQL Statement that is either an In - line statement or a stored procedure call.

16 - Execute package task?

The Execute Package Task enables you to build SSIS solutions called parent packages that execute other packages called "Child Packages".  You'll find this capability an indispensable part of your SSIS development as your packages begin to grow.

Separating packages into discrete functional workflows makes for shorter development and testing cycles and facilitates best development practices. in SSIS, the child package is aware of the parenment package parameters and can reach out and access those parameters - level configuration values.  The majority of the configuratble properties are in the Package tab of the Executable package Task Editor.

The first option is to provide the location of the child package.  The options here are either File system and SQL Server.  You can deploy SSIS package in the FIle system task as an .dtsx file, or within the msdb database of a SQL Server instance.  if you select file system, you must first create a new connection manager connection to the child package. if the child package is located in a SQL Server, you'll need to provide the OLE DB Connection Manager for the SQL Server that holds your packages.  In either case, browse to and then select the child package within the connection to set the package to execute in the task.

17 - What is transaction in SSIS package and how to implement it?

Packages use transactions to bind the database actions that tasks perform into atomic units. and by doing this maintain data integrity.  Al MS IS container types - packages the For loop, For each loop, and Sequence containers, and the task hosts that encapsulate each task can be configured to use transactions. IS provides three options for configuring transactions: Not supported, Supported, and Required.

Require indicates that the container start a transaction, unless one is already started by its parent container. if a transaction already exists, the containter joins the transaction For example, if a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence Container would start its own transaction. If the package were configured to use the Required option, the Sequence containter would join the package transaction. 

Supported indicates that the container does not start a transaction, but joins any transaction started by its parent container. For example, if a package with four Executable SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any taks fails.  if the package does not start a transaction, the four execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back

Not Supported indicates that the container does not start a transaction or join an existing transaction. A transaction started by a parent container does not affect child containers that have been configured to Not Support transactions. For instance, if a package is configured to start a transaction and a For Loop Container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.

18 - Difference between Execute TSQL Task and Execute SQL Task

In SSIS there is one tasks Execute TSQL task which is similar to Execute SQL task. Will see what is the difference between two.

Execute TSQL Task:  

Pros: Takes less memory, faster perfomance

Cons: Output into variable not supported, Only supports ADO.net connection

Execute SQL Task:

Pros: Support output into variables and multiple types of connection, parameterized query possible.

Cons: Takes more memory, slower performance compared to TSQL task.

19- Precedence Constraints

A task will only execute if the condition that is set by the precedence constraint preceding the task is met.  By using these constraints, it will choose different execution paths depending on the success or failure of other tasks.

Success - Workflow will proceed when the preceding containter executes successfully. Indicated in control flow by a solid green line.

Failure - Workflow will proceed when the preceding container's execution results in a failure. Indicated in control flow by a solid red line.

Completion - Workflow will proceed when the preceding container's execution completes, regardless of success or failure. Indicated in control flow by a solid blue line.

Expression/Constraint with logical AND - workflow will proceed when specified expression and constraints evaluate to true. Indicated in control flow by a solid color line along with a small 'fx' icon next to it.  Color of line depends on logical constraint chosen (e.g. success = green, completion = blue).  

20- What is the use of config file in SSIS?

Config file in SSIS is used to provide inputs to connection manager different properites which package use to assign values at run time dynamically.

Using config file user need to make changes to the config file which package will take automatically at run time because of using it you don't need to every time make changes in the packages in case you are deploying package on multiple servers or locations. There are multiple ways in which configuration values can be stored.

XML configuration file: Store the config file as an XML file..

Environment variable Store the config in on of the environment variables.

Registry entry Store the confi in the registry

Parent package variable Store the config as a variable in the package that contains the tasks.

SQL Server Store the config in t a table in SQL Server

21- Different between Control Flow and Data Flow?

a. Control flow is for designing the flow of the package. Data flow is for ETL process.

b. Data Flow is the subset of control flow

c. There will be only one control flow while multiple dataflow can exists.

d. Data flow cannot work without a control flow

All process based tasks are part of control flow while ETL related tasks are the part of Dataflow which is again a subset of control flow.

22 - Check point?

Checkpoint is the property in SSIS which enables the project to restart from the point of failure. When we set the property to true package create the checkpoin file which stores the information about package execution and use to restart package from the point of failure.  If the package runs successfully, the checkpoint file is deleted, and then re-created the next time the package runs.

How to Find Percentage of Null Values in every Column of all the Tables in SQL Server Database

Use <>

GO

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)

--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,TotalTableRowCount INT
    ,NullRecordCount INT
    )

DECLARE Cur CURSOR
FOR
SELECT C.Table_CataLog
    ,C.Table_Schema
    ,C.Table_Name
    ,C.Column_Name
    ,'[' + C.Table_CataLog + ']' + '.[' + C.Table_Schema + '].' 
    + '[' + C.Table_Name + ']' AS FullQualifiedTableName
FROM information_schema.Columns C
INNER JOIN information_Schema.Tables T ON C.Table_Name = T.Table_Name
    AND T.Table_Type = 'BASE TABLE'
    and C.IS_Nullable='YES'


OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(MAX) = NULL

    SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' 
    + @SchemaName + ''' AS TableName,
      ''' + @TableName + ''' AS SchemaName,
      ''' + @ColumnName + ''' AS ColumnName,
      (Select count(*) from ' + @FullyQualifiedTableName + ' with (nolock)) 
      AS TotalTableRowCount,
      count(*) as NullRecordCount from ' + @FullyQualifiedTableName 
      + 'with (nolock) Where ' + @ColumnName + ' IS NULL' 

    --Print @SQL
    INSERT INTO #Results
    EXEC (@SQL)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
END

CLOSE Cur

DEALLOCATE Cur


SELECT *,
Cast((NullRecordCount/Cast(TotalTableRowCount as Numeric(13,1)))*100 AS Numeric(4,1))

 AS NullPercentColumnValues
FROM #Results
where NullRecordCount <>0
--drop table #Results

SSIS - Tuning SSIS Data Flow

Dear All,

I have found couple of points to improve data flow task level.I would like to describe below:

Data Flow Performance:

I do consider tuning at ETL( Extract, Transform and Load) sections.

Extraction Level:-

Network Tuning: 

1. Change the network packet size in the connection manager

•       Higher values typically yield fast through put
•       By Default, it will be ZERO, you can change MAX value as: 32767

2. Try to experiment with shared memory vs TCP/IP

3. Enable JUMBO frames on network. To do this please consult your network specialists

4. If you have OLEDB Command statements, then try to create another connection manager with     low package size.( Not go with 32767 max value )

SQL Queries:

1. Consider using NOLOCK hint on table in your source query. 

•     Removes locking overhead
•     Improves the speed of large tables scan
•     Risky side effects
•     Understand before using hints in the source query

2. SELECT query with only selected columns

3.Do require conversions in the source query, don't go with Data Conversion T/F later.

LookUp:-

1.Changes SELECT statement to only use the columns you need. So that, it optimizes memory        usage

2.Consider adding NOLOCK hint.

3.In SSIS 2008, use shared lookup cache. Create cache transform and cache connection manager

4.Use SQL query for reference table

5.Use WHERE condition in SQL query in LookUp T/F.

6.When your input row count is large then go for using partial cache or full cache lookup to improve performance.

7.No cache lookup is a row based operation

8.A partial cache builds the cache as the lookup T/F is executing and also comes with high transaction impact. A partial cache approach is viable solution if you have large number of input rows.

9.However, best option is use "Full Cache" of lookup and filter the reference table.

10. Apply an 80/20 rule and load one lookup T/F with 20 percent of the most common matching records, which will allow 80 percent matches in the full cache.

Transform Level:-

Different transformations:

1.Row based(synchronous)

•    Logically works row by row
•    Data conversion,Derived column examples
•    Buffer reused

2.Partial blocking(Asynchronous)

•     Works with groups of rows
•     Merge,Merge Join,Union All,LookUp examples
•     Data copied to new buffers

3. Blocking( Asynchronous)

•     Need all input rows before producing any output rows
•     Aggregate,Sort,Pivot&UnPivot examples
•     Data copied to new buffers

Data Types:

1.Make data types as narrow as possible so you will allocate less memory for your transformation

2.Do not perform excessive casting of data types

•   It will degrade performance
•   Cast source types at the database using CAST/CONVERT functions where ever possible

Push Up or Down where ever possible:

1.Data flow transformations can often be optimized

• Sort: Push to source queries when possible, instead of using SORT transformation for sorting cross database joins.
• Use IsSorted and SortKeyPosition option to sort data fro OLEDB source data instead of going with SORT transformation.
• Go with MERGE sql statement instead of SCD and LookUp T/F's for SCD's data handling.
• Use GROUP BY in sql source query instead of Aggregation T/F.
• Use INSERT INTO statement instead of a data flow task on a single instance
• Find about DELTA load VS RELOAD data

Loading Level:-

1. Use SQL Server destination 

•  only when package and server are on the same server
•  Error handling weaker than OLEDB Destination

2.Commit size = 0, then fast

3.Drop some indexes based on load growth %

4.Load data in to partitons tables

5. Truncate table instead of DELETE statement

Other options to consider:

1.BLOB Temp Storage Path: Binary Large Object 
2.Buffer Temp Storage Path:  change the Buffer Temp Storage Path and BLOB Temp Storage Path to drive locations that have been optimized and ensure they are not using the C:/ system drive.
3.Default Buffer Max Rows - 10,000 means no single buffer can have more than 10,000 rows
4.Default Buffer Size - 10,48,5760 Specify number of bytes that a single buffer cannot exceed
If single row is 1200 bytes the max rows(10,000) times row width(1200)
10,000 * 1200 ==> 12000000 which is greater than default buffer size
5.Engine Threads - 20
6.Run in Optimized Mode - True - Ignores unused source columns,destination columns and any T/F's.

SQL Server - Logical Query Processing Phases

The main statement used to retrieve data in T-SQL is the SELECT statement. Following are
the main query clauses specified in the order that you are supposed to type them (known as
“keyed-in order”):
1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY
But as mentioned, the logical query processing order, which is the conceptual interpretation
order, is different. It starts with the FROM clause. Here is the logical query processing
order of the six main query clauses:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
1.What is the difference between the WHERE and HAVING clauses?
The WHERE clause is evaluated before rows are grouped, and therefore is evaluated
per row. The HAVING clause is evaluated after rows are grouped, and therefore
is evaluated per group.

2. Why are you not allowed to refer to a column alias defined by the SELECT
clause in the WHERE clause?
Because the WHERE clause is logically evaluated in a phase earlier to the one
that evaluates the SELECT clause.

3. Why are you not allowed to refer to a column alias defined by the SELECT
clause in the same SELECT clause?
Because all expressions that appear in the same logical query processing phase
are evaluated conceptually at the same point in time.

Difference between CTE and Temp Table and Table Variable

mp Table or Table variable or CTE are commonly used for storing data temporarily in SQL Server. In this article, you will learn the differences among these three.

CTE

CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

A sub query without CTE is given below :

SELECT * FROM (
 SELECT Addr.Address, Emp.Name, Emp.Age From Address Addr
 Inner join Employee Emp on Emp.EID = Addr.EID) Temp
WHERE Temp.Age > 50
ORDER BY Temp.NAME

By using CTE above query can be re-written as follows :

;With CTE1(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
)
SELECT * FROM CTE1 --Using CTE 
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME

When to use CTE

1. This is used to store result of a complex sub query for further use.
2. This is also used to create a recursive query.

Temporary Tables

In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

1. Local Temp Table

   Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

   
   CREATE TABLE #LocalTemp
   (
    UserID int,
    Name varchar(50), 
    Address varchar(150)
   )
   GO
   insert into #LocalTemp values ( 1, 'Shailendra','Noida');
   GO
   Select * from #LocalTemp
   
   

   
   The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.

2. Global Temp Table

   Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

   
   CREATE TABLE ##GlobalTemp
   (
    UserID int,
    Name varchar(50), 
    Address varchar(150)
   )
   GO
   insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');
   GO
   Select * from ##GlobalTemp
    
   
   

   
   Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Table Variable

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

 GO
 DECLARE @TProduct TABLE
 (
 SNo INT IDENTITY(1,1),
 ProductID INT,
 Qty INT
 ) 
 --Insert data to Table variable @Product 
 INSERT INTO @TProduct(ProductID,Qty)
 SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 
 --Select data
 Select * from @TProduct
 
 --Next batch
 GO
 Select * from @TProduct --gives error in next batch
 

Note

1. Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
2. CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.
3. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.