SSIS Interview Questions and Answers Pdf

1. How to log SSIS Executions?
Answer: SSIS includes logging features that write log entries when run-time events occur and can also write custom messages. This is not enabled by default. 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.

2. What are variables and what is variable scope?
Answer: Variables store values that an SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions. Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.

3. What are the tools associated with SSIS?
Answer: Workflow is a set of instructions on to specify the Program Executor on how to execute tasks and containers within SSIS Packages.

4. What is a workflow in SSIS?
Answer: Workflow is a set of instructions on to specify the Program Executor on how to execute tasks and containers within SSIS Packages.

5. What is SSIS? How it related with SQL Server? Answer:
SQL Server Integration Services (SSIS) is a component of SQL Server which can be used to perform a wide range of Data Migration and ETL operations. SSIS is a component in MSBI process of SQL Server. This is a platform for Integration and Workflow applications. It is known for a fast and flexible OLTP and OLAP extensions used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and multidimensional data sets. (E learning)

6. How do you do incremental load?
Answer: the Fastest Way to do incremental load is by using Timestamp column in the source table and then storing last ETL timestamp, In ETL process pick all the rows having Timestamp greater than the stored Timestamp so as to pick only new and updated records.

7. Difference between asynchronous and synchronous transformations?
Answer: Asynchronous transformation have different Input and Output buffers and it is up to the component designer in an A sync component to provide a column structure to the output buffer and hook up the data from the input.

8. Where is SSIS package stored in the SQL Server?
Answer: sysdtspackages90 stores the actual content and ssydtscategories, sysdtslog90, sysdtspackagefolders90, sysdtspackagelog, sysdtssteplog, and sysdtstasklog do the supporting roles.

9. What is the Execution Tree?
Answer: Execution trees demonstrate how package uses buffers and threads. At run time, the data flow engine breaks down Data Flow task operations into execution trees. These execution trees specify how buffers and threads are allocated in the package. Each tree creates a new buffer and may execute on a different thread. When a new buffer is created such as when a partially blocking or blocking transformation is added to the pipeline, additional memory is required to handle the data transformation and each new tree may also give you an additional worker thread.

10. How to pass property value at Run time? How do you implement a Package Configuration?
Answer:  A property value like connection string for a Connection Manager can be passed to the pkg using package configurations. Package Configuration provides different options like XML File, Environment Variables, SQL Server Table, Registry Value or Parent package variable.

11. Explain the architecture of SSIS?
Answer: a.Integration Services service: monitors running Integration Services packages and manages the storage of packages.
b.Integration Services object model: includes managed API for accessing Integration Services tools, command-line utilities, and custom applications.
c.Integration Services runtime and run-time executable: it saves the layout of packages, runs packages, and provides support for logging, breakpoints, configuration, connections, and transactions. The Integration Services run-time executable is the package, containers, tasks, and event handlers that Integration Services includes, and custom tasks.
d.Data flow engine: provides the in-memory buffers that move data from source to destination.

12. How Does Ssis Differ From Dts?
Answer: 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/workflow from the data flow and the ability to use a buffer/memory oriented architecture for data flows and transformations which improve performance.

13. What Is A Task?
Answer: 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.

14. How Is Ssis Runtime Engine Different From The Ssis Dataflow Pipeline Engine?
Answer: The SSIS Runtime Engine manages the workflow of the packages during runtime, which means its role is to execute the tasks in a defined sequence. As you know, you can define the sequence using precedence constraints. This engine is also responsible for providing support for event logging, breakpoints in the BIDS designer, package configuration, transactions, and connections. The SSIS Runtime engine has been designed to support concurrent/parallel execution of tasks in the package.

15. What Is An Ssis Proxy Account And Why Would You Create It?
Answer: When we try to execute an SSIS package from a SQL Server Agent Job it fails with the message “Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account”. This error message is generated if the account under which SQL Server Agent Service is running and the job owner is not a sysadmin on the instance or the job step is not set to run under a proxy account associated with the SSIS subsystem. Refer to this tip to learn more about it.