Hello, I am trying to pull in data from the SSISDB database of SQL Server. Below is the script I am using in the data load editor. It executes the script without error, but fetches 0 lines. If I replace the sql query with test data and pull from another database and table, it fetches lines successfully. Any idea why it does not pull from this SSISDB database? The query should return about 5,500 records.
LIB CONNECT TO 'server7_SSISDB';
LET StartDate = Date(Today()-10,'MM-DD-YYYY');
table_Executions:
LOAD [execution_id]
,[folder_name] AS [Execs_folder_name]
,[project_name] AS [Execs_project_name]
,[package_name] AS [Execs_package_name]
,[reference_id] AS [Execs_reference_id]
,[reference_type] AS [Execs_reference_type]
,[environment_folder_name] AS [Execs_environment_folder_name]
,[environment_name] AS [Execs_environment_name]
,[project_lsn] AS [Execs_project_lsn]
,[executed_as_sid] AS [Execs_executed_as_sid]
,[executed_as_name] AS [Execs_executed_as_name]
,[use32bitruntime] AS [Execs_use32bitruntime]
,[operation_type] AS [Execs_operation_type]
,[created_time] AS [Execs_created_time]
,[object_type] AS [Execs_object_type]
,[object_id] AS [Execs_object_id]
,[status] AS [Execs_status]
,[start_time] AS [Execs_start_time]
,[end_time] AS [Execs_end_time]
,[caller_sid] AS [Execs_caller_sid]
,[caller_name] AS [Execs_caller_name]
,[process_id] AS [Execs_process_id]
,[stopped_by_sid] AS [Execs_stopped_by_sid]
,[stopped_by_name] AS [Execs_stopped_by_name]
,[dump_id] AS [Execs_dump_id]
,[server_name] AS [Execs_server_name]
,[machine_name] AS [Execs_machine_name]
,[total_physical_memory_kb] AS [Execs_total_physical_memory_kb]
,[available_physical_memory_kb] AS [Execs_available_physical_memory_kb]
,[total_page_file_kb] AS [Execs_total_page_file_kb]
,[available_page_file_kb] AS [Execs_available_page_file_kb]
,[cpu_count] AS [Execs_cpu_count]
;
SQL SELECT [execution_id]
,[folder_name]
,[project_name]
,[package_name]
,[reference_id]
,[reference_type]
,[environment_folder_name]
,[environment_name]
,[project_lsn]
,[executed_as_sid]
,[executed_as_name]
,[use32bitruntime]
,CAST(CASE
WHEN [operation_type] = 101 THEN 'Deploy Project'
WHEN [operation_type] = 106 THEN 'Restore Project'
WHEN [operation_type] = 200 THEN 'Execute Package'
WHEN [operation_type] = 202 THEN 'Stop Package'
WHEN [operation_type] = 300 THEN 'Validate Project'
WHEN [operation_type] = 301 THEN 'Validate Package'
ELSE NULL
END AS VARCHAR) AS [operation_type]
,[created_time]
,[object_type]
,[object_id]
,CAST(CASE
WHEN [Status] = 1 THEN 'Created Execution'
WHEN [Status] = 2 THEN 'Running'
WHEN [Status] = 3 THEN 'Canceled'
WHEN [Status] = 4 THEN 'Failed'
WHEN [Status] = 5 THEN 'Pending Execution'
WHEN [Status] = 6 THEN 'Unexpected Termination'
WHEN [Status] = 7 THEN 'Succeeded'
WHEN [Status] = 8 THEN 'Stopping'
WHEN [Status] = 9 THEN 'Completed'
ELSE NULL
END AS VARCHAR) AS [status]
,[start_time]
,[end_time]
,[caller_sid]
,[caller_name]
,[process_id]
,[stopped_by_sid]
,[stopped_by_name]
,[dump_id]
,[server_name]
,[machine_name]
,[total_physical_memory_kb]
,[available_physical_memory_kb]
,[total_page_file_kb]
,[available_page_file_kb]
,[cpu_count]
FROM [SSISDB].[catalog].[executions]
WHERE CAST([start_time] as DATE) >= '$(StartDate)'
;
