Skip to the content
LeaplogicLeaplogic
  • Home
  • About Us
  • Contact
SIGN IN
  • Home
  • About Us
  • Contact

  • Getting Started
    • Before You Begin
    • Creating an Account
    • Logging into LeapLogic
    • Reset Password
    • Quick Tour of the Web Interface
    • LeapLogic in 15 minutes
      • Prerequisites
      • Step 1. Log into LeapLogic
      • Step 2. Create Assessment and Get Insights
      • Step 3. Create Transformation Pipeline and See Results
      • Step 4. Edit or Optimize the Transformed Code
      • Step 5: Complete the Transformation Lifecycle
  • Introduction to LeapLogic
    • Overview
    • High Level Architecture
    • Supported Legacy and Cloud Platforms
    • Key Features
  • Workload Assessment
    • Overview
    • Value Proposition
    • Creating Assessment
      • Prerequisites
      • Step 1. Provide Primary Inputs
        • Automation Coverage
      • Step 2. Add the Additional Inputs
        • Table Stat Extraction Steps
          • Teradata
          • Oracle
          • Netezza
      • Step 3. Update the Source Configuration
      • Step 4. Configure the Recommendation Settings
    • Assessment Listing
    • Understanding Insights and Recommendations
      • Volumetric Info
      • EDW
        • Oracle
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Vertica
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Snowflake
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Azure Synapse
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • SQL Server
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Teradata
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Netezza
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Google Big Query
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Redshift
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • PostgreSQL
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Duck DB
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • ClickHouse
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Exasol
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • DB2
          • Highlights
          • Analysis
          • Optimization
          • Recommendations
          • Lineage
          • Downloadable Reports
      • ETL
        • Informatica
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Ab Initio
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • DataStage
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Talend
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • SSIS
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Informatica BDM
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Oracle Data Integrator
          • Highlights
          • Analysis
          • Downloadable Reports
        • Pentaho
          • Highlights
          • Analysis
          • Downloadable Reports
        • Azure Data Factory
          • ARM Template
          • Highlights
          • Analysis
          • Downloadable Reports
        • Matillion
          • Highlights
          • Analysis
          • Downloadable Reports
        • SnapLogic
          • Highlights
          • Analysis
          • Downloadable Reports
      • Orchestration
        • AutoSys
          • Highlights
          • Analysis
          • Downloadable Reports
        • Control-M
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • SQL Server
          • Highlights
          • Analysis
      • BI
        • OBIEE
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Tableau
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • IBM Cognos
          • Highlights
          • Analysis
          • Downloadable Reports
        • MicroStrategy
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Power BI
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • SSRS
          • Highlights
          • Analysis
          • Downloadable Reports
        • SAP BO
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • WebFOCUS
          • Highlights
          • Analysis
          • Downloadable Reports
      • Analytics
        • SAS
          • Highlight
          • Analysis
          • Lineage
          • Downloadable Reports
        • Alteryx
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
      • Integrated Assessment (EDW, ETL, Orchestration, BI)
        • Highlights
        • Analysis
        • Optimization
        • Lineage
        • Recommendations
    • Managing Assessment Reports
      • Downloading Report
      • Input Report Utility
      • View Configuration
    • Complexity Calculation Logic
    • Key Benefits
    • Ad hoc Query
  • Metadata Management
    • Overview
    • Introduction to Data Catalog
      • Managing Data Catalog
        • Building Data Catalog
        • Insights to Data Catalog
        • Managing the Repository and Data Source
      • Creating Repository (Repo)
      • Creating Data Source
    • Tag Management
    • Key benefits
  • Batch Processing using Pipeline
    • Introduction
    • Designing Pipeline
      • How to create a pipeline
        • Configuring Migration Stage
          • Schema Optimization
        • Configuring Transformation Stage
          • On-premises to Cloud
          • Cloud-to-Cloud
          • LeapLogic Express
        • Configuring Validation Stage
          • Data Validation
            • Table
            • File
            • File and Table
            • Cell-by-cell validation
          • Query Validation
            • Query Validation (When Data is Available)
            • Query Validation (When Data is Not Available)
          • Schema Validation
        • Configuring Execution Stage
        • Configuring ETL Conversion Stage
          • Ab Initio
          • Informatica
          • Informatica BDM
          • Matillion
          • DataStage
          • SSIS
          • IICS
          • Talend
          • Oracle Data Integrator
          • Pentaho
          • SnapLogic
        • Configuring Mainframe Conversion Stage
          • Cobol
          • JCL
        • Configuring Orchestration Stage
          • AutoSys
          • Control-M
        • Configuring BI Conversion Stage
          • OBIEE to Power BI
          • OBIEE to AWS QuickSight
          • Tableau to Amazon QuickSight
          • Tableau to Power BI
          • Tableau to Superset
          • Tableau to Looker
          • IBM Cognos to Power BI
        • Configuring Analytics Conversion Stage
          • SAS
          • Alteryx
        • Configuring Script Conversion Stage
    • Key Features
      • How to schedule a pipeline
      • Configuring Parameters
  • Pipeline Reports
    • Overview of Pipeline Report
    • Pipeline Listing
    • Reports and Insights
      • Migration
      • Transformation
        • On-premises to Cloud
        • Cloud-to-Cloud
        • LeapLogic Express
      • Validation
        • Data
          • File
          • Table
          • File and Table
        • Query
          • Query Validation Report (When Data is Available)
          • Query Validation Report (When Data is not Available)
        • Schema
      • Execution
      • ETL
        • Ab Initio
        • Informatica
        • Informatica BDM
        • Matillion
        • DataStage
        • SSIS
        • IICS
        • Talend
        • Oracle Data Integrator
        • Pentaho
        • SnapLogic
      • Mainframe
        • Cobol
        • JCL
      • Orchestration
        • AutoSys
        • Control-M
      • BI
        • OBIEE to Power BI
        • OBIEE to Amazon QuickSight
        • Tableau to Amazon QuickSight
        • Tableau to Power BI
        • Tableau to Superset
        • Tableau to Looker
        • IBM Cognos to Power BI
      • Analytics
        • SAS
        • Alteryx
      • Shell Script
      • Common Model
    • Automation Level Indicator
      • ETL
        • Informatica
        • Matillion
        • DataStage
        • Informatica BDM
        • SnapLogic
        • IICS
        • Ab Initio
        • SSIS
        • Talend
        • Pentaho
      • Orchestration
        • AutoSys
        • Control-M
      • EDW
      • Analytics
        • SAS
        • Alteryx
      • BI
      • Shell Script
    • Error Specifications & Troubleshooting
  • SQL Transformation
    • Overview
    • Creating and Executing the Online Notebook
      • How to Create and Execute the Notebook
      • Supported Features
    • Configuring the Notebook
      • Transformation
      • Unit Level Validation
      • Script Level Validation
    • Notebook Listing
  • Operationalization
    • Overview
      • Basic
      • Advanced
      • Cron Expression
    • Parallel Run Pipeline Listing
  • Transformation Source
    • Introduction
    • Creating Transformation Source Type
  • Governance
    • Summary of Governance - Roles and Permissions
    • User Creation
      • Creating a new User Account
    • Adding Roles and permissions
      • How to add Roles and Permissions to a new user?
    • Adding Group Accounts
    • Default Quota Limits
    • Product Usage Metrics
  • License
    • EDW
    • ETL
  • LeapLogic Desktop Version
    • Overview
    • Registration and Installation
    • Getting Started
    • Creating Assessment
      • ETL
      • DML
      • Procedure
      • Analytics
      • Hadoop
    • Reports and Insights
      • Downloadable Reports
      • Reports for Estimation
    • Logging and Troubleshooting
    • Sample Scripts
    • Desktop vs. Web Version
    • Getting Help
  • LeapLogic (Version 4.8) Deployment
    • System Requirements
    • Prerequisites
    • Deployment
      • Extracting Package
      • Placing License Key
      • Executing Deployment Script
      • Accessing LeapLogic
    • Uploading License
    • Appendix
    • Getting Help
  • Removed Features
    • Configuring File Validation Stage
    • Variable Extractor Stage
      • Variable Extractor Report
    • Configuring Meta Diff Stage
      • Meta Diff
    • Configuring Data Load Stage
      • Data Load
    • Configuring Multi Algo Stage
  • FAQs
  • Tutorial Videos
  • Notice
Home   »  Workload Assessment   »  Understanding Insights and Recommendations   »  ETL  »  SSIS Assessment Report

SSIS Assessment Report

This topic contains information about the SSIS assessment report. The input format for SSIS assessment is DTSX, which is an XML-based file format. This file contains instructions for processing data flow from the source to the target.

In This Topic:

  • Highlights
    • Summary
    • Complexity
    • Queries
    • Entities
  • Analysis
    • Source Information
    • Queries
    • Entities
  • Lineage
  • Downloadable Reports
    • Insights and Recommendations
    • Source Inventory Analysis
    • Lineage Report


Highlights

The highlights section gives you a high-level overview of your assessment summary based on the analysis of the selected workloads. It includes a graphical depiction of the complexity of packages as well as summary of the packages, queries and entities used.

Summary

This section summarizes the input SSIS graphs, including number of packages, executable processes, stored procedures and so on. Each file in the SSIS assessment is called a package. This report comprises a total of eight packages, implying that eight input files are provided for the assessment.

  • Total Packages: Total number of input files.
  • Executable Processes: Displays the number of executable processes. It is used to run external files in SSIS packages such as shell, python, etc.
  • Components: A component is data flow in SSIS packages. SSIS furnishes three types of components for data flow.
    • Sources: Extract the data from source data stores.
    • Transformations: Modify the data.
    • Destinations: Load the processed data back into data stores or store it in memory.
  • Connections: Provides the number of connections used to implement integration services to move data from one place to another.
  • Stored Procedures: Pre-defined SQL codes that can be reused.
  • Executables: Displays the number of executables. It is control flow in SSIS packages. SSIS furnishes three types of elements for control flow.
    • Containers: Provide a sequence or structure to run tasks.
    • Tasks: Functional work segments of control flow elements.
    • Precedence constraints: Specify the order of operations as well as the relationships between containers and tasks.
  • Sub-Packages: Displays the total number of packages within another package.

Complexity

This section provides a graphical representation of SSIS files based on the complexity. This information is required to make various decisions, including budget estimation.

Queries

It displays a synopsis of the total queries with information about executable SQL tasks, variables, and so on.

  • Total Queries: Total number of queries in the input files.
  • Execute SQL Tasks: Provides the number of queries in the Execute SQL tasks. It is one of the executable components in SSIS that is used to retrieve result sets.
  • SQL Commands: Provides the number of SQL queries in the SQL Commands component. It is used to interact with the database.
  • Commands Params: Provides the number of SQL queries in the Command Params component.
  • Variable Tasks: Provides the number of SQL queries in the Variable Task component.

Entities

This section provides a synopsis of the analyzed entities including the total number of entities and missing tables in the input packages.


Analysis

This topic provides a detailed examination of packages, queries, and entities.

Source Information

This section provides a comprehensive report of the source files with information about the total number of packages, executables, transformation, connection, and so on.

  • Package Name: Name of the package.
  • Executables: Displays the number of executables in each package. It is control flow in SSIS packages. SSIS furnishes three types of elements for control flow.
    • Containers: Provides a sequence or structure to run tasks.
    • Tasks: Functional work segments of control flow elements.
    • Precedence constraints: Specify the order of operations as well as the relationships between containers and tasks.
  • Transformations: Displays the number of data flow components that are used to perform various actions such as sorting, merging, data cleansing and so on.
  • Connections: Provides information about the connections such as OLE DB connection, FLAT file, etc that are used to connect the database.
  • Stored Procedures: Pre-defined SQL codes that can be reused.
  • Queries: Provides details of queries in the package.
  • Complexity: Provides complexity of each package.
  • Pipeline: A type of component to transform data from source to target.
  • Executable Processes: Displays the number of executable processes. It is used to run external files in SSIS packages such as shell, python, etc.
  • Sub-Packages: Displays the total number of packages within another package.
  • Dependent Packages: Displays the number of packages that depend on other packages.
  • Components: A component is data flow in SSIS packages. SSIS furnishes three types of components for data flow.
    • Sources: Extract the data from source data stores.
    • Transformations: Modify the data.
    • Destinations: Load the processed data back into data stores or store it in memory.
  • Containers: Provides a sequence or structure to run tasks.
  • Event Handler: Displays the number of event handlers in each package. An event handler handles or manages events that arise during the run time
  • Package Parameter: Displays the details of parameters defined in each package.
  • Script Task Count: Displays the number of script tasks used in each package.

Queries

This page displays a detailed analysis of the queries used in the input packages. Moreover, it displays the package name and the query type of each query.

Entities

This section displays a detailed analysis of entities along with the missing table details.

  • Table Name: Displays name of the table.
  • Package Name: Displays the name of the package.
  • Data Volume: Displays the quantity of data.
  • Materialized View: These are queries used to store data in physical tables where you can define to auto update the table when changes are made in the source tables or execute a command to update the tables.
  • Frequency of Use: Displays the frequency of table used.
  • Primary Key: To identify unique row in the table. It will not accept NULL values.
  • Foreign Key: To link two tables.
  • Unique Key: To identify records in a table.
  • No. of Create: Displays number of CREATE queries executed on the table.
  • No. of Delete: Displays number of DELETE queries executed on the table.
  • No. of Insert: Displays number of INSERT queries executed on the table.
  • No. of Read: Displays number of READ queries executed on the table.
  • No. of Update: Displays number of UPDATE queries executed on the table.
  • No. of Rows: Displays number of rows on the table.
  • Primary Index: Indexes based on the primary key.
  • Unique Primary Index: Allows only unique values in the column of the table.
  • Index: Indexes are lookup tables that help to quickly retrieve data from the database.
  • Queries Executed: Number of queries that are executed.
  • Transactional: Displays the number of data flow components that are used to perform sorting, merging, data cleansing and so on.
  • Source Database: Displays name of the source database.


Lineage

End-to-end data and process lineage identify the complete dependency structure through interactive and drill-down options to the last level.

Typically, even within one line of business, multiple data sources, entry points, ETL tools, and orchestration mechanisms exist. Decoding this complex data web and translating it into a simple visual flow can be extremely challenging during large-scale modernization programs. The visual lineage graph adds tremendous value and helps define the roadmap to the modern data architecture. It deep dives into all the existing flows, like Autosys jobs, applications, ETL scripts, BTEQ/Shell (KSH) scripts, procedures, input and output tables, and provides integrated insights. These insights help data teams make strategic decisions with greater accuracy and completeness. Enterprises can proactively leverage integrated analysis to mitigate the risks associated with migration and avoid business disruption.

Now, let’s see how you can efficiently manage lineage.

To view the required lineage:

  1. Select either the Process or Data tab.
  2. Enter the keywords in the Search Keywords field.
  1. Click the Search icon to generate the lineage.

Process lineage illustrates the dependencies between two or more processes such as files, jobs, executables, etc., whereas data lineage depicts data flow between two or more data-holding components such as entities, flat files, etc. 

In addition, the filter search icon allows you to include or exclude particular nodes to obtain the required dependency structure. You can also choose the direction of the lineage. By default, the Dependency Direction is Left to Right Hierarchy. You can also choose Right to Left Hierarchy or Bidirectional dependency directions as required. Moreover, you can also increase the Hierarchy Levels to nth level.

Lineage facilitates you visualize how your selected nodes are connected and depend on each other. The nodes and their connecting edges (relationships) help you to understand the overall structure and dependencies.

Nodes Edges
SSIS Executable Call
Tables Read
File Execute
Job Write
Autosys Box OTHER

Manage Lineage

This feature enables you to view and manage your lineage. You can add, modify, or delete nodes and their relationships to generate an accurate representation of the required dependency structure. There are two ways to update the lineage: either using Complete Lineage report or Lineage Template.

Using Complete Lineage report

Follow the below steps to modify the lineage:

  1. Click the Manage Graph icon.
  1. Click Download Complete Lineage to update, add, or delete the nodes and their relationships in the current lineage.
  1. Once the complete lineage report is downloaded, you can make necessary updates such as updating, deleting or adding the nodes and its relationships.
  2. After making the required changes, upload the updated lineage report in Upload to Modify Lineage.
  3. Click Apply to incorporate the updates into the dependency structure.
  4. Generate the required process or data lineage.

Using Lineage Template

Follow the below steps to add new nodes and their relationships to the current lineage report:

  1. Click the Manage Graph icon.
  1. Click Download Lineage Template.
  1. Once the lineage template is downloaded, you can add new nodes and relationships in the template.
  2. After making the required changes, upload the template in Upload to Modify Lineage.
  3. Click Apply to incorporate the updates into the complete dependency structure.
  4. Generate the required process or data lineage.

Important

To effectively manage lineage, you must adhere to the following rules:

  • Do not modify the column headers or their order in the Complete Lineage report or the Lineage template. The following are the column headers: source_name, source_type, target_name, target_type, relation_type, and database_id.
  • When deleting a row, retain the value in the database_id column and clear all the other column values.
  • When inserting a row, add all the column values except in the database_id column.
  • When updating a row, ensure that all the columns have relevant data.

You can also apply:

FeatureIconUse
FilterUsed to filter the lineage.
ReloadAssists in reloading graphs.
SaveUsed to save the lineage.
DownloadUsed to download the file.
ExpandUsed to enlarge the screen.


Downloadable Reports

Downloadable reports allow you to export detailed assessment reports of your source data which enables you to gain in-depth insights with ease. To access these assessment reports, click Reports.

Types of Reports

In the Reports section, you can see various types of reports such as Insights and Recommendations, Source Inventory Analysis, and Lineage reports. Each report type offers detailed information allowing you to explore your assessment results.

Insights and Recommendations

This report provides an in-depth insight into the source input files. It contains the final output including the details of queries, complexity, packages, and so on.

Here, you can see the ssis folder and the Lineage Dependency Report.xlsx.

Lineage Dependency Report.xlsx: This report contains information about views and package level lineages. It includes information about used and impacted tables, views, files, direct dependencies, dependency hierarchy and more.

This report contains the following information:

  • view_report: Provides information about the views.
  • script_report: Provides information about package level lineage.

Detailed ssis Assessments Reports

To access a detailed assessment report, open the ssis folder.

SSIS Report.xlsx: This report provides insights about the source inventory. It helps you plan the next frontier of a modern data platform methodically. It includes information about files, sub packages, precedence constraints, and more.

This report contains the following information:

  • Report Summary: Provides information about all the generated artifacts.
  • Volumetric Info: Presents a summary of the aggregated inventory after analyzing the source files. For instance, it provides total number of packages, executables, transformations, sub-packages, and likewise. It also provides package-level complexity.
  • File Summary: Lists all the SSIS input files. It contains the total count of executables, pipeline, components, connections, sub-packages, and so on for every SSIS package.
  • Sub-packages: Packages within another package. It lists all the SSIS packages and the associated sub-packages.
  • Precedence Constraints: Defines the relationship between two precedence constraints or tasks.
  • Procedure: Lists all the procedures.
  • Query: Lists all the SQL queries along with the information about executable, executable type, component type, and so on.
  • Transformation: Provides information about transformations.
  • Connection: Defines to implement integration services to move data from one place to another. It lists all the connections along with the connection manager reference id.
  • Entity: Lists all the entities. It also includes information about the entity type, database name, statement type and so on.
  • Parsed Query: Lists all the queries that parsed.
  • Unparsed Query: Lists all the queries that are not parsed.
  • Event Handler: Helps to manage and execute events that arise during the run time. This sheet provides information about the event handlers along with the executables and types.
  • Script Task: Helps to add new functionality to expand the SSIS package capabilities using C++ or Visual Basic. This sheet provides information about the executables, language, project items, and so on.
  • Execute Process: Executes batch files or command files. This sheet provides information about executables, execute process data, arguments, and so on.
  • Executables: It is the control flow in SSIS packages. It lists all the executables along with their type.
  • Connection Manager: Provides information about the connection manager. It includes the creation name and connection string.
  • Package Parameter: Lists all the SSIS packages along with package parameter details.

Browse through the query folder to access the Queries.csv file.

Queries.csv: This report provides information about queries including the used and impacted tables, analyzed status, complexity, and more. If the analyzed status is Analyzed, it indicates that the query is analyzed successfully. Conversely, a Not Analyzed status indicates that the query is not analyzed.

Source Inventory Analysis

It is an intermediate report which helps to debug failures or calculate the final report. It includes all the generated CSV reports.

external_procedures_details.CSV: Provides information about external procedures files. It includes information about packages, procedures, and its availability.

invalid_query.csv: This report lists all the invalid queries.

Lineage Report

This report provides complete dependency details for all nodes. It provides an end-to-end data and process lineage that helps to identify the complete dependency structure and the data flow.

This report contains the following information:

  • Dependency (Process): Provides information about the process lineage.
  • Dependency (Data): Provides information about the data lineage.
  • Nodes: Lists all the source and target nodes along with its type.
  • Volumetric Info (Summary): Provides volumetric information about the artifact types such as input tables, output tables, and schedulers.


To learn more, contact our support team or write to: info@leaplogic.io

Copyright © 2025 Impetus Technologies Inc. All Rights Reserved

  • Terms of Use
  • Privacy Policy
  • License Agreement
To the top ↑ Up ↑