SQL server SSIS package information store in database.
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;
To create the SSISDB catalog in SQL Server Management Studio
- Open SQL Server Management Studio.
- Connect to the SQL Server Database Engine.
- In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
- Click Enable CLR Integration.The catalog uses CLR stored procedures.
- Click Enable automatic execution of Integration Services stored procedure at SQL Server startup to enable the catalog.startup stored procedure to run each time the SSIS server instance is restarted.The stored procedure performs maintenance of the state of operations for the SSISDB catalog. It fixes the status of any packages there were running if and when the SSIS server instance goes down.
- Enter a password, and then click Ok.
https://www.sqlshack.com/deploying-packages-to-sql-server-integration-services-catalog-ssisdb/
