Enterprise Data Catalog Scanner Configuration Guide > Configuring Data Integration Resources > SQL Server Integration Service
  

SQL Server Integration Service

SQL Server Integration Service (SSIS) is a component of the Microsoft SQL Server database that can be used to perform data migration tasks.

Objects Extracted

The SSIS resource extracts data flow tasks as mappings from an SSIS data source.
You can view the summary, and detailed lineage, and control summary of SSIS assets in the Catalog.

Prerequisites

You must have the advanced SSIS resource license. Contact Informatica Global Customer Support for the license.

Permissions to Configure the Resource

Configure ssisadmin and dbdatareader permissions on the SSIS data source for the user account that you use to access the data source.

Supported Data Sources

Enterprise Data Catalog supports metadata extraction from the following data sources:

Supported Transformations

Enterprise Data Catalog displays the detailed lineage for the following transformations:

Supported Variables

You can upload a variable values file to override the variables defined in the SSIS mapping. You can override variables such as source file name, file path, source tables, target tables, and SQL queries.
The following is an example of a variable values file:
[Packagename.DataFlowTaskName]
User::Variablename1=values
User::Variablename2=values

Supported Parameters

Enterprise Data Catalog supports connection parameters.

Resource Connection Properties

The General tab includes the following properties:
Property
Description
SSIS Scanner Type
Select the type of SSIS resource:
  • - Repository Server. Select this option if you have a configured a repository in MSDB database to store all the packages in SSIS.
  • - File. Select this option if you have configured a file system to store all the packages in SSIS.
  • - Repository ServerDB. Select this option if the SSIS packages are deployed in the SSIS catalog.
You must configure the required properties for the selected SSIS resource type. For more information, see the SSIS Resource Types topic.
Password
The password to connect to the SQL Server Integration Service database or protected package.
Variable values file
Click Choose to select the file that includes values for the SSIS variables. Alternatively, you can place the file in the Variable values file text box from your file browser using a drag-and-drop operation.
Working Directory Path
Specify the path of the source file that is uploaded to the SSIS package.
The Metadata Load Settings tab includes the following properties:
Property
Description
Enable Source Metadata
Etracts metadata from the data source.
Auto assign connections
Indicates that connections are assigned automatically.
Enable Reference Resources
Extracts metadata about assets that are not included in this resource, but referred to in the resource. Examples include source and target tables in PowerCenter mappings, and source tables and files from Tableau reports.
Retain Unresolved Reference Assets
Retains unresolved reference assets in the catalog after you assign connections. Retaining unresolved reference assets help you view the complete lineage. The unresolved assets include deleted files, temporary tables, and other assets that are not present in the primary resource.
Memory
Specifies the memory required to run the scanner job. Select one of the following values based on the data set size imported:
  • - Low
  • - Medium
  • - High
Note: For more information about the memory values, see the Tuning Enterprise Data Catalog Performance article on How To-Library Articles tab in the Informatica Doc Portal
JVM Options
JVM parameters that you can set to configure scanner container. Use the following arguments to configure the parameters:
  • - -Dscannerloglevel=<DEBUG/INFO/ERROR>. Changes the log level of scanner to values, such as DEBUG, ERROR, or INFO. Default value is INFO. When the SSIS data set is huge, use the ERROR scanner log level.
  • - -DagentLogLevel=<NONE/FATAL/ERROR/WARNING/STATUS/INFO/DEBUG>. Changes the Enterprise Data Catalog Agent log level to values such as NONE, FATAL, ERROR, WARNING, STATUS, INFO, and DEBUG. Default log level is DEBUG. Use the ERROR log level when the SSIS data set is huge.
  • - -Dscanner.container.core=<No. of core>. Increases the core for the scanner container. The value should be a number.
  • - -Dscanner.yarn.app.environment=<key=value>. Key pair value that you need to set in the Yarn environment. Use a comma to separate the key pair value.
  • - -Dscanner.pmem.enabled.container.memory.jvm.memory.ratio=<1.0/2.0>. Increases the scanner container memory when pmem is enabled. The default value is 1.

SSIS Resource Types

Configure the following properties for the selected SSIS resource type:
SSIS Resource Type
Properties
Repository Server
  • - Agent URL. URL of the Enterprise Data Catalog agent that runs on a Microsoft Windows Server.
    • Note: Make sure that you specify the URL in the following format: http://<hostname>:<connector_port>/MIMBWebServices.
  • - SQL Server Version. Select one of the following options from the SQL Server Version drop-down list:
    • - SQL Server 2012
    • - SQL Server 2014
    • - SQL Server 2016
    Default is SQL Server 2012.
  • - Package/Repository Name. Specify the repository or package from which you want to import metadata. Click Select. The Select Package/Repository Name dialog box appears. Select the required package using one of the following options:
    • - Select from list. Select the required package or repository from a list of packages or repositories.
    • - Select using regex. Provide an SQL regular expression to select the package or the repository name that matches with the expression.
  • - Host Name. The host name or IP address of the machine where SSIS is running.
File
  • - File. Click Choose to select the file that includes the packages that you want to upload to Enterprise Data Catalog. Alternatively, you can place the file in the File text box from your file browser using a drag-and-drop operation. Make sure that the files have the extension .dtsx. If you want to upload multiple files and multiple parameter files, add the files in a .zip file and upload the .zip file.
  • - Encoding. Select the type of encoding you want for the extracted metadata from the drop-down list.
Repository ServerDB
  • - Authentication Mode. Select the authentication type to connect to the SQL Server Integration Service database. Specify one of the following values:
    • - SQL Server Authentication. Specify the database user name and password to connect to the Microsoft SQL Server.
    • - Windows Authentication. Windows authentication to authenticate the database user name and password to connect to the SSIS server.
    Default is SQL Server Authentication.
  • - Host Name. Host name or IP address of the machine where SQL Server Integration Service is running.
  • - User. User name to connect to the SQL Server Integration Service database.
  • - Database. Name of the SQL Server Integration Service database.
  • - Browse Folder Filter. Specify the name of the package folder in the SSIS catalog.
  • - Packages. Select the SQL Server Integration Service package from which you want to import metadata using one of the following options:
    • - Select from list. Select the required package from a list of packages.
    • - Select using regex. Provide an SQL regular expression to select the package name that matches the expression.
  • - Agent URL. URL of the Enterprise Data Catalog agent that runs on a Microsoft Windows Server.
    • Note: Make sure that you specify the URL in the following format: http://<hostname>:<connector_port>/MIMBWebServices.

Configuring Enterprise Data Catalog Agent for the Resource on a Windows-authenticated Server

To configure the Enterprise Data Catalog Agent for SSIS, perform the following steps:
  1. 1. Download the SQL Server JDBC driver version 4.2 to a temporary location.
  2. 2. Copy the sqljdbc<version>.jar file to the <Enteprise_Data_Catalog_Agent_location>\java\Jdbc\mssql location.
  3. 3. Copy the \sqljdbc_4.2\enu\auth\x64\sqljdbc_auth.dll file extracted from sqljdbc_4.2.jar file to the <Enterprise_Data_Catalog_Agent_location>\jre\bin location.
  4. 4. Run the RestartServerService.bat file.
  5. 5. In Catalog Administrator, create an SSIS resource with the following Enterprise Data Catalog Agent URL: http://<HostName>:<Port>/MIMBWebServices/
  6. 6. Run the resource.

Limitations of the Resource

The following are the limitations of the SSIS resource: