Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hlines_usacs
Creator II
Creator II

SQL Query from SSISDB Database 0 Lines Fetched

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)'

;

connection to ssisdb.jpg

14 Replies
hlines_usacs
Creator II
Creator II
Author

I have tried that also, please see prior comment. I tried just a few fields and got the same result.

Anonymous
Not applicable

Hard to guess without seeing your actual system, but other things to check could include :

  • Is the Qlik Data Connection set up correctly and pointing at the correct database ?
  • Are the database credentials used by Qlik the same as those used directly against the database ?
hlines_usacs
Creator II
Creator II
Author

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?

Anonymous
Not applicable

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.

hlines_usacs
Creator II
Creator II
Author

I had to make the Qlik user account a SysAdmin on the sql database. Once I did that, it pulled the data. Thanks!