Microsoft SQL Server Integration Services (SSIS) connection

To access your data in Microsoft SQL Server Integration Services, create a connection asset for it.

Microsoft SQL Server Integration Services (SSIS) is a data integration platform.

Supported versions

Microsoft SQL Server Integration Services for Microsoft SQL Server 2022.

Create a connection to Microsoft SQL Server Integration Services

To create the connection asset, you need the following connection details:

Hostname or IP address
Port number
If the Microsoft SQL Server has been set up in a domain that uses NTLM (New Technology LAN Manager) authentication, select Use Active Directory. Then enter the name of the domain associated with the username and password
SSL certificate (if required by the database server)
Username and password

For Credentials and Certificates, you can use secrets if a vault is configured for the platform and the service supports vaults. For information, see Using secrets from vaults in connections.

Choose the method for creating a connection based on where you are in the platform

In a project: Click Assets > New asset > Prepare data > Connect to a data source. See Adding a connection to a project.

In a catalog: Click Add to catalog > Connection. See Adding a connection asset to a catalog.

In the Platform assets catalog: Click New connection. See Adding platform connections.

Next step: Add assets from the connection

In a project, create and run a metadata import with the Import ETL job or the Get ETL job lineage goal to add assets to a catalog. See Importing ETL jobs and Getting ETL job lineage.

Federal Information Processing Standards (FIPS) compliance

The Microsoft SQL Server Integration Services connection cannot be created in a FIPS environment.

Microsoft SQL Server Integration Services setup

Install Integration Services (SSIS)

Restrictions

You can use this connection only for source data. You cannot write to data or export data with this connection.

Supported file types

The Microsoft SQL Server Integration Services connection supports importing ETL job information.

Configuring lineage metadata import for Microsoft SQL Server Integration Services

When you create a metadata import for the Microsoft SQL Server Integration Services connection, you can set options specific to this data source, and define the scope of data for which lineage is generated. For details about metadata import, see Designing metadata imports.

To import lineage metadata for Microsoft SQL Server Integration Services, complete these steps:

Create a data source definition. Select Microsoft SQL Server as the data source type.
Create a connection to the data source in a project.
Create a metadata import. Learn more about options that are specific to Microsoft SQL Server Integration Services data source:
- After you select a data source definition, in the Scanner field, select SSIS.
- When you define a scope, you can analyze the entire data source or use the include and exclude options to define the exact folders, packages and projects that you want to be analyzed. See Include and exclude lists.
- Optionally, you can provide external input in the form of a .zip file. You add this file in the Add inputs from file field. The file must have a supported structure. See External inputs.
- Specify advanced import options.

Include and exclude lists

You can include or exclude assets up to the package level. Provide folders, projects and packages in the format folder/project and folder/package. Each part is evaluated as a regular expression. Example values:

myFolder/.*: all projects and packages in myFolder folder,
myFolder/myProject: the myProject project in myFolder folder,
myFolder/mySubfolder/myPackage: the myPackage package in myFolder/mySubfolder folder

External inputs

If you use external Microsoft SQL Server Integration Services projects (in the .ispac format), packages (in the .dtsx format), and package configurations (in the .dtsconfig format), you can add them in a .zip file as an external input. The .zip file can have the following structure:

<SSIS_folder_name>
  [project_name].ispac
  [package_name].dtsx
  [package_config_name].dtsconfig
ssisVariableOverrides.csv
ssisComponentOverrides.csv

The ssisVariableOverrides.csv file contains variable overrides values.
The ssisComponentOverrides.csv file contains component override values.

Advanced import options

Enable packages: You can extract packages that were deployed by using legacy Package Deployment Model.
Enable projects: You can extract projects that were deployed by using legacy Project Deployment Model.
Environment folder: You can specify the name of a folder of the environment that is used to set parameter values on the server. If no value is provided, the folder of the project is used.
Environment name: You can specify the name of the environment that is used to set parameter values on the server. If no value is provided, no environment is used and only literal values that are defined on the server are extracted.
Analyse data flow tasks: You can specify a comma-separated list of data flows to include in the analysis. As a value, specify the full name of the data flow task, represented by the PackagePath attribute in Microsoft SQL Server without the first backslash. Each value is evaluated as a regular expression. Example values:

Package\\Data Flow Task 1: The data flow task with the name 'Package\Data Flow Task 1`,
Package\\.*: All data flow tasks in the 'Package' package

Exclude data flow tasks: You can specify a comma-separated list of data flows to exclude from the analysis. As a value, specify the full name of the data flow task, represented by the PackagePath attribute in Microsoft SQL Server without the first backslash. Each value is evaluated as a regular expression. Example values:

Package\\Data Flow Task 1: The data flow task with the name 'Package\Data Flow Task 1`,
Package\\.*: All data flow tasks in the 'Package' package

Date format: You can specify the format of date and time. You can specify many formats, separated by the vertical bar (|), for example yyyy-MM-dd HH:mm:ss.S|HH:mm:ss. The values must comply with SimpleDateFormat format. To use the default date and time format, leave the value empty.
Current Date: You can specify a date to replace the value of the SSIS GETDATE() expression. Use this option if you do not want the lineage to be updated with the current date each time the analysis is run. Add the date in the format that you specified in the Date format setting. To use the current date, leave the value empty.
Current date UTC: You can specify a date to replace the value of the SSIS GETUTCDATE() expression. Use this option if you do not want the lineage to be updated with the current date each time the analysis is run. Add the date in the format that you specified in the Date format setting. To use the current date, leave the value empty.
Skip disabled tasks and packages: You can exclude disabled tasks and packages from the analysis. If you want to analyze them, set the value to false.

Learn more

SQL Server Integration Services

Parent topic: Supported connections