Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hlines_usacs
Contributor 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

1 Solution

Accepted Solutions

Re: SQL Query from SSISDB Database 0 Lines Fetched

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 ?
14 Replies

Re: SQL Query from SSISDB Database 0 Lines Fetched

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

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

hlines_usacs
Contributor II

Re: SQL Query from SSISDB Database 0 Lines Fetched

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

Re: SQL Query from SSISDB Database 0 Lines Fetched

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

hlines_usacs
Contributor II

Re: SQL Query from SSISDB Database 0 Lines Fetched

It runs successfully and returns 5508 records.

hlines_usacs
Contributor II

Re: SQL Query from SSISDB Database 0 Lines Fetched

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.

tomasz_tru
Valued Contributor

Re: SQL Query from SSISDB Database 0 Lines Fetched

Try to use

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

instead of:

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

hlines_usacs
Contributor II

Re: SQL Query from SSISDB Database 0 Lines Fetched

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

tomasz_tru
Valued Contributor

Re: SQL Query from SSISDB Database 0 Lines Fetched

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

Re: SQL Query from SSISDB Database 0 Lines Fetched

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.