# Microsoft SQL Server Data Source View (DSV) - Import

Availability-note[AWS](https://help.talend.com/r/en-US/Cloud/labels-on-talend-help-center/availability-aws)

AWS-only features are available at any of the following URLs: us.cloud.talend.com, eu.cloud.talend.com, ap.cloud.talend.com

## Bridge Requirements

This bridge:

* is only supported on Microsoft Windows.

## Bridge Specifications

| **Vendor** | **Microsoft** |
| --- | --- |
| **Tool Name** | **SQL Server Data Source View (DSV)** |
| **Tool Version** | **9.0 (2005) to 10.5 (2008 r2)** |
| **Tool Web Site** | http://msdn.microsoft.com/en-us/library/ms174778.aspx |
| **Supported Methodology** | [Data Modeling] Data Store (Physical Data Model), BI Design (RDBMS Source, Dimensional Target, Transformation Lineage, Expression Parsing) via DSV XML File |
| **Data Profiling** | ![](https://help.qlik.com/talend/en-US/talend-data-catalog/8.1/Content/Resources/images/cross.png) |
| **Incremental Harvesting** | ![](https://help.qlik.com/talend/en-US/talend-data-catalog/8.1/Content/Resources/images/cross.png) |
| **Multi-Model Harvesting** | ![](https://help.qlik.com/talend/en-US/talend-data-catalog/8.1/Content/Resources/images/cross.png) |
| **Remote Repository Browsing for Model Selection** | ![](https://help.qlik.com/talend/en-US/talend-data-catalog/8.1/Content/Resources/images/cross.png) |

**SPECIFICATIONS**  
**Tool:** Microsoft / SQL Server Data Source View (DSV) version 9.0 (2005) to 10.5 (2008 r2) via DSV XML File  
See http://msdn.microsoft.com/en-us/library/ms174778.aspx  
**Metadata:** [Data Modeling] Data Store (Physical Data Model), BI Design (RDBMS Source, Dimensional Target, Transformation Lineage, Expression Parsing)  
**Component:** MicrosoftSqlServerDsv version 12.0.0  
  
**OVERVIEW**  
This bridge imports an XML file with DSV or XMLA extension.  
  
**REQUIREMENTS**  
n/a  
  
**FREQUENTLY ASKED QUESTIONS**  
n/a   
  
**LIMITATIONS**  
Refer to the current general known limitations at [MIMB Known Limitations](https://help.qlik.com/talend/en-US/talend-data-catalog/8.1/Subsystems/ModelBridgeGuide/Content/MIMB-Known-Limitations.htm)  
  
**SUPPORT**  
Provide a troubleshooting package with debug log. Debug log can be set in the UI or in conf/conf.properties with MIR\_LOG\_LEVEL=6

## Bridge Parameters

| **Parameter Name** | **Description** | **Type** | **Values** | **Default** | **Scope** |
| --- | --- | --- | --- | --- | --- |
| **File** | This bridge imports an XML file with DSV or XMLA extension.<br><br><br><br>The file represents a data source view definition for Microsoft SQL Server 2005 Analysis Services.<br><br><br><br>Two options are available to retrieve such a file:<br><br><br><br>A) From SQL Server Business Intelligence Development Studio:<br><br>Each project contains one or more files with dsv extension. Such file can be selected and exported with the command: "File-&gt;Save .dsv As ..." to generate the DSV file. Or the file can be found in the project directory.<br><br><br><br>Each project contains one or more files with dsv extension. Such file can be selected and exported with the command: "File-&gt;Save .dsv As ..." to generate the DSV file. Or the file can be found in the project directory.<br><br><br><br>B) From SQL Server Management Studio:<br><br>SQL Server Management Studio can connect to an existing Analysis Services server, which contains a list of data source views. Choose a data source view to be saved and use the command:"Script database as-&gt;CREATE To -&gt; File..." to generate a file with XMLA extension. | FILE | <table><tr><td>*.dsv</td></tr><tr><td>*.xmla</td></tr></table> |  | Mandatory |
| **Create dimensional layer** | Select whether to create a dimensional layer or a purely physical layer.<br><br>'True'<br><br>A dimensional layer will be created.<br><br><br><br>'FALSE'<br><br>No dimensional layer will be created. | BOOLEAN | <table><tr><td>False</td></tr><tr><td>True</td></tr></table> | True |  |

## Bridge Mapping

| **Meta Integration Repository (MIR)<br>Metamodel<br>(based on the OMG CWM standard)** | **"Microsoft SQL Server Data Source View (DSV)"<br>Metamodel<br>MicrosoftSqlServerDsv** | **Mapping Comments** |
| --- | --- | --- |
|  |  |  |
| **AssociationRole** | **Element Content** |  |
| Multiplicity |  | Based on Source information and KeyType attribute |
| Source |  | Based on Foreign Key information |
| **AssociationRoleNameMap** | **SourceField, TargetField** |  |
| **Attribute** | **Column** |  |
| Description | msprop:Description | Mapped only if comment is empty. |
| Name | msprop:FriendlyName | Attribute Name. Computed if not set |
| Optional | minOccurs |  |
| PhysicalName | msprop:DbColumnName | Attribute Physical Name.Computed from 'name' if not set |
| **BaseType** | **SourceField, TargetField** | **BaseTypes are created to serve as parents for the simpleTypes defined in the file.** |
| **CandidateKey** | **xs:unique** |  |
| PhysicalName | name | Class Physical Name.Computed from the 'name' if not set |
| UniqueKey |  | parent key attribute information |
| **Class** | **Table** | **where msprop:TableType="Table"** |
| CppClassType |  | Set to ENTITY |
| CppPersistent |  | Set to True |
| Description | msprop:Description |  |
| Name | msprop:FriendlyName | Class Name. Computed if not set |
| PhysicalName | msprop:DbTableName | Class Physical Name.Computed from the 'name' if not set |
| **DatabaseSchema** | **Database schema** | **Inferred from Table definition** |
| **DerivedType** | **SimpleType** | **In case of an Union, only the first item is used** |
| Length | length, totalDigits, maxLength, minLength |  |
| LowerBound | maxExclusive, maxInclusive |  |
| Name | name | either explicitely in the file, or computed based on its usage |
| Scale | fractionDigits |  |
| UpperBound | maxExclusive, maxInclusive |  |
| UserDefined |  | True for simpleTypes defined in the file, False for those defined in the Schema specification |
| **DesignPackage** |  | **No equivalent in XML. A main package "Logical View" is created to contain all elements.** |
| **Dimension** | **DataTable, Named Query** | **Represents the definition of logical table views, including calculated columns** |
| Name | Name |  |
| **DimensionAttribute** | **Column, Calculated Column** |  |
| Name | Name |  |
| Position | position |  |
| **ForeignKey** | **SourceField, TargetField** |  |
| **Index** | **TargetIndex** |  |
| Name | Name |  |
| **IndexMember** | **TargetIndexField** |  |
| Position |  | position in XML file |
| **Join** | **Relationship** |  |
| Name | name |  |
| **JoinRole** | **Relationship** | **2 roles are created for each join Relationship** |
| **OlapSchema** | **Data Source View** | **An OLAPSchema is created to represent the collection of objects available in this DSV file available to Cube builders** |
| **SQLViewAttribute** | **View Column** |  |
| Description | msprop:Description | Mapped only if comment is empty. |
| DesignLevel |  | No equivalent for Logical Only objects. |
| Name | msprop:FriendlyName | Attribute Name. Computed if not set |
| PhysicalName | msprop:DbColumnName | Attribute Physical Name.Computed from 'name' if not set |
| Position |  | The attributes are ordered by position in the XML file |
| **SQLViewEntity** | **View** | **Tables withTableType=View** |
| Description | msprop:Description |  |
| DesignLevel |  | No equivalent for Logical Only objects. |
| Name | msprop:FriendlyName | Class Name. Computed if not set |
| PhysicalName | msprop:DbTableName | Class Physical Name.Computed from the 'name' if not set |
| **StoreConnection** | **Database** | **Inferred from Table definition** |
| **StoreModel** | **Schema** | **The model is built using the elements contained in the file.** |
| Name |  | Computed from the file name. |