SSIS Interview Questions and Answers

Q1. What is SSIS?

Ans: SSIS was first introduced with SQL Server 2005, which was the next generation of SQL Server software after SQL Server 2000. SSIS is a form of ETL (extraction, transformation and load), which is a database term that integrates data scheduling and tasks. The SSIS engine automation many data maintenance tasks, so you can update data without manually firing procedures and imports.

Q2. What is the control flow

Ans: In SSIS a workflow is called a control-flow. A control-flow links together our modular data-flows as a series of operations in order to achieve a desired result.

A control flow consists of one or more tasks and containers that execute when the package runs.
To control order or define the conditions for running the next task or container in the package control flow, you use precedence constraints to connect the tasks and containers in a package.
A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow.
SQL Server 2005 Integration Services (SSIS) provides three different types of control flow elements: containers that provide structures in packages, tasks that provide functionality, and precedence constraints that connect the executables, containers, and tasks into an ordered control flow.

Q3. What kind of variables can you create?

Ans: 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.

Q4. What is data transformation?

Ans: Data transformation is a vague term, because you can pull data from any format and transform it to any other format. That is the goal of data transformation. In many database jobs, you’ll have data listed in a simple file such as a CSV or Excel file. Your job is to automatically pull data from this file and import it into your database tables. You can sometimes perform data updates and do “scrubbing” to the data to clean it up, because these flat files can contain raw data that needs to be better formatted. All of this can be accomplished using an SSIS package.

Q5. What is a data flow

Ans: A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations.
Before you can add a data flow to a package, the package control flow must include a Data Flow task. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package.
SQL Server 2005 Integration Services (SSIS) provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.

Q6. What kind of containers can you use with SSIS packages?

Ans: 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.

Q7. Explain What Is Connection Managers In Ssis?

Ans: While gathering data from different sources and writing it to a destination, connection managers are helpful.  Connection manager facilitates the connection to the system that include information’s like data provider information, server name, authentication mechanism, database name, etc.

Q8. How to back up or retrive the SSIS packages?

Ans: 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.

Q9. Explain What Is Ssis Breakpoint?

Ans: A breakpoint enables you to pause the execution of the package in business intelligence development studio during troubleshooting or development of an SSIS package.

Q10. How is SSIS different from DTS?

Ans: Older versions of SQL Server used DTS, which was similar to SSIS. DTS let you create steps that you would then assign a priority order. With SSIS, you can separate data from work flow, and SSIS has significantly better performance than older DTS packages. While performance isn’t always an issue when running jobs during off-peak hours, it’s a problem when you must run jobs during normal business hours. You can run SSIS during business hours without too much performance degradation.

Q11. Explain What Is Event Logging In Ssis?

Ans: In SSIS, event logging allows you to select any specific event of a task or a package to be logged. It is very helpful when you are troubleshooting your package to understand the performance package.

Q12. What is SSIS’ control flow integration?

Ans: When you create a package, you usually need some tasks to complete before you can move on to the next task. SSIS lets you control the order in which each data task is performed. This is important, because the wrong data flow can cause major issues with your data and sometimes cause severe data corruption.

Q13. Explain What Is Logging Mode Property?

Ans: 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

Q14. How to create the deployment utility?

Ans: 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:

• Right click on project and click on properites.
• Select “True” for createDeploymentUtiltiy Option. Also, you can set the deployment path.
• Now close the window after making the changes and build the project by right clicking on the project.
• A deployment folder will be created in BIN folder of you main project location.
• Inside the deployment folder, you will find .manifest file, double clicking on it you can get options to deploy package on SQL Server.
• Log in to SQL Server and check in MSDB on Integration Services.

Q15. Explain What Is A Data Flow Buffer?

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

Q16. What can you do in an SSIS task?

Ans: A task is one step in your SSIS job. A task can be almost database transformation step. It can be connection to another database, importing data from a file or database table or running a stored procedure against your database tables. You can also customize tasks with the Microsoft .NET language, which makes SSIS a very powerful tool with your database.

Q17. For What Data Checkpoint Data Is Not Saved?

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

Q18. How do you do error handling in SSIS

Ans: When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.
For example, a data conversion fails because a column contains a string instead of a number, an insertion into a database column fails because the data is a date and the column has a numeric data type, or an expression fails to evaluate because a column value is zero, resulting in a mathematical operation that is not valid.
Errors typically fall into one the following categories:
1) Data conversion errors, which occur if a conversion results in loss of significant digits, the loss of insignificant digits, and the truncation of strings. Data conversion errors also occur if the requested conversion is not supported.
2) Expression evaluation errors, which occur if expressions that are evaluated at run time perform invalid operations or become syntactically incorrect because of missing or incorrect data values.
3) Lookup errors, which occur if a lookup operation fails to locate a match in the lookup table. Many data flow components support error outputs, which let you control how the component handles row-level errors in both incoming and outgoing data. You specify how the component behaves when truncation or an error occurs by setting options on individual columns in the input or output.

For example, you can specify that the component should fail if customer name data is truncated, but ignore errors on another column that contains less important data.

Q19.What is File system deployment?

Ans: 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.

Q20. Explain What Is Conditional Split Transactions In Ssis?

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

Q21. How do you do logging in SSIS

Ans: SSIS includes logging features that write log entries when run-time events occur and can also write custom messages.

Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.
To customize the logging of an event or custom message, Integration Services provides a schema of commonly logged information to include in log entries. The Integration Services log schema defines the information that you can log. You can select elements from the log schema for each log entry.
To enable logging in a package:
1). In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.
2). On the SSIS menu, click Logging.
3). Select a log provider in the Provider type list, and then click Add.

Q22. List Out The Different Types Of Data Viewers In Ssis?

Ans: Different types of data viewers in SSIS include:

• Grid
• Histogram
• Scatter Plot
• Column Chart

Q23. How do you deploy SSIS packages

Ans: Integration Services (SSIS) makes it simple to deploy packages to any computer.
There are two steps in the package deployment process:
1. The first step is to build the Integration Services project to create a package deployment utility.
2. The second step is to copy the deployment folder that was created when you built the Integration Services project to the target computer, and then run the Package Installation Wizard to install the packages.

Q25. Mention What Are The Possible Locations To Save Ssis Package?

Ans: You can save SSIS package at

• SQL Server
• Package Store
• File System

Q26. What is Manifest file in SSIS?

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