Is there an SSIS connector to pick up .xel file (Extended Events)? and methodology correction

Question

I am currently working on a project to analyse the usage of dimension/measures on one of our cubes using extended events. As we are on SQL Server 2012, the extended events GUI is not available. I am using sys.fn_xe_file_target_read_file to return the file in XML format, one event per row.

I am then parsing key elements from the XML into a staging table.

select xe.TraceFileName
     , xe.TraceEvent
     , xe.EventDataXML.value('(/event/data[@name="EventSubclass"]/value)[1]', 'int')               as EventSubclass
     , xe.EventDataXML.value('(/event/data[@name="ServerName"]/value)[1]', 'varchar(50)')          as ServerName
     , xe.EventDataXML.value('(/event/data[@name="DatabaseName"]/value)[1]', 'varchar(50)')        as DatabaseName
     , xe.EventDataXML.value('(/event/data[@name="NTDomainName"]/value)[1]', 'varchar(50)')        as NTDomainName
     , xe.EventDataXML.value('(/event/data[@name="NTUserName"]/value)[1]', 'varchar(50)')          as NTUserName
     , xe.EventDataXML.value('(/event/data[@name="NTCanonicalUserName"]/value)[1]', 'varchar(50)') as NTCanonicalUserName
     , xe.EventDataXML.value('(/event/data[@name="ConnectionID"]/value)[1]', 'int')                as ConnectionID
     , xe.EventDataXML.value('(/event/data[@name="StartTime"]/value)[1]', 'datetime')              as StartTime
     , xe.EventDataXML.value('(/event/data[@name="EndTime"]/value)[1]', 'datetime')                as EndTime
     , xe.EventDataXML.value('(/event/data[@name="Duration"]/value)[1]', 'bigint')                 as Duration
     , xe.EventDataXML.value('(/event/data[@name="TextData"]/value)[1]', 'varchar(max)')           as TextData
into #List
from
(
    select [file_name]              as TraceFileName
         , object_name              as TraceEvent
         , convert(xml, event_data) as EventDataXML
    from sys.fn_xe_file_target_read_file('path\filename*.xel', null, null, null)
) xe;

The .xel files is appended to every day. I need to set up a mechanism (using SSIS) package to archive the file daily and apply the sql logic above to read the data into a temp/staging table/

However, I am struggling to find a suitable connector on SSIS. None of them allow me to pick up the .xel file from its folder.

Answer 1

TSQL is handling the reading of the file via sys.fn_xe_file_target_read_file so I think you're looking at a Data Flow Task. The source there will be an OLE DB Source component with the above query as your starting point.

That'll add those N columns to a data flow and then you can land them wherever you please. If it's all on the same server, I'd skip the data flow and use an Execute SQL Task instead.

This is part of my XE monitoring, perhaps it's of use to you

CREATE PROCEDURE dbo.ExtendedEventCaptureStop
AS
BEGIN

    SET NOCOUNT ON;

    --------------------------------------------------------------------------------
    -- Turn off our extended event
    --------------------------------------------------------------------------------
    IF EXISTS
    (
        -- When a XE is active, then there is an entry
        -- in sys.dm_xe_sessions
        SELECT
            *
        FROM
            sys.dm_xe_sessions AS DXS
            INNER JOIN 
                sys.server_event_sessions AS SES
                ON SES.name = DXS.name
        WHERE
            SES.name = N'what_queries_are_failing'
    )
    BEGIN
        -- Start the session
        ALTER EVENT SESSION what_queries_are_failing
        ON SERVER STATE = STOP;
    END

    --------------------------------------------------------------------------------
    -- Extract data from our XE
    --------------------------------------------------------------------------------
    ;
    WITH events_cte AS
    (
        SELECT
            DATEADD(mi,
            DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
            xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [err_timestamp],
            xevents.event_data.value('(event/data[@name="severity"]/value)[1]', 'bigint') AS [err_severity],
            xevents.event_data.value('(event/data[@name="error_number"]/value)[1]', 'bigint') AS [err_number],
            xevents.event_data.value('(event/data[@name="message"]/value)[1]', 'nvarchar(512)') AS [err_message],
            xevents.event_data.value('(event/action[@name="database_id"]/value)[1]', 'int') AS [database_id],
            xevents.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
            xevents.event_data,
            xevents.event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(512)') AS [username],
            'what_queries_are_failing' AS session_name
        FROM sys.fn_xe_file_target_read_file
        (
            'D:\mssql\MSSQL13.MSSQLSERVER\mssql\extendedevents\what_queries_are_failing*.xel'
        ,   'D:\mssql\MSSQL13.MSSQLSERVER\mssql\extendedevents\what_queries_are_failing*.xem'
        ,   NULL
        ,   NULL) AS fxe
        CROSS APPLY (SELECT CAST(event_data as XML) AS event_data) AS xevents
    )
    INSERT INTO
        dbo.ExtendedEventErrorCapture
    (
        err_timestamp
    ,   err_severity
    ,   err_number
    ,   err_message
    ,   database_id
    ,   sql_text
    ,   event_data
    ,   session_name
    ,   username
    )
    SELECT
        E.err_timestamp
    ,   E.err_severity
    ,   E.err_number
    ,   E.err_message
    ,   E.database_id
    ,   E.sql_text
    ,   E.event_data
    ,   E.session_name
    ,   E.username
    FROM
        events_cte AS E;

    --------------------------------------------------------------------------------
    -- Get rid our extended event files only if the XE is turned off
    -- or no longer exists
    --------------------------------------------------------------------------------
    IF NOT EXISTS
    (
        SELECT
            1
        FROM
            sys.dm_xe_sessions AS DXS
            INNER JOIN 
                sys.server_event_sessions AS SES
                ON SES.name = DXS.name
        WHERE
            SES.name = N'what_queries_are_failing'

        UNION ALL
        SELECT
            1
        FROM
            sys.server_event_sessions AS SES
        WHERE
            SES.name = N'what_queries_are_failing'

    )
    BEGIN
        -- Assumes you've turned on xp_cmdshell
        EXECUTE sys.xp_cmdshell'del D:\mssql\MSSQL13.MSSQLSERVER\mssql\extendedevents\what_queries_are_failing*.xe*';
    END

END
GO

Changing out the file location and the session name, it stop the XE so we don't have to worry about the file being locked. I parse the data out of the XE and into my table and then delete any XE files left in the folder.

Oh, but now my XE session is stopped, I should restart it. That's this procedure

CREATE PROCEDURE dbo.ExtendedEventCaptureStart
AS
BEGIN

    SET NOCOUNT ON;
    --------------------------------------------------------------------------------
    -- Create the table to store out the XE data
    --------------------------------------------------------------------------------
    IF NOT EXISTS
    (
        SELECT
            *
        FROM
            sys.schemas AS S
            INNER JOIN 
                sys.tables AS T
            ON T.schema_id = S.schema_id
        WHERE
            T.name = 'ExtendedEventErrorCapture'
            AND S.name = 'dbo'
    )
    BEGIN
        CREATE TABLE 
            dbo.ExtendedEventErrorCapture
        (
            err_timestamp datetime2(7) NULL
        ,   err_severity bigint NULL
        ,   err_number bigint NULL
        ,   err_message nvarchar(512) NULL
        ,   database_id int NULL
        ,   sql_text nvarchar(MAX) NULL
        ,   event_data xml NULL
        ,   session_name sysname
        ,   username nvarchar(512)
        )
        -- This is only vaild for a Develper/Enterprise edition license
        WITH (DATA_COMPRESSION = PAGE);
    END

    --------------------------------------------------------------------------------
    -- Create the extended event to keep track of bad sql queries
    --------------------------------------------------------------------------------
    IF NOT EXISTS
    (
        SELECT
            *
        FROM
            sys.server_event_sessions AS SES
        WHERE
            SES.name = N'what_queries_are_failing'
    )
    BEGIN
        --Create an extended event session
        CREATE EVENT SESSION
            what_queries_are_failing
        ON SERVER
        ADD EVENT sqlserver.error_reported
        (
            ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.username)
            WHERE ([severity]> 10)
        )
        ADD TARGET package0.asynchronous_file_target
        (set filename = 'D:\mssql\MSSQL13.MSSQLSERVER\mssql\extendedevents\what_queries_are_failing.xel' ,
            metadatafile = 'D:\mssql\MSSQL13.MSSQLSERVER\mssql\extendedevents\what_queries_are_failing.xem',
            max_file_size = 512,
            increment = 16,
            max_rollover_files = 5)
        WITH (MAX_DISPATCH_LATENCY = 5SECONDS);

    END

    --------------------------------------------------------------------------------
    -- Turn on the extended event
    --------------------------------------------------------------------------------
    IF NOT EXISTS
    (
        -- When a XE is active, then there is an entry
        -- in sys.dm_xe_sessions
        SELECT
            *
        FROM
            sys.dm_xe_sessions AS DXS
            INNER JOIN 
                sys.server_event_sessions AS SES
                ON SES.name = DXS.name
        WHERE
            SES.name = N'what_queries_are_failing'
    )
    BEGIN
        -- Start the session
        ALTER EVENT SESSION what_queries_are_failing
        ON SERVER STATE = START;
    END
END

So now my SSIS package would be a single Execute SQL Task with two statements inside

EXECUTE dbo.ExtendedEventCaptureStop;
EXECUTE dbo.ExtendedEventCaptureStart;