MS SQL Resource Configuration

mssql.subdialect

Sub-dialect of the target system in this connection

• MSSQL — for SQL Server (2008 and newer)

• MSSQL2005 — for SQL Server 2005 and older

• SYBASE — for Sybase ASE / SAP ASE

• PDW — for Parallel Data Warehouse

• AZURE_SQL_DB — for Azure SQL Database

• AZURE_MANAGED — for Azure SQL Managed Instance

• AZURE_SYNAPSE — for Azure Synapse Analytics (Azure SQL Data Warehouse / SQL Shared pool)

• AMAZON_RDS_SQL_SERVER — for Amazon RDS for SQL Server

MSSQL

mssql.username

User name for connection to the MS SQL database

manta_user

mssql.extractedDbsSchemas

List of databases and schemas to extract, separated by commas, which are to be provided in the format database/schema; each part is evaluated as a regular expression

database1/sch1,database2/sch2,database3

mssql.excludedEntities

Specify the entities to exclude from extraction. The filter input is divided into two parts by a semicolon character ; . Each filter follows the same format of (Entity Types;)?Name Pattern.

1. Entity types (optional): The first part lists the types of entities to exclude. These can include table, view, type, procedure, function, trigger, sequence, synonym, or database_link. Multiple entity types should be separated by a | character .

2. Name pattern: The second part of the field is a name pattern that matches the entity names to be excluded. This pattern can be either a single name (for any database and any schema), a schema and a name separated by a / (for any database) or a database, schema and name separated by / (see examples).

Both parts of the filter expression are case-insensitive.

You can specify multiple filters by separating them with a comma ,.

If a non-filtered entity (e.g., a view) points to a filtered entity (e.g., a table), the filtered entity will still appear as a deduced item.

Available as of R42.1

• temp_.*: Excludes all objects whose name starts with temp_, regardless of their type, database or schema

• function;abc.*: Excludes functions whose name starts with abc, regardless of their database or schema

• table|view;dw.*/.*,function|procedure;abc.*/def.*/ghi.*: Excludes every table and every view from schemas that start with dw of all databases and every function and procedure whose name starts with 'ghi' from schemas that start with 'def' of databases that start with 'abc'

mssql.ddl.encoding

Encoding of automatically extracted MS SQL DDL scripts. See Encodings for applicable values.

utf8

mssql.authenticationType

Authentication type to use; see MS SQL Server Authentication Types for a description of the options
(Orchestration API only, do not use in file-based configurations)

SQL Server​
Native
NTLM
JavaKerberos

mssql.serverName

User-set server name. Leave blank, to use the automatically extracted server name. Set to a custom unique server name to override the automatically extracted server name. For example, in Multi-AZ deployments for Amazon RDS for Microsoft SQL Server, the value should specify a "cluster name"; because (1) any of the individual SQL Server instances could be used during extraction (and therefore, the server name can change over time for Automatic Data Lineage) and (2) the individual server names are re-generated after each reboot with failover by AWS.

SQLEXPRESS

mssql.extractor.authentication.jaasConf

JAAS configuration file that overrides the default settings of the Java Authentication and Authorization Service; only valid for JavaKerberos authentication

/path/to/jaas.conf

filepath.lowercase

Whether paths to files should be lowercase (false for case-sensitive file systems, true otherwise)

false
true

mssql.ddl.input

Directory with automatically extracted MS SQL DDL scripts (for the analysis phase)

${mssql.dll.output}

mssql.script.replace

Path to the CSV file with the replacements to be applied to the provided Transact-SQL scripts; see Placeholder Replacement in Input Scripts for details about the replacement file format

${manta.dir.input}/mssql/${mssql.dictionary.id}/replace.csv

mssql.analyze.parallelCount

Number of parallel threads which will analyze DDL and Transact-SQL

4

mssql.dictionary.mappingFile

Path to automatically generated mappings for MS SQL servers

${manta.dir.temp}/mssql/
mssqlDictionaryMantaMapping.csv

mssql.connections.file

Name of the file that contains linked server connection definitions in UTF-8 encoding

This file only overrides definitions of linked servers extracted from MS SQL server. As such, it does not need to contain all the connections, just the ones where the user needs to change/add any of the connection attributes.

For more information about this file and its format, see the section on Connection Definition Settings in Informatica PowerCenter Resource Configuration and get more details in (Manual) Connection Mappings Explained.

linkedServerConnectionsConfiguration.prm

mssql.expressionDescriptions.enabled.ddl

Enables or disables building transformation logic descriptions from SQL code in DDL scripts

false
true

mssql.expressionDescriptions.enabled.queryService

Enables or disables building transformation logic descriptions from MS SQL SQL code defined in other technologies

false
true

mssql.extractor.hostNameInCertificate

The driver will validate the TLS certificate's subject value using the provided host name in certificate. If the host name in the certificate is blank, the driver will use the server name to validate the server TLS certificate. If the server name is different from the DN (Domain Name) of the certificate, set the host name in the certificate to the server name.

SQLSERVER

mssql.analyze.retainUnusedResultSetColumns

Flag specifying whether the data lineage should include sub-query resultset columns that do not have any downstream lineage

By default, set to false

false
true

Server name

Unique name of the server

SQLSERVER

Host name

Name of the host where the database is running

localhost
127.0.0.1

Connection ID

External MS SQL connection ID in third-party tools; for example, as it appears in an SSIS package, Informatica PowerCenter connection string, etc., or it can be left empty if it is the same as the server name listed as the database instance name

WIN-RR1232

Exclude filter

List of databases and schemas to exclude from extraction, separated by commas

database3/sch3,database3/sch4