SET FMTONLY ON (Useful in TSQL & SSIS packages for using temp tables)

As we talked earlier about ‘SET NOEXEC ON’ statement in TSQL which restricts the SQL batch statements to get executed but they only get parsed / complied. When we set NOEXEC as ‘ON’ then in the output we will get a message ‘Command completed successfully’; provided SQL statement(s) are correct else error message(s) will there. But in any case we will not be able to see the results or the column names being returned by the SQL batch or a stored procedure using NOEXEC ON statement.

Here we come up with ‘SET FMTONLY’ statement. If this is being set as ‘ON’ (by default it is OFF) at the start if a batch of SQL statements or in any stored procedure; then while executing that batch or stored procedure we will not get the actual results of that sp/batch, but instead we will get only the metadata (i.e. column names) being returned by the SQL.

CREATE PROC TestProc

AS

SELECT TOP 10 Name, ProductNumber

Color  FROM dbo.Product

Result:

 

 

 

 

 Using FMTONLY ON

SET FMTONLY ON

CREATE PROC TestProc

AS

SELECT TOP 10 Name, ProductNumber

Color  FROM dbo.Product

 

Result:

Use in SSIS

: In SSIS when we use some source to extract the data from, the source component (like OLEDB Source) is supposed to provide the complete information of all the columns being returned by the source. This information of columns is used by its following component (Transformation or destination) at design time to map it accordingly. The source component may be using some SQL statements or Stored Procedure to get the result.

All these columns information is actually detected by the source using the Metadata of the SQL query result-set.

Problem: If we are having some temp tables (#table) in the SQL query / stored procedure of the source and in the result set we are getting result (some or all columns) from those temp tables. In that case, at design time, source will not be providing information /metadata about the columns being returned & thus we will not be able to map them in its following components.

CREATE PROC TestProc

AS

SELECT TOP 10 Name, ProductNumber

Color INTO #MyTempProduct

FROM dbo.Product

SELECT * FROM #MyTempProduct

Solution: This problem of using temp tables in SSIS source can be resolved by using the FMTONLY statement & setting it as ‘ON’ on the top of the batch /sp. Doing so, sp will be executed to provide the metadata of the result set & then it can be used for mapping purposes.

SET FMTONLY ON;

EXEC TestProc

Demerit: There is one issue in using FMTONLY for getting metadata out of temp tables. When we use this statement, SP will be executed 5 times instead of only one time. This not only will slow down the performance but also it may create issue if the stored procedure is doing some modifications etc in the database; so try to avoid this work around in that case.

There are certain properties which can be set to minimize this number of executions.

Set “DelayValidation” on the Data Flow task to TRUE and the executions of the stored proc will go down from 5 to 3.
Set the “ValidateExternalMetadata” setting of the source and destination connections to be FALSE. This further reduced the executions to 2.

Another Workaround: Instead of temp table we can use table variables (@tableVar) in the queries / sp. This will provide the metadata of the result-set automatically; because we would already have declared its structure in the query/sp which is not the case with temp tables (#Table). But table variables sometimes give poorer performance than Temp tables.