Ans) we use ETL (Extraction Transformation Loading)  tool.

Ans) Package is a core component of SSIS. Package can be created simple graphical user interphase or programmatically.

Ans) .dtsx

Ans) It is categorized into Two component

1. a) SSIS Runtime Engine
2. b) Data flow transformation pipeline Engine

Ans) 1252(ANSI  Latin)

Ans) Carriage Return and Line Field

Ans) -1

Ans)

Read Committed

Read uncommitted

Repeatable Read

Serializable

Snapshot

Ans) Control flow of a package defines actions that are to be executed when the package run. The control flow contains various tasks and containers as well.

Ans)  Execute SQL Task,

Data flow Task,

Script Task,

File system Task

Ans)  For loop Containers

For each loop Containers

Sequence Containers

Ans) The Dataflow transformation pipeline engine completely handles the data flow of the package. The data flow contains, Data flow sources (excel source, flat file, OLEDB etc),

Data flow transformations (conditional split transformation) derived column transformation, lookup transformation etc.) and data flow destination.

Ans) It’s a logical connection between SSIS application and database or file system.

Ans)

• OLE DB Connection Manager
• ODBC Connection Manager
• Flat file connection Manager
• SMTP connection Manager
• Excel Connection Manager

Ans) Send mail task is used to send an E-mail message to the recipients via SMTP connection.

Ans)  There are FIVE permanent tabs, they are

• Control Flow
• Data  Flow
• Parameters
• Event Handler
• Package Explorer

Ans)  For each file enumerator is used to enumerate through a collection of files in the specified location.

Ans) It is Categorized into 3 parts

Changed Attribute (Type I)

Historical Attribute (Type II)

Fixed Attribute (Type III)

Ans) Logging is a feature which creates the record that traces the execution of tasks and container within the package.

Ans)

• On Error
• On Warning
• On Task Failed
• On Post Validate
• On pre-Validate

Ans) Checkpoints allows a package to restart at the point of failure.

Data conversation is used to convert the data type of a column

Ans) False

SQL Server Reporting Service

Ans) It is a developer tool for building simple and complex reports using existing report item template.

Example: Table, Columns, Rectangle Different Chart Template

1. Data Source: Logical connection between the application (SSRS) and database.
2. Report Servers: Report server is the core engine that drives Reporting services.
3. Report Manager: Report Manager is a web-based administrative interface for reporting services.

Table Report:

It is used to display data in a grid format that has a fixed number of columns and a variable number of rows

Matrix:

Matrix report item displays the aggregated data in a grid format that has a variable number of rows and variable number of columns as well.

Ans) Linked report is the report with the settings and properties of its own. A linked report can have its own name, description, location, parameter properties, report execution properties etc.

Subreport item is used to display a report within the current report.

A drill through report will always allow going from summary to detail whereas drill down reports allow you to look to the data in different levels.

Hidden

Multi-value

Internal

Allow Null values

Allow Blank value

Cascading Reports

Ans) SQL Server Analysis Services

Ans) The main usage of SSAS is to build OLAPS/online analytical processing.

Ans) Online analytical processing

There are two types of schemas in OLAP

1. Star schema.
2. Snowflake schema.

In a star schema, every dimension table related directly to a fact table.

In snowflake schema, some dimension tables are related indirectly to the fact table

The basic unit of storage and analysis in analysis services is the cube. A cube is a collection of data that is been aggregated to allow Queries to return data quickly. Cubes are ordered into Dimensions and measures comes from the fact table.

The fact table contains basic information that you want to summarize whereas Dimension Table contains hierarchical data by which like to summarize.

Multi-Dimensional expression (MDX) is a language created to allow the users to work with Multi-Dimensional data in Microsoft SQL Server Analysis Services.

Order Function

Top Count

Bottom Count

Common Operator

Colon Operator

.Dot Operator

Ans) Best Way to compare if a specific dimension is at a certain level is by using ‘IS’ operator.

Ans) You can use ALTER CUBE statement

Syntax:

ALTER CUBE current cube| your cube name UPDATE DIMENSION, “DEFAULT_MEMBER= “;