Microsoft SQL Server Database (via JDBC) - Import - 7.1

Talend Data Catalog Bridges

author
Talend Documentation Team
EnrichVersion
7.1
EnrichProdName
Talend Big Data Platform
Talend Data Fabric
Talend Data Management Platform
Talend Data Services Platform
Talend MDM Platform
Talend Real-Time Big Data Platform
EnrichPlatform
Talend Data Catalog

Bridge Specifications

Vendor Microsoft
Tool Name SQL Server Database
Tool Version 7.0 to 14.x (2017)
Tool Web Site http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx
Supported Methodology [Relational Database] Multi-Model, Data Store (Physical Data Model, Stored Procedure Expression Parsing) via JDBC API
Multi-Model Harvesting
Incremental Harvesting
Data Profiling
Remote Repository Browsing for Model Selection

SPECIFICATIONS
Tool: Microsoft SQL Server Database version 7.0 to 14.x (2017) via JDBC API
See http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx
Metadata: [Relational Database] Multi-Model, Data Store (Physical Data Model, Stored Procedure Expression Parsing)
Bridge: JdbcMicrosoftSqlServer version 11.0.0

OVERVIEW
This bridge uses a JDBC connection to a MICROSOFT SQL SERVER database server to extract all its metadata (e.g. Schemas, Tables, Columns, Data Types.) and parse any SQL (e.g. Views, Stored Procedures)to produce the data flow lineage.
This bridge imports the database server metadata as multi models with one model per schema. Further imports (incremental harvesting) will detect changes in schema to only import changed schemas (reusing already harvested unchanged schemas).

REQUIREMENTS
Driver details:
- driver name: Microsoft JDBC Driver 7.4 for SQL Server for JVM 64-bits (com.microsoft.sqlserver.jdbc.SQLServerDriver)
- driver version: 4.5
- file: mssql-jdbc-7.4.1.jre11.jar (1,219,373 bytes)
- file location: ${MODEL_BRIDGE_HOME}/java/Jdbc/mssql

Permissions:
The username specified must have the VIEW DEFINITION and CONNECT permissions.

FREQUENTLY ASKED QUESTIONS
Q: How to connect with Integrated Authentication On Windows?
A: The JDBC URL specified in the Host parameter must include 'integratedSecurity=true' or other settings required by your configuration
In addition, copy the x64 version of mssql-jdbc_auth-<version>-<arch>.dll into the directory where the JDBC driver is installed that should match either the value of the 'Driver location' parameter or the driver default folder '\java\Jdbc\mssql'.
Please check the official Microsoft documentation for details:
https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-2017#Connectingintegrated

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
Driver location
This parameter is optional.

By default, all necessary JARs will be loaded from:
'${MODEL_BRIDGE_HOME}/java/Jdbc/mssql'

However, you may use this parameter to specify a different location. All JARs found in the specified folder will be added to the CLASSPATH.

The 'com.microsoft.sqlserver.jdbc.SQLServerDriver' JDBC driver with the 'Microsoft JDBC Driver 7.4 for SQL Server' name is required.
DIRECTORY      
Host Enter the host name or IP address where MS SQL Server database server is running (examples 1 and 2). Optionally, the parameter may contain a fully qualified JDBC connection string as defined for MS SQL Server (example 3).

For Azure SQL Database, bridge requires hostNameInCertificate when specified encrypt=true (example 4).

Example #1:
someservername.com

Example #2:
192.169.10.20

Example #3:
jdbc:sqlserver://192.168.56.61:1433;databaseName=AdventureWorksDW;integratedSecurity=false;

Example #4:
jdbc:sqlserver://abc.database.windows.net:1433;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
STRING   localhost Mandatory
Port MS SQL Server database engine service port number. Default value is 1433.

For optimal connection performance, you should set the 'Port' option when you connect to a named instance.
This will avoid a round trip to the server to determine the port number.
If both a 'Port' option and 'Instance' option are used, the 'Port' option will take precedence and the 'Instance' option will be ignored.
NUMERIC      
Instance MS SQL Server instance name.

SQL Server allow for the installation of multiple database instances per server.
Each instance is identified by a specific name.
To connect to a named instance of SQL Server, you can either specify the port number of the named instance (preferred),
or you can specify the instance name.
STRING      
User The database username on whose behalf the connection is being made.

The user should have at least the VIEW DEFINITION permission and a permission to CONNECT to the database.
Also, for the Microsoft SQL Server specific bridge, if this parameter is empty, then it is assumed that integrated security is being used (see http://msdn.microsoft.com/en-us/library/ms378428.aspx#Connectingintegrated). In this case, the bridge will attempt to connect with this type of signature:

jdbc:sqlserver://; integratedSecurity=true
instead of

jdbc:sqlserver://; user=userid;password=userpassword

However, in order for this to work, the user must have the sqljdbc_auth.dll available on the PATH environment variable. Also the version of the library must match the version of the sqljdbc4.jar that they are using.
STRING      
Password The user password used to connect to the database via JDBC. PASSWORD      
Database This option can be used as a constraint to import a SQL Server database by specifying one database name.

The possible values are one database name: only the schemas and tables that belong to this database will be imported.

Example:
AdventureWorksDW - import tables from the 'AdventureWorksDW' database.
REPOSITORY_SUBSET      
Schema Description: you can specify a list of database schemas to import.
When the list is empty all available schemas are imported.
The list can have one or more schema names separated by semicolons (e.g. schema1; schema2).
You can specify schema name patterns using Microsoft SQL LIKE expressions syntax.
Patterns support inclusions and exclusions.
Here is an example of inclusion syntax, "A%; %B; %C%; D" that tries to get schema names that:
- start with A or
- end with B or
- contain C or
- equal D

Note: when a pattern has special characters, like spaces enclose it in single quote marks (e.g. "'two wo%'; onewo%;").

To exclude a pattern prefix it with 'NOT'. Here is an example of exclusion syntax, "A%; %B; NOT %SYS; NOT 'SYS%'"
that contributes to the following SQL filter: "where (name like A% or name like %B) and (name not like %SYS) and (name not like 'SYS%')"
REPOSITORY_SUBSET      
Tables, Views The subset of tables to import expressed as a semicolon separated list of tables, e.g.
table1; table2

All user tables are imported if that list is empty.

All system tables (e.g. SysAdmin) and objects are ignored by default. If system tables are needed, use the Miscellaneous parameter option -s.

Table name patterns using SQL LIKE expressions syntax:

- Inclusion syntax:
using '%, e.g.
A%; %B; %C%; D
- start with A or
- end with B or
- contain C or
- equal D

- Exclusion syntax:
using 'NOT", e.g.
A%; %B; NOT %SYS; NOT 'SYS%'
- where (name like A% or name like %B)
- and (name NOT like %SYS)
- and (name NOT like 'SYS%')

- Special characters:
using single quote, e.g.
'two wo%'; onewo%;
STRING      
Stored Procedures, Functions You can specify a list of operations (stored procedures and functions) to import from the selected databese.
When the list is empty all available operations will be imported.
The list can have one or more operation names separated by semicolons (e.g. operation1; operation2).
You can specify operation name patterns using Microsoft SQL LIKE expressions syntax.
Patterns support inclusions and exclusions.
Here is an example of inclusion syntax, "A%; %B; %C%; D" that tries to get operation names that:
- start with A or
- end with B or
- contain C or
- equal D

Note: when a pattern has special characters, like spaces enclose it in single quote marks (e.g. "'two wo%'; onewo%;").

To exclude a pattern prefix it with NOT. Here is an example of exclusion syntax, "A%; %B; NOT %SYS; NOT 'SYS%'"
that contributes to the following SQL filter: "where (name like A% or name like %B) and (name not like %SYS) and (name not like 'SYS%')"

If there is a need to prevent all of the operations from being imported, specify a value that doesn't match any operation's name in the database.
REPOSITORY_SUBSET      
Stored procedure details Controls the amount of details imported from stored procedures:

'Signature'
The name and parameters of stored procedures

'Code, signature'
The above plus code

'Lineage, code, signature'
The above plus data lineage derived from the code

'None'
stored procedure details are not included.
ENUMERATED
None
Signature
Code, signature
Lineage, code, signature
Signature  
Import indexes Controls the import of Indexes:

'False'
Indexes are not imported

'True'
Indexes are imported
BOOLEAN
False
True
False  
Miscellaneous Specify miscellaneous options identified with a -option followed by a value if required:

GENERAL OPTIONS
-m <Java Memory's maximum size>
1G by default on 64bits JRE or as set in conf/conf.properties, e.g.
-m 8G
-m 2500M

-j <Java Runtime Environment command line options>
This option must be the last one in the Miscellaneous parameter as all the text after -j is passed "as is" to the JRE, e.g.
-j -Dname=value -Xms1G

-jre <Java Runtime Environment full path name>
It can be an absolute path to javaw.exe on Windows or a link/script path on Linux, e.g.
-jre "c:\Program Files\Java\jre1.8.0_211\bin\javaw.exe"

-v <Environment variable value>
None by default, e.g.
-v var1=value1 -v var2="value2 with spaces"

-model.name <model name>
Override the model name, e.g.
-model.name "My Model Name"

-prescript <script name>
The script must be located in the bin directory, and have .bat or .sh extension.
The script path must not include any parent directory symbol (..).
The script should return exit code 0 to indicate success, or another value to indicate failure.
For example:
-prescript \"script.bat\"

JDBC OPTIONS
-f <number of rows>
The database driver fetch size in number of rows. E.g.
-f 100

-s
Import system objects that are skipped by default.

-synonyms
Disable import of all synonyms.

-server.name <database identification name>
Useful when importing a pluggable database that is being moved between containers or a development database going to a production server.

-backup <directory>
Full path of an empty directory to save the metadata input files for further troubleshooting.
STRING      

 

Bridge Mapping

Meta Integration Repository (MIR)
Metamodel
(based on the OMG CWM standard)
"Microsoft SQL Server Database (via JDBC)"
Metamodel
JdbcMicrosoftSqlServer
Mapping Comments
     
Name Name  
Argument Procedure Column The stored procedure parameters are stored in the arguments
Description Comments on the column  
Kind Type The kind of parameter
Name Name  
Association Exported Keys  
Aggregation   True if all the attributes in the Exported Key a+C105re in the Primary Key
AssociationRole Exported Keys  
ExtraConstraint Update Rule, Delete Rule  
Multiplicity   Nullable property of the columns of the Exported Key
Source   Based on the multiplicity of each role
AssociationRoleNameMap Exported Keys The rolename map is created if the columns in the primary and foreign keys are different
Attribute Table Column  
Comment Comments on the column  
InitialValue Default value  
Name Name  
Optional Based on the nullable property  
PhysicalName Name  
Position Position If position is not provided, the order in which the attributes are retrieved is used.
BaseType Types  
DataType Data Type See datatype conversion array
Length Size  
Name   The name is computed from the datatype
PhysicalName Name  
Scale Maximum scale  
CandidateKey Index, Primary Key  
Name Name  
PhysicalName Name  
UniqueKey Non-Unique property  
Class Table of type "TABLE"
Comment Comments on the table  
CppClassType   Set to ENTITY
CppPersistent   Set to True
Name Name  
PhysicalName Name  
ClassDiagram Schema A class diagram is created for each package and contains all the elements of the package
DerivedType Column Table column, stored procedure column SQL View column or type
DataType Data Type See datatype conversion array
Length Size  
Name   The name is computed from the datatype
PhysicalName Name  
Scale Decimal digits  
UserDefined   True for Type
DesignPackage Schema A Package is created for each retrieved schema. If there is no schema a default package is created.
Name Name Set to "Schema" if there is no schema or the schema has no name.
ForeignKey Exported Keys  
Name Name  
PhysicalName Name  
Index Index, Primary Key, Exported Keys  
Clustered Index type true if index type is tableIndexClustered
Name Name  
PhysicalName Name  
IndexMember Index Member, Key Member  
Position Position in the Index or key  
SortOrder Ascending/descending order  
SQLViewAttribute View Column  
Comment Comments on the column  
Name Name  
PhysicalName Name  
Position Ordinal position  
SQLViewEntity Table of type "VIEW"
Comment Comments on the table  
Name Name  
PhysicalName Name  
StoreModel Catalog The model is built using the elements contained in the catalog (The database name used for catalog)
Name Name Set to MSSQLServer database name.
StoredProcedure Stored Procedure  
Description Comments on the stored procedure  
Name Name  
Synonym Table Synonym  
Name Name