What is MSBI? What are the tools that MSBI comprises?
As per definition, MSBI or Microsoft Business Intelligence is a powerful suite of tools that provide solutions for Business Intelligence and Data Mining Queries. MSBI empowers users to gain access to accurate and up-to-date information for better decision-making at an organizational level. There are three tools that constitute the bulk of MSBI:
| SSIS | SSAS | SSRS |
| SQL Server Integration Services or SSIS is basically leveraged for integrating data from multiple databases together. This tool can deal with huge amounts of data and is therefore used for large transactions. | SQL Server Analysis Services or SSAS is mostly used to analyze the performance of SQL Server when it comes to loading balancing, heavy data, and transactions, among other uses. This tool is mostly related to the administration of SQL Server. | SQL Server Reporting Services or SSRS pertains to report generation. Being platform-independent, this tool can be used across numerous applications and is therefore quite popular in the market these days. |
How is SSIS related to SQL Server?
SQL Server Integration Services is a component of SQL Server in MSBI. This tool can be used to perform numerous data migration and ETL operations.
This platform is used for integration and workflow applications. Known for its quick and flexible OLTP and OLAP extensions for data extraction transformation, and loading (ETL), SSIS can also be used to automate the maintenance of SQL Server databases and multidimensional datasets.
Mention the errors in SSIS and explain how error handling works.
There are three types of major errors in the SSIS landscape, namely:
- Data connection error: This type of error occurs when the Connection Manager cannot be initialized with the connection string.
- Data transformation error: This error occurs while the data is being transformed over a data pipeline.
- Expression evaluation error: This type of error occurs if the run-time evaluated expressions are invalid.
When the column data is transformed, extracted from sources, or loaded into a destination, errors occur.
Procedure errors are handled in the control flow by the precedence control and by redirecting the execution flow, and data errors are handled in the data flow task by redirecting the data flow using the error output of a component.
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.
What are different lookup cache modes available in SSIS?
Different lookup cache modes in SSIS are as follows:
- Full cache mode
- Partial cache mode
- No cache mode
How can we do logging in SSIS?
SSIS includes logging features that not only write log entries when run-time events occur but also write custom messages. Integration Services supports a diverse set of log providers and empowers users with the ability to create custom log providers. Integration Services log providers can write log entries into text files, SQL Server Profiler, SQL Server Windows Event Log, or XML files. The logging configuration provides an in-built feature that can log the details of various events like OnError, Onwarning, etc.
How do you deploy SSIS packages?
The SSIS project, ‘Build’ offers a deployment manifest file. Users are required to run the manifest file and decide whether to deploy it onto a file system or onto a Microsoft SQL Server database. The SQL Server deployment is fast and is also relatively secure than the file system deployment. Alternatively, this deployment can be done from packages in SSMS from the file system or SQL Server.
How can we organize SSIS packages on production?
There are three ways to do so:
- Through Manifest:
- Create a deployment utility by setting its properties as true. It will be formed in the bin folder of the solution.
- Once the package is built, copy all the files in the utility and use the manifest file to display it on the product.
- Using the DTExec.exe utility
- By importing the package directly into the Microsoft database from SQL Server Management Studio (SSMS) by logging into Integration Services.
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.
When shall we use a null data-driven subscription?
Users should first create a data-driven subscription that uses the Null Delivery Provider. When the Null Delivery Provider is specified as the method of delivery in the subscription, the Report Server targets the Report Server Database as the delivery destination and uses a specialized rendering extension called the null rendering extension. Different from the other delivery extensions, the Null Delivery Provider does not have delivery settings that can be configured through a subscription.
