1.What is MSBI? What are the tools that MSBI comprises?

• MSBI, or Microsoft Business Intelligence, is defined as a sophisticated set of tools that provides solutions for Business Intelligence and Data Mining queries. MSBI enables users to obtain access to accurate and up-to-date information for better corporate decision-making. The majority of MSBI is made up of three tools:
  SSIS
  SSAS
  SSRS
  SQL Server Integration Services (SSIS) is primarily used to integrate data from multiple databases. This programme can handle massive volumes of data and is hence used for large transactions. SQL Server Analysis Services, or SSAS, is primarily used to examine SQL Server performance in terms of load balancing.

2.How is SSIS related to SQL Server?

• SQL Server Integration Services is a SQL Server component in MSBI. This programme can be used to execute a variety of data migration and ETL tasks.
  This platform is used for workflow and integration applications. SSIS, which is well-known for its fast and versatile OLTP and OLAP extensions for data extraction, transformation, and loading (ETL), can also be used to automate the maintenance of SQL Server databases and multidimensional datasets.

3.What are the tools associated with SSIS?

• SSIS systems offer both simple solutions (such as moving data from one area to another) and enterprise-level solutions (such as developing a large number of complicated packages in a team setting).
  • Development Studio for Business Intelligence (BIDS)
  o To design and develop new packages, as well as the data source objects and views that the packages rely on. o To alter existing packages. o To debug package functionality. o To create the deployment bundle that is used for package deployment.
  • SQL Server Import and Export Wizard o The most basic method for creating Integration Services packages. These packages may extract data from a wide range of data sources.

4.What is a workflow? What do you mean by a control flow and a data flow?

• A workflow is essentially a set of instructions communicated with the Program Executor to serve as a guideline for the execution of tasks and containers.
• A control flow is made up of one or more jobs and containers that run when a package is executed. Priority constraints, which connect the tasks and containers in a package, are used to specify conditions for performing tasks in the package control flow.
  SSIS provides three types of control flow elements:
  • Structures in packages are provided by containers
  • Tasks that add functionality
  • Precedence restrictions link the executables in an ordered control flow.
  A data flow is made up of sources and destinations that extract and load data, respectively.

5. Mention the errors in SSIS and explain how error handling works.

• In the SSIS landscape, there are three sorts of big errors:
  • Data connection error: This error happens when the Connection Manager is unable to be initialised with the connection string.
  • Data transformation error: This type of error occurs while data is being transformed through a data pipeline.
• • Expression evaluation error: This sort of error happens when the expressions evaluated at run-time are invalid.
• Errors arise when column data is altered, extracted from sources, or loaded into a destination.

6.Define environmental variables in SSIS.

• The configuration of environmental variables sets package properties equal to the value in an environment variable. These are used to configure properties that are dependent on computers that execute packages.

7.What are different lookup cache modes available in SSIS?

• The following are the different lookup cache modes in SSIS:
  • Cache mode (full) 
  • Cache mode (partial)
  • There is no cache mode.

8.Explain the architecture of SSIS.

• • Integration Services: It keeps track of running Integration Services packages and manages package storage.
  • Integration Services object model: This object model includes a controlled API for reading Integration Services tools, command-line utilities, and custom applications.
  • Integration Services runtime and executables: It maintains package layout, runs packages, and supports logging, breakpoints, configuration, connections, and transactions.
  • Dataflow Engine: This component provides in-memory buffers for moving data from one location to another.

9.How can we do logging in SSIS?

• SSIS has logging features that not only create log entries but also write custom messages when run-time events occur. Integration Services supports a large number of log providers and gives users the flexibility to establish their own. Log entries can be written to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files using Integration Services log providers. The logging configuration includes a feature that allows you to log the details of various events such as OnError, OnWarning, and so on.

10.How do you deploy SSIS packages?

• The ‘Build’ SSIS project includes a deployment manifest file. Users must run the manifest file to determine whether it should be deployed to a file system or a Microsoft SQL Server database. The SQL Server deployment is both quick and secure compared to the file system deployment. This deployment can also be done using packages from the file system or SQL Server in SSMS.

11.How can we organize SSIS packages on production?

• There are three ways to accomplish this:
  · By way of Manifest:
  o Set the deployment utility’s settings to true to create a deployment utility. It will be created in the solution’s bin folder.
  o Once the package has been produced, copy all of the files in the utility and display them on the product using the manifest file.
  • Using the DTExec.exe command-line programme
  • By logging into Integration Services and importing the package straight into a Microsoft database from SQL Server Management Studio (SSMS).

12.Define query parameters in SSRS.

• In the query of data sources that are to be included in the SQL WHERE clause for accepting parameters, query parameters are revealed. The symbol ‘@’ is used to start query parameters.

13.What are variables? Define the variable scope.

• Variables are used to store values that SSIS packages and their containers, tasks, and event handlers leverage at the run time. Scripts can also use variables. Additionally, precedence constraints that sequence tasks and containers into a workflow use variables when constraint definitions include expressions.
• Integration Services supports two types of variables: user-defined variables and system variables.

14.Define SSRS and also talk about the SSRS architecture.

• SQL Server Reporting Services (SSRS) is a server-based software solution that generates Microsoft-developed reports. It’s used to create and deliver a range of interactive printed reports, and it’s controlled via a web-based interface. It supports and develops bespoke reporting apps using a web service interface.

15.Explain the Reporting Life Cycle in SSRS.

• Visual Studio Report Designer is used to create reports. It produces a class that encapsulates the report definition.
  • Report processing: The report definition is pulled from the report data source. All calculations for grouping, sorting, and filtering are done by it.
  • Report rendering: In this stage, the Report Instance is passed to a specialised rendering extension that requires HTML or PDF formats. Every page’s header and footer parts are used to evaluate item expressions. The report is finally transformed into a specific output document.