SSIS not working with temp tables

Received this fine error message during ETL?:
The metadata could not be determined because statement ” in procedure ” uses a temp table.

If you are working on SSIS 2012, then it uses system stored procedure sp_describe_first_result_set to fetch the metadata of the tables and it does not support temporary tables. But you can go for other options like table variables and CTEs which are going to work fine.
In my case, since I’m a bit stubborn I looked for another solution and found a few:



Solution 1:
An option is to use a SET FMTONLY ON command in front of your actual query to send a fake “First result set” to SSIS with your correct column structure. So you can do something like:

SET FMTONLY ON
select 0 as a, 1 as b, 'test' as C, GETDATE() as D
SET FMTONLY OFF

select a, b, c, d from ##TempTable

When SSIS runs sp_describe_first_result_set, it will return the metadata and column names of your FMTONLY command, and won’t complain about not being able to determine the metadata of your temp table because it won’t even try.



Solution 2:
And the best solution is this one in my opinion, simply provide SSIS the metadata up front like:

EXEC [dbo].[usp_SSIS_DataSet] @Date = '2012-01-01'
WITH RESULT SETS
(
(
[DateID] [int] NOT NULL,
[Sales] [decimal](16, 4) NOT NULL
)
)

NOTE: Don’t know why, but syntax wise it needs the round brackets twice.
You can use this in your OLE DB Source component as a query (or in a variable).








Advertisements

Who is the owner/creator of the #Temp tables!?

I found this great post by Aaron Bertrand with the title ‘Dude, who owns that #temp table?

He explains how you could run a trace on SQL Server 2012 and figure out who’s actually creating those #Temp tables! 🙂

1) Create an Extended Event (by using this SQL script):
NOTE: Make sure to verify that ‘c:\temp‘ does exist on your server


CREATE EVENT SESSION [TempTableCreation] ON SERVER
ADD EVENT sqlserver.object_created
(
ACTION
(
-- you may not need all of these columns
sqlserver.session_nt_username,
sqlserver.server_principal_name,
sqlserver.session_id,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.sql_text
)
WHERE
(
sqlserver.like_i_sql_unicode_string([object_name], N'#%')
AND ddl_phase = 1 -- just capture COMMIT, not BEGIN
)
)
ADD TARGET package0.asynchronous_file_target
(
SET FILENAME = 'c:\temp\TempTableCreation.xel',
-- you may want to set different limits depending on
-- temp table creation rate and available disk space
MAX_FILE_SIZE = 32768,
MAX_ROLLOVER_FILES = 10
)
WITH
(
-- if temp table creation rate is high, consider
-- ALLOW_SINGLE/MULTIPLE_EVENT_LOSS instead
EVENT_RETENTION_MODE = NO_EVENT_LOSS
);
GO
ALTER EVENT SESSION [TempTableCreation] ON SERVER STATE = START;

2) Verify that the trace is running:
Head over to SSMS and verify that the Extended Event was created and is running at this moment:

sql-server-2012-extended-events-who-is-creating-temp-tables-temptablecreation

Also open the ‘Watch Live Data‘-window:

sql-server-2012-extended-events-who-is-creating-temp-tables-watch-live-data

3) Analyzing the data:

Run this query to analyse which #Temp tables currently exist and who made them:


DECLARE @delta INT = DATEDIFF(MINUTE, SYSUTCDATETIME(), SYSDATETIME());

;WITH xe AS
(
SELECT
[obj_name] = xe.d.value(N'(event/data[@name="object_name"]/value)[1]',N'sysname'),
[object_id] = xe.d.value(N'(event/data[@name="object_id"]/value)[1]',N'int'),
[timestamp] = DATEADD(MINUTE, @delta, xe.d.value(N'(event/@timestamp)[1]',N'datetime2')),
SPID = xe.d.value(N'(event/action[@name="session_id"]/value)[1]',N'int'),
NTUserName = xe.d.value(N'(event/action[@name="session_nt_username"]/value)[1]',N'sysname'),
SQLLogin = xe.d.value(N'(event/action[@name="server_principal_name"]/value)[1]',N'sysname'),
HostName = xe.d.value(N'(event/action[@name="client_hostname"]/value)[1]',N'sysname'),
AppName = xe.d.value(N'(event/action[@name="client_app_name"]/value)[1]',N'nvarchar(max)'),
SQLBatch = xe.d.value(N'(event/action[@name="sql_text"]/value)[1]',N'nvarchar(max)')
FROM
sys.fn_xe_file_target_read_file(N'C:\temp\TempTableCreation*.xel',NULL,NULL,NULL) AS ft
CROSS APPLY (SELECT CONVERT(XML, ft.event_data)) AS xe(d)
)
SELECT
DefinedName = xe.obj_name,
GeneratedName = o.name,
o.[object_id],
xe.[timestamp],
o.create_date,
xe.SPID,
xe.NTUserName,
xe.SQLLogin,
xe.HostName,
ApplicationName = xe.AppName,
TextData = xe.SQLBatch,
row_count = x.rc,
reserved_page_count = x.rpc
FROM xe
INNER JOIN tempdb.sys.objects AS o
ON o.[object_id] = xe.[object_id]
AND o.create_date >= DATEADD(SECOND, -2, xe.[timestamp])
AND o.create_date <= DATEADD(SECOND, 2, xe.[timestamp])
INNER JOIN
(
SELECT
[object_id],
rc = SUM(CASE WHEN index_id IN (0,1) THEN row_count END),
rpc = SUM(reserved_page_count)
FROM tempdb.sys.dm_db_partition_stats
GROUP BY [object_id]
) AS x
ON o.[object_id] = x.[object_id]
ORDER BY xe.[timestamp]


sql-server-2012-extended-events-who-is-creating-temp-tables-analyse-results-with-query

See all #Temp tables which existed?
After the user dropped the #Temp table, it will no longer be listed here.
But if you do want to see every #Temp tables which was created in a certain period, replace the INNER-joins with LEFT-joins and voila!:


DECLARE @delta INT = DATEDIFF(MINUTE, SYSUTCDATETIME(), SYSDATETIME());

;WITH xe AS
(
SELECT
[obj_name] = xe.d.value(N'(event/data[@name="object_name"]/value)[1]',N'sysname'),
[object_id] = xe.d.value(N'(event/data[@name="object_id"]/value)[1]',N'int'),
[timestamp] = DATEADD(MINUTE, @delta, xe.d.value(N'(event/@timestamp)[1]',N'datetime2')),
SPID = xe.d.value(N'(event/action[@name="session_id"]/value)[1]',N'int'),
NTUserName = xe.d.value(N'(event/action[@name="session_nt_username"]/value)[1]',N'sysname'),
SQLLogin = xe.d.value(N'(event/action[@name="server_principal_name"]/value)[1]',N'sysname'),
HostName = xe.d.value(N'(event/action[@name="client_hostname"]/value)[1]',N'sysname'),
AppName = xe.d.value(N'(event/action[@name="client_app_name"]/value)[1]',N'nvarchar(max)'),
SQLBatch = xe.d.value(N'(event/action[@name="sql_text"]/value)[1]',N'nvarchar(max)')
FROM
sys.fn_xe_file_target_read_file(N'C:\temp\TempTableCreation*.xel',NULL,NULL,NULL) AS ft
CROSS APPLY (SELECT CONVERT(XML, ft.event_data)) AS xe(d)
)
SELECT
DefinedName = xe.obj_name,
GeneratedName = o.name,
o.[object_id],
xe.[timestamp],
o.create_date,
xe.SPID,
xe.NTUserName,
xe.SQLLogin,
xe.HostName,
ApplicationName = xe.AppName,
TextData = xe.SQLBatch,
row_count = x.rc,
reserved_page_count = x.rpc
FROM xe
LEFT JOIN tempdb.sys.objects AS o
ON o.[object_id] = xe.[object_id]
AND o.create_date >= DATEADD(SECOND, -2, xe.[timestamp])
AND o.create_date <= DATEADD(SECOND, 2, xe.[timestamp])
LEFT JOIN
(
SELECT
[object_id],
rc = SUM(CASE WHEN index_id IN (0,1) THEN row_count END),
rpc = SUM(reserved_page_count)
FROM tempdb.sys.dm_db_partition_stats
GROUP BY [object_id]
) AS x
ON o.[object_id] = x.[object_id]
ORDER BY xe.[timestamp]


sql-server-2012-extended-events-who-is-creating-temp-tables-analyse-results-with-query-after-drop-table

After a few seconds the ‘Watch Live Data‘-window will start showing results (or after dropping the table, can’t remember…):

sql-server-2012-extended-events-who-is-creating-temp-tables-watch-live-data-screen