Microsoft SQL Server Integration Services (SSIS) is an ETL tool that extracts data from multiple sources, transforms, and loads them into different target databases. SSIS is a component of the Microsoft SQL Server database.
You can run connection-aware scans on Microsoft SQL Server Integration Services sources.
Objects extracted
Metadata Command Center extracts the following objects from an SSIS source system:
•Calculation
•Data Task
•Database
•Folder
•Package
•Parameter
•Parameter Container
•Procedure Definition
•Procedure Instance
•Result Set
•Result Set Calculation
•Schema
•Script
•Statement
Prerequisites for configuring the SSIS catalog source
To extract SSIS metadata using the Files mode, perform the following prerequisite tasks:
• Split input into logical applications, and then create a configuration for each logical application. For example, if you split the input into 10 logical applications based on lines of business, create 10 configurations. If a line of business comprises different areas such as treasury or accounting, create separate configurations for those areas also.
Warning: Jobs might run slowly or hang if you don't split input into logical applications.
• Choose one of the following options:
- If you store the packages in a database, export the input files together with the corresponding DTSCONFIG, CONMGR, and PARAMS files. Export the input files to the Secure Agent machine using the dtutil utility. You get the utility during the Microsoft SQL Server installation.
- If you store the package as files in the DTSX format, export the input files from the files system. You must also export the corresponding DTSCONFIG and CONMGR files.
To extract the SSIS metadata using the SSISDB mode, perform the following prerequisite tasks:
•Use the SQL Server connector to connect to Microsoft SQL Server Integration Services. For information about configuring a connection in Administrator, see Connections in the Cloud Common Services help.
•If SSISDB has the packages deployed, then the catalog sources can connect to it to extract DTSX, CONMGR, and PARAMS files.
• For Windows authentication on a Linux machine, consider NTLM user authentication. The following example shows the connection string for a Microsoft SQL server database that uses NTLM authentication in a domain named informatica.com:
To perform a connection-aware scan, run the catalog source job. After the job completes, assign connections and run the job again. For more information about the types of connection scans and assigning connections, see Administration.
Permissions
To extract the SSIS project files from an SSISDB database in the SSISDB mode, grant permissions to perform the following operations on the SSISDB database:
•select on catalog.folders
•select on catalog.projects
•select on catalog.packages
•select on catalog.environments
•select on catalog.environment_variables
•select on catalog.object_parameters
•execute on catalog.get_project procedure
To read specific folders, projects, or packages, assign one of the following roles or permissions:
•Membership to the ssis_admin database role
•Membership to the sysadmin server role
•READ permission on the folders and projects
Connection properties
On the Registration page in Metadata Command Center, you can choose to connect using the Files option or the SSISDB option.
Files option
Use this option to extract metadata from files and directories in the file system. Specify the path to the source directory that contains the files that you want to extract. For example, /home/ssis may be the source directory on Linux.
SSISDB option
To connect using the SSISDB option, configure a connection to SQL Server in the Informatica Intelligent Cloud Services Administrator. You can view the connection properties for that connection on the Registration page in Metadata Command Center.
The following table describes the Microsoft SQL Server connection properties:
Property
Description
Runtime Environment
The execution platform that runs tasks. The runtime environment is either a Secure Agent or a serverless runtime environment.
User Name
Name of the Microsoft SQL Server user account that connects to the Microsoft SQL Server database.
Host
Host name of the machine where Microsoft SQL Server runs.
Port
Port number for the Microsoft SQL Server database engine service.
Code Page
Code page associated with the Microsoft SQL Server database.
Schema
A list of schema for which you want to extract metadata.
Database Name
Name of the Microsoft SQL Server database to connect to.
Configuration parameters for metadata extraction
In the Configuration Parameters area, enter configuration parameters.
Note: Click Show Advanced to view all configuration parameters.
The following table describes the parameters that you can configure to extract metadata from an SSIS source system:
Parameter
Description
Default variables values file
Applicable for both SSISDB and Files reader module modes.
Path to the file with default variables values.
File format consists of [VARIABLES] and/or [FUNCTIONS] section.
External Connection Manager Files
Applicable if you connect using the Files reader module mode.
Paths of connection manager files used by the SSIS packages. For example, enter C:\SSIS\Projects\MegaEtl\MegaDb.conmgr
DTSX Package to Project Param file mappings
Applicable if you connect using the Files reader module mode.
If the path of a DTSX package matches the pattern specified in the DTSX package pattern field, then the variables that you define in the corresponding Project Params file field will be included.
For example, enter /home/RootDir/ssis/*.dtsx=/home/RootDir/ssis/Project.params
Read parameters from SSISDB
Advanced parameter. Applicable if you connect using the SSISDB reader module mode.
Determines whether to read parameters from SSISDB.
Select one of the following options:
- Yes. Reads project and package parameters from SSISDB.
- No. Doesn't read project and parameters from SSISDB.
Expert parameter
This property appears when you click Show Advanced. Use expert parameters when it is recommended by Informatica Global Customer Support.
Referenced source systems
If the source system references another source system, create a connection assignment in Metadata Command Center to view data lineage with endpoints. To create a connection assignment, create a connection based on the referenced source system, and then assign the connection to the catalog source.
Note: You can view the lineage with reference objects without creating a connection assignment. After connection assignment, you can view the actual objects.
You can connect to the following types of referenced source systems:
•Oracle. The catalog source must belong to the Database class type.
•Microsoft SQL Server. The catalog source must belong to the Database class type.
•File System. The catalog source must belong to the File System class type.
Note: For catalog sources that you created before the April 2023 release with connections assigned at the schema level, purge and run the catalog source again.