Microsoft SQL Server Integration Services (SSIS) (Repository Server) - Import
Bridge Requirements
This bridge:is only supported on Microsoft Windows.
requires the tool to be installed to access its SDK.
Bridge Specifications
| Vendor | Microsoft |
| Tool Name | SQL Server Integration Services (SSIS) |
| Tool Version | 9.0 (2005) to 15x (2019) |
| Tool Web Site | https://docs.microsoft.com/en-us/sql/integration-services/ |
| Supported Methodology | [Data Integration] Multi-Model, Metadata Repository, ETL (Source and Target Data Stores, Transformation Lineage, Expression Parsing) via .Net API |
| Data Profiling |  |
| Incremental Harvesting | |
| Multi-Model Harvesting |  |
| Remote Repository Browsing for Model Selection | |
SPECIFICATIONS
Tool: Microsoft / SQL Server Integration Services (SSIS) version 9.0 (2005) to 15x (2019) via .Net API
See https://docs.microsoft.com/en-us/sql/integration-services/
Metadata: [Data Integration] Multi-Model, Metadata Repository, ETL (Source and Target Data Stores, Transformation Lineage, Expression Parsing)
Component: MicrosoftSqlServerRepositoryIs version 11.2.0
OVERVIEW
The bridge imports SSIS Packages from a corresponding SQL Server Repository using the SQL Server SDK APIs.
REQUIREMENTS
In order for this bridge to connect to the SQL Server Repository and import, it is necessary to ensure that the SQL Server Client Components are installed properly and corresponding services are running locally or established in your local network. The bridge uses Windows Authentication to connect to Integration Services. Please be sure the application is logged into Windows Domain as a 'User', which has appropriate rights to the objects you wish to import.
FREQUENTLY ASKED QUESTIONS
Q: Is it possible to disable case sensitivity for column names in SSIS packages?
A: ETL bridges import all features "AS IS" depending on the database type and its rules. The Database bridge sets case sensitive flag if needed and the stitcher is capable of stitching, even if names are in different cases.
LIMITATIONS
Refer to the current general known limitations at https://metaintegration.com/Products/MIMB/Help/#!Documents/mimbknownlimitations.html
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 | |||||||
| SQL Server version | Specifies the version of SQL Server. | ENUMERATED |
|
2012 | ||||||||
| Host | Host name or IP address where SQL Server IS is running. | STRING | localhost | Mandatory | ||||||||
| Package | SQL Server Integration Service Project name to be processing. | REPOSITORY_SUBSET | Mandatory | |||||||||
| Password | The value of the password for the protected package. | PASSWORD | ||||||||||
| Variable values file | MICROSOFT SQL SERVER IS CONFIGURATION OVERRIDE File defines overrides for SSIS parameters and variables. SSIS supports project and package level parameters (and variables). The bridge understands and uses them for lineage building, and supports parameters produced in any SSIS version since 2005. Each definition consists of a name and value pair (e.g. param_name=param_value_verride). Each name should be prefixed with its NAMESPACE. For example: $Project::project_parameter_1=global_override_1 $Package::package_parameter_1=global_override_2 User::user_variable_1=global_override_3 System::system_variable_1=global_override_4 A configuration pair may be set on the ROOT (file beginning) or separated by a CONTEXT ([Project:project_name.]package_name[.task_name]). For example: $Project::project_parameter_1=global_override_1 // ROOT override [PackageName] $Project::project_parameter_1=global_override_2 // package specific override [PackageName.TaskName] User::task_variable_1=global_override_3 // task specific override For multi-project import the CONTEXT requires a Project prefix. For example: $Project::project_parameter_1=global_override_1 // ROOT, all projects override [Project:Project_1] $Project::project_parameter_1=global_override_2 // project "Project_1" specific overrride [Project:Project_1.PackageName.TaskName] $Project::project_parameter_1=global_override_3 // project "Project_1" package and task specific overrride [Project:Project_2.PackageName] $Project::project_parameter_1=global_override_4 // project "Project_2" package specific overrride Consider using this paramater when SSIS employs variables (without the correct default values) in SQL statements. It should help the bridge understanding lineage through SQL statements. When the same variable is defined multiple times in a context, the latest definition is used. However, when the same variable is defined in a [Package] and a [Package.Task] contexts the definition in the higher level context, e.g., [Package.Task], is used. Variable names are case sensitive. The values in this file override default variable values, if any defined in SSIS. |
FILE |
|
|||||||||
| Miscellaneous | ${BridgeLib.bp_misc_common_etl_import_options} OTHER OPTIONS -s Enable specifying mappings between Transformation Script inputs and outputs externally. In this case, the inputs go to a table in "_Script_" database and outputs come from a table in the database. One can specify mapping relationships between inputs and outputs by defining a mapping between their tables. By default the bridge interconnect all inputs to all outputs in Transformation Scripts. |
STRING |
Bridge Mapping
| Meta Integration Repository (MIR) Metamodel (based on the OMG CWM standard) |
"Microsoft SQL Server Integration Services (SSIS) (Repository Server)" Metamodel MicrosoftSqlServerIs |
Mapping Comments |
| Attribute | Column | |
| Description | Column.Description | |
| Name | Column.Name | |
| BaseType | SSIS Type | |
| Class | Table | |
| Description | Table.Description | |
| Name | Table.Name | |
| ClassifierMap | Connection or Trainsformation Output link | |
| Description | Description | |
| Name | Name | |
| DataAttribute | Transformation Column | |
| Description | Description | |
| Name | Name | |
| DataSet | Output/Input | |
| Description | Description | |
| Name | Name | |
| DerivedType | ColumnType | |
| Name | DataType | |
| DesignPackage | Package | |
| Description | Package.Description | |
| Name | Package.Name | |
| FeatureMap | Input/Output column lineageId link | |
| Name | Name | |
| StoreModel | Package |