Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)'
;
I have tried that also, please see prior comment. I tried just a few fields and got the same result.
Hard to guess without seeing your actual system, but other things to check could include :
Yes to your first question. I am working on the second - but wouldn't it give an error if the Qlik credentials couldn't access the data?
Depends on the security set up on the database and I am clutching at straws blindly
But to quote Arthur Conan Doyle :
Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth.
I had to make the Qlik user account a SysAdmin on the sql database. Once I did that, it pulled the data. Thanks!