14 Replies Latest reply: May 10, 2017 3:03 PM by Hannah Lines RSS

    SQL Query from SSISDB Database 0 Lines Fetched

    Hannah Lines

      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