Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Skip the ticket, Chat with Qlik Support instead for instant assistance.
Showing results for 
Search instead for 
Did you mean: 
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.


LET StartDate = Date(Today()-10,'MM-DD-YYYY');


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]













          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]





          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]
















  FROM [SSISDB].[catalog].[executions]

  WHERE CAST([start_time] as DATE) >= '$(StartDate)'


connection to ssisdb.jpg

1 Solution

Accepted Solutions
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 ?

View solution in original post

14 Replies
Not applicable

I'd suggest you double check your date formats in your where clause :

  WHERE CAST([start_time] as DATE) >= '$(StartDate)'

Creator II
Creator II

If i remove the where clause, the same thing happens. 0 Line Fetched.

Not applicable

What happens if remove Qlik from the scenario and run the SQL directly against the database ?

Creator II
Creator II

It runs successfully and returns 5508 records.

Creator II
Creator II

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.


Try to use

SET StartDate = Date(Today()-10,'MM-DD-YYYY');

instead of:

LET StartDate = Date(Today()-10,'MM-DD-YYYY');

Creator II
Creator II

I don't believe the date variable is the issue. For now, I completely removed it. I still get 0 Lines Fetched.


Sorry, I was wrong here. It couldn't help.

Not applicable

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.