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;
