Microsoft SQL Server Integration Services (SSIS) (via DSV File) - Import - Cloud

Talend Cloud Data Catalog Bridges

author
Talend Documentation Team
EnrichVersion
Cloud
EnrichProdName
Talend Cloud
EnrichPlatform
Talend Data Catalog

Note: This bridge is not available in Talend Cloud Data Catalog by default. You need to set up a remote harvesting agent to import metadata from this source. For more information, see Deploying a remote harvesting agent in Talend Cloud Data Catalog Administration guide.

Bridge Requirements

This bridge:
  • is only supported on Microsoft Windows.

Bridge Specifications

Vendor Microsoft
Tool Name SQL Server Integration Services (SSIS)
Tool Version 9.0 (2005) to 14.x (2017)
Tool Web Site http://msdn.microsoft.com/en-us/library/ms174778.aspx
Supported Methodology [Data Integration] BI Design (RDBMS Source, Dimensional Target) via DSV XML File
Data Profiling
Remote Repository Browsing for Model Selection
Multi-Model Harvesting
Incremental Harvesting

SPECIFICATIONS
Tool: Microsoft / SQL Server Integration Services (SSIS) version 9.0 (2005) to 14.x (2017) via DSV XML File
See http://msdn.microsoft.com/en-us/library/ms174778.aspx
Metadata: [Data Integration] BI Design (RDBMS Source, Dimensional Target)
Component: MicrosoftSqlServerDsvImport.MicrosoftSqlServerIntegrationServices version 11.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 http://metaintegration.com/Products/MIMB/MIMBKnownLimitations.html or bundled in Documentation/ReadMe/MIMBKnownLimitations.html

SUPPORT
Provide a trouble shooting 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.

The file represents a data source view definition for Microsoft SQL Server 2005 Analysis Services.

Two options are available to retrieve such a file:

A) From SQL Server Business Intelligence Development Studio:
Each project contains one or more files with dsv extension.Such file can be selected and exported with the command: "File->Save .dsv As ..." to generate the DSV file. Or the file can be found in the project directory.

Each project contains one or more files with dsv extension.Such file can be selected and exported with the command: "File->Save .dsv As ..." to generate the DSV file. Or the file can be found in the project directory.

B) From SQL Server Management Studio:
SQL Server Management Studio can connect to an existingAnalysis 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->CREATE To -> File..." to generate a file with xmla extension.
FILE
*.dsv
*.xmla
  Mandatory
Create dimensional layer Select whether to create a dimensional layer or a purely physical layer.
'True'
A dimensional layer will be created.

'FALSE'
No dimensional layer will be created.
BOOLEAN   True  

 

Bridge Mapping

Meta Integration Repository (MIR)
Metamodel
(based on the OMG CWM standard)
"Microsoft SQL Server Integration Services (SSIS) (via DSV File)"
Metamodel
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.