SSIS Interview Questions - Part 2

Q: What is the function of Event handlers tab in SSIS?
On the Event handlers tab, workflows can be configured to respond to package events.
For example, we can configure Work Flow when any task fails, stops or starts ..

Q: What is the function of Package explorer tab in SSIS?
This tab provides an explorer view of the package. You can see what is happening in the package. The Package is a container at the top of the hierarchy.

Q:  Can you name 5 or more of the native SSIS connection managers?

• OLE DB connection – Used to connect to any data source requiring an OLE DB connection (i.e.,SQL Server 2000). OLE DB Connection Manager Example
• Flat file connection – Used to make a connection to a single file in the File System. Required for reading information from a File System flat file. Tutorial: Import Flat File to SQL Server
• ADO.Net connection – Uses the .Net Provider to make a connection to SQL Server 2005 or other connection exposed through managed code (like C#) in a custom task
• Analysis Services connection – Used to make a connection to an Analysis Services database or project. Required for the Analysis Services DDL Task and Analysis Services Processing Task
• File connection – Used to reference a file or folder. The options are to either use or create a file or folder
• Excel

Q: What are the different types of data sources available in SSIS?

• ADO NET Source
• Data Reader Source
• Excel Source
• Flat file Source: For Example, refer Step 3 in the tutorial
• ODBC Source
• OLE DB Source: For Example, refer Step 5 in the tutroial
• Raw File Source
• XML Source

Q: What are the different types of data destinations available in SSIS?

• ADO NET Destination
• Data Reader Destination
• Excel Destination
• Flat File Destination: For Example, refer Step 7 in the tutorial
• ODBC Destination
• OLE DB Destination
• Raw File Destination
• Recordset Destination
• SQL Server Destination: For Example, refer Step 4 in the tutorial

Q: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).

Q: What are the possible locations to save SSIS package?
You can save a package wherever you want.

• SQL Server
• Package Store
• File System

Q: How to provide security to packages?
We can provide security in two ways

• Package encryption
• Password protection.

Q:  How do we convert data type in SSIS?
The Data Conversion Transformation in SSIS converts the data type of an input column to a different data type.
Refer this tutorial where Data Conversion Transformation is used in a Real Time Example
Import Flat File to SQL Server

Q: What is the use of Bulk Insert Task in SSIS?
Bulk Insert Task is used to upload large amount of data from flat files into Sql Server. It supports only OLE DB connections for destination database.

Q: How to quickly load data into sql server table?
Fast Load option. This option is not set by default so most developers know this answer as otherwise the load is very slow.

Q: What are the Transformations available in SSIS?

• Aggregate: It applies aggregate functions to Record Sets to produce new output records from aggregated values. Example
• Conditional Split:  Separates available input into separate output pipelines based on Boolean Expressions configured for each output. Example
• Data Conversion: Converts columns data types from one to another type. It stands for Explicit Column Conversion. Example
• Derived Column:  Create a new (computed) column from given expressions. Example
• Lookup: Performs the lookup (searching) of a given reference object set against a data source. It is used for exact matches only.
• Merge: Merges two sorted data sets into a single data set into a single data flow.
• Merge Join: Merges two data sets into a single data-set using a join junction.
• Multicast: Sends a copy of supplied Data Source onto multiple Destinations.
• Row Count: Stores the resulting row count from the data flow / transformation into a variable.
• Union All: Merge multiple data sets into a single data-set.
• Audit: Adds Package and Task level Metadata - such as Machine Name, Execution Instance, Package Name, Package ID, etc..
• Character Map: Performs SQL Server column level string operations such as changing data from lower case to upper case.
• Copy Column: Add a copy of column to the output we can later transform the copy keeping the original for auditing.
• Data Mining Query: Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.
• Export Column: Used to export a Image specific column from the database to a flat file.
• Fuzzy Grouping: Used for data cleansing by finding rows that are likely duplicates.
• Fuzzy Lookup: Used for Pattern Matching and Ranking based on fuzzy logic.
• Import Column: Reads image specific column from database onto a flat file.
• Row Sampling: Captures sample data by using a row count of the total rows in data-flow specified by rows or percentage.
• Pivot: Used for Normalization of data sources to reduce anomalies by converting rows into columns
• Unpivot: Used for de-normalize the data structure by converts columns into rows in case of building Data Warehouses.

<< Prev Next >>