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');
,[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]
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'
END AS VARCHAR) AS [operation_type]
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'
END AS VARCHAR) AS [status]
WHERE CAST([start_time] as DATE) >= '$(StartDate)'
Solved! Go to Solution.
Hard to guess without seeing your actual system, but other things to check could include :
I tried just loading 4 fields that are populated for all records - still 0 lines fetched.
I have also tried loading the data from SSISDB to another database and using a "staging" table. This loads all the records without issue. The purpose of this app is going to a status dashboard and needs to be as close to "real time" as possible. I would like to not have to run a process to reload the staging table and then pull from that.
What happens if you make the SQL really simple? Maybe just :
Select [execution_id] FROM [SSISDB].[catalog].[executions] ;
And run that direct against the database and then via Qlik.