Monday, April 6, 2020

How to get SSIS catalog information's

 

Understanding Where SQL Server SSIS Package Information Is Stored

In SQL Server, Integration Services (SSIS) package execution details and related metadata are stored within the SSISDB catalog database. This centralized storage makes managing and monitoring your SSIS packages easier and more efficient.

Querying SSIS Package Execution Details

The following T-SQL query retrieves detailed information about SSIS package executions along with related folder, project, and package metadata stored in the SSISDB catalog:


SELECT
    E.execution_id,
    E.folder_name,
    E.project_name,
    E.package_name,
    E.reference_id,
    E.reference_type,
    E.environment_folder_name,
    E.environment_name,
    E.project_lsn,
    E.executed_as_sid,
    E.executed_as_name,
    E.use32bitruntime,
    E.operation_type,
    E.created_time,
    E.object_type,
    E.object_id,
    E.status,
    E.start_time,
    E.end_time,
    E.caller_sid,
    E.caller_name,
    E.process_id,
    E.stopped_by_sid,
    E.stopped_by_name,
    E.dump_id,
    E.server_name,
    E.machine_name,
    E.total_physical_memory_kb,
    E.available_physical_memory_kb,
    E.total_page_file_kb,
    E.available_page_file_kb,
    E.cpu_count,
    F.folder_id,
    F.name,
    F.description,
    F.created_by_sid,
    F.created_by_name,
    F.created_time,
    P.project_id,
    P.folder_id,
    P.name,
    P.description,
    P.project_format_version,
    P.deployed_by_sid,
    P.deployed_by_name,
    P.last_deployed_time,
    P.created_time,
    P.object_version_lsn,
    P.validation_status,
    P.last_validation_time,
    PKG.package_id,
    PKG.name,
    PKG.package_guid,
    PKG.description,
    PKG.package_format_version,
    PKG.version_major,
    PKG.version_minor,
    PKG.version_build,
    PKG.version_comments,
    PKG.version_guid,
    PKG.project_id,
    PKG.entry_point,
    PKG.validation_status,
    PKG.last_validation_time
FROM SSISDB.catalog.executions AS E
INNER JOIN SSISDB.catalog.folders AS F ON F.name = E.folder_name
INNER JOIN SSISDB.catalog.projects AS P ON P.folder_id = F.folder_id AND P.name = E.project_name
INNER JOIN SSISDB.catalog.packages AS PKG ON PKG.project_id = P.project_id AND PKG.name = E.package_name;

This query joins executions with their respective folders, projects, and packages to provide a comprehensive overview of SSIS activity.

How to Create the SSISDB Catalog in SQL Server Management Studio

If the SSISDB catalog is not yet created on your server, follow these steps to set it up:

  1. Open SQL Server Management Studio (SSMS).

  2. Connect to your SQL Server Database Engine instance.

  3. In Object Explorer, expand the server node.

  4. Right-click on Integration Services Catalogs and select Create Catalog.

  5. Enable CLR Integration as the catalog requires CLR stored procedures.

  6. Enable automatic execution of Integration Services stored procedures at SQL Server startup. This option runs the catalog.startup stored procedure each time the SQL Server instance restarts, ensuring that the catalog’s operation state is properly maintained and fixing the status of any running packages if the server was previously shut down unexpectedly.

  7. Set a password for the encryption key used by the catalog, then click OK to complete the creation.

Additional Resources

This setup and querying approach gives you control and insight into your SSIS environment, helping you manage package deployments and executions effectively.

at
Labels:

Popular Posts