SQL Server Integration Services source configuration

The Source configuration field in theSQL Server Integration Services (SSIS) technical lineage Edge capability allows you to map the systems names of databases and connections in SSIS to the names of System assets in Data Catalog.

Tip If you previously integrated SQL Server Integration Services via the lineage harvester (deprecated), you can copy and paste the JSON code from your <source ID> configuration file into the Source configuration field.

The value of the Source configuration field must be a valid block of JSON code, for example:

Copy 
{
  "DataSources": {
    "dhb-sql-prod": {
      "dialect": "mssql",
      "collibraSystemName": "my-system-name"
    }
},
  "ConnStringRegExTranslation": {

    "Data Source=dhb-sql-prod;Initial Catalog=SFG_repl_staging;Provider=SQLNCLI11;Integrated Security=SSPI.*": {
      "dbname": "DATAHUB",
      "schema": "DBO",
      "dialect": "mssql",
      "collibraSystemName" : "WAREHOUSE"
    },

    "Server=sb-dhub;User ID=SYS_USER;Initial Catalog=STAGEDB;Port=6306.*": {
      "dbname": "STAGEDB",
      "schema": "STAGE_OWNER",
      "dialect": "sybase",
      "collibraSystemName" : ""
    }

  }
}

The following sections describe the properties you can use in your JSON code block.

DataSources

Use DataSources to map a connection name to a Collibra system name and dialect. The connection name must match the name as it appears in your SSIS connection manager. If you define the same connection in both DataSources and ConnStringRegExTranslation, the ConnStringRegExTranslation definition takes precedence.

Property

 Description Required?

DataSourceName

The name of your data source.

No

dialect

The dialect of the database.

If you omit this property, SSIS connections are parsed as SQL Server (mssql) by default. Set it explicitly for non-SQL Server databases, such as Oracle, DB2, or Teradata.

You can enter one of the following values:

  • azure, for an Azure SQL Server data source.
  • bigquery, for a Google BigQuery data source.
  • db2, for an IBM DB2 data source.
  • hana, for an SAP HANA data source.
  • hana-cviews, for getting lineage from calculated views in an SAP HANA Classic on-premises data source.
  • hana-cviews-v2, for getting lineage from calculated views in an SAP HANA Cloud/Advanced data source.
    Important 

    To get technical lineage including calculated views, you must harvest SAP HANA by specifying two data sources in the lineage harvester configuration file. In one data source, specify the hana dialect, and in the other, specify the hana-cviews or hana-cviews-v2 dialect.

  • hive, for a HiveQL data source.
  • greenplum, for a Greenplum data source.
  • mssql, for a Microsoft SQL Server data source.
  • mysql, for a MySQL data source.
  • netezza, for a Netezza data source.
  • oracle, for an Oracle data source.
  • postgres, for a PostgreSQL data source.
  • redshift, for an Amazon Redshift data source.
  • snowflake, for a Snowflake data source.
  • spark, for a Spark SQL data source.
  • sybase, for a Sybase data source.
  • teradata, for a Teradata data source.

No

collibraSystemName

The system or server name of the data source.

Use this property to override the default Collibra System asset name for this data source.

No

ConnStringRegExTranslation

Use ConnStringRegExTranslation to map a connection string to connection details. Definitions in this section take precedence over DataSources.

If a connection string does not match any pattern in this section and has no corresponding entry in DataSources, it remains unresolved and appears as UNDEFINED on the technical lineage source status page. To resolve unresolved connections, go to Resolving a CONFIGURATION analyze error.

Property

 Description Required?

<regular expression>

A regular expression pattern that must match one or more connection strings.

When you define the pattern:

  • Match the connection string exactly as it appears in the SSIS connection manager, stored in the .dtsx or .conmgr file, including the connection string prefix. For example, if the connection string starts with Server=, the key must also start with Server=.
  • The pattern must match the entire connection string. Append .* only if you deliberately shorten a pattern to cover several connection strings sharing a prefix.
  • If more than one pattern matches a connection string, the first matching pattern in the JSON file is used. Place more specific patterns above broader ones.
  • Keys are interpreted as regular expression patterns, and the configuration file is JSON. Therefore, backslashes must be escaped for both the regular expression and JSON. Write a single \ from the connection string as \\\\, and a literal . as \\.. For example, the named instance SQLHOST01\DEV becomes SQLHOST01\\\\DEV.
    • An unescaped key might cause an error or fail to match the connection string. For example, \D is a valid regular expression that matches any non-digit character, so SQLHOST01\DEV does not match the literal server name.
  • Matching is case-insensitive.

Example 1: Shortened pattern

Connection string in the SSIS package:

Server=sb-dhub;User ID=SYB_USER2;Initial Catalog=STAGEDB;Port=6306;Persist Security Info=True;Auto Translate=False;

Regular expression:

Server=sb-dhub;User ID=SYB_USER2;Initial Catalog=STAGEDB;Port=6306.*

The pattern starts with the same prefix as the connection string (Server=) and matches the beginning exactly. It ends with .* to cover any additional properties.

Example 2: Complete escaped key

Connection string in the SSIS package:

Data Source=SQLPROD01\FINANCE;Initial Catalog=DW_Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;

Regular expression:

Data Source=SQLPROD01\\\\FINANCE;Initial Catalog=DW_Staging;Provider=SQLNCLI11\\.1;Integrated Security=SSPI;

The backslash in the named instance and the dot in the provider version are each escaped for both the regular expression and JSON. The key matches the full connection string without .*.

No

dbname

 The name of your database, to which the data source connection refers.

No

schema

The name of your schema, to which the regular expression refers.

No

dialect

The dialect of the database.

You can enter one of the following values:

  • azure, for an Azure SQL Server data source.
  • bigquery, for a Google BigQuery data source.
  • db2, for an IBM DB2 data source.
  • hana, for an SAP HANA data source.
  • hana-cviews, for getting lineage from calculated views in an SAP HANA Classic on-premises data source.
  • hana-cviews-v2, for getting lineage from calculated views in an SAP HANA Cloud/Advanced data source.
    Important 

    To get technical lineage including calculated views, you must harvest SAP HANA by specifying two data sources in the lineage harvester configuration file. In one data source, specify the hana dialect, and in the other, specify the hana-cviews or hana-cviews-v2 dialect.

  • hive, for a HiveQL data source.
  • greenplum, for a Greenplum data source.
  • mssql, for a Microsoft SQL Server data source.
  • mysql, for a MySQL data source.
  • netezza, for a Netezza data source.
  • oracle, for an Oracle data source.
  • postgres, for a PostgreSQL data source.
  • redshift, for an Amazon Redshift data source.
  • snowflake, for a Snowflake data source.
  • spark, for a Spark SQL data source.
  • sybase, for a Sybase data source.
  • teradata, for a Teradata data source.

If you omit this property, SSIS connections are parsed as SQL Server (mssql) by default. Set it explicitly for non-SQL Server databases, such as Oracle, DB2, or Teradata.

No

collibraSystemName

The system or server name of the data source.

Use this property to override the default Collibra System asset name for this data source.

No

Resolving a CONFIGURATION analyze error

If the technical lineage source status page shows a CONFIGURATION analyze error for your SSIS source, expand the error to find the unresolved connections and a snippet for each one:

Copy 
# Please, define 'UNDEFINED' values in your configuration file
# Move these entries into the 'ConnStringRegExTranslation' section of
# your connection_definitions.conf, keeping the entries and sections it already
# has, and leave these '#' lines behind - they are not valid JSON. Keys of that
# section are regular expressions, so the connection strings below are escaped
# to match as they are.

{
  "ConnStringRegExTranslation": {
    "Data Source=SQLPROD01\\\\FINANCE;Initial Catalog=DW_Staging;Provider=SQLNCLI11\\.1;Integrated Security=SSPI;": {
      "collibraSystemName": "UNDEFINED",
      "dbname": "DW_Staging",
      "dialect": "mssql",
      "schema": "UNDEFINED"
    }
  }
}

Copy only the JSON object. Consider the following:

  • Do not copy the lines that start with #, as they are not valid JSON.
  • The JSON snippets provided in the analyze error are already escaped as required. You don't need to escape the regular expression patterns manually.
  • Do not modify the connection string key or append .*. Replace each UNDEFINED value with the appropriate value.
Copy 
{
  "ConnStringRegExTranslation": {
    "Data Source=SQLPROD01\\\\FINANCE;Initial Catalog=DW_Staging;Provider=SQLNCLI11\\.1;Integrated Security=SSPI;": {
      "collibraSystemName": "finance-warehouse",
      "dbname": "DW_Staging",
      "dialect": "mssql",
      "schema": "dbo"
    }
  }
}

Then add this entry to the ConnStringRegExTranslation section of your Source configuration, keeping any existing entries:

Copy 
{
  "DataSources": {
    "analytics-db": {
      "dialect": "mssql",
      "collibraSystemName": "analytics-system"
    }
  },
  "ConnStringRegExTranslation": {
    "Data Source=SQLPROD01\\\\FINANCE;Initial Catalog=DW_Staging;Provider=SQLNCLI11\\.1;Integrated Security=SSPI;": {
      "collibraSystemName": "finance-warehouse",
      "dbname": "DW_Staging",
      "dialect": "mssql",
      "schema": "dbo"
    },
    "Server=reporting-db;User ID=SYS_USER;Initial Catalog=REPORTS;Port=6306.*": {
      "dbname": "REPORTS",
      "schema": "dbo",
      "dialect": "sybase",
      "collibraSystemName": "reporting-system"
    }
  }
}