Dynamic SQL in OLEDB source component in SSIS

The metadata could not be determined because statement contains dynamic SQL

When we use a batch of T-SQL statements which contains dynamic SQL or uses temporary tables inside, in an OLEDB source component placed in a Data Flow Task of a SSIS package, we get an error during column names extraction because the source component gets failed to fetch the metadata (columns and their data types) for the given T-SQL batch.

In this post, we are going to learn how we can use a dynamic SQL statement or a batch of T-SQL statement which does not expose its metadata directly, in a source component which could be an OLEDB source, or an ODBC source being used to fetch data from the SQL Server.

Follow below steps to generate the error:

• Create an integration services project in visual studio.
• Add a Data Flow Task in the package.
• Create an OLEDB connection manager pointing to the SQL Server.
• Drag and drop an OLEDB source component from the toolbox to the Data Flow Task.
• Assign the created OLEDB connection in connection manager section of the OLEDB source.
• In Data Access Mode, select SQL Command.
• In SQL command text, use below command which uses a dynamic SQL string to get the dummy data:

DECLARE @Var INT = 100
DECLARE @SQLStatement NVARCHAR(1000)
SET @SQLStatement = N'CREATE TABLE #tbl 
(
 Value INT
)

INSERT INTO #tbl (Value)
VALUES(@Var)

SELECT * FROM #tbl'
EXEC SP_EXECUTESQL @SQLStatement, N'@Var INT', @Var

• Now, click on the preview button located just below the SQL command text box or on Columns tab being used to get the list of columns and we will get the below metadata error:

How to use dynamic SQL statement which uses temporary table, in a source component of SSIS package

Error “The metadata could not be determined because statement ‘EXEC SP_EXECUTESQL @SQLStatement,  N’@Var INT’, @Var’ contains dynamic SQL.” occurs because the source component gets failed to fetch the column metadata. Instead of the above error, we also might get an error message as “No column information was returned by the SQL command“.

To overcome these errors, considering the version of the SQL Server, we can follow different approaches to fix them. Lets discuss these approaches in more detail:

Approach 1 – SQL Server 2008 R2 and below versions of SQL Server

In above metadata error, we can see that the source component suggests “Consider using the WITH RESULT SETS clause to explicitly describe the result set”, however, WITH RESULT SETS clause is only available on and after SQL Server 2012 which is a subject of discussion of Approach 2.

For SQL Server 2008 R2 or below versions, we need to create a contract query which returns the same set of metadata as our actual query will. Then, we wrap the contract query inside an IF code block which always results a false output to avoid the execution of the contract query statement. By exposing the metadata with the help of a static SQL statement design time, the contract query helps the source component to understand the metadata.

Below is the contract query created for the above statement:

IF(1 = 0)
BEGIN
SELECT '' AS Value
END

So, instead of above code which yields metadata error, we can use the below statement if we are dealing with SQL Server 2008 R2 or below version of SQL Server:

--Contract query goes here
IF(1 = 0) --Always false condition
BEGIN
 SELECT '' AS Value
END

--Main query goes here
DECLARE @Var INT = 100
DECLARE @SQLStatement NVARCHAR(1000)
SET @SQLStatement = N'CREATE TABLE #tbl 
 (
 Value INT
 )

 INSERT INTO #tbl (Value)
 VALUES(@Var)

 SELECT * FROM #tbl'
EXEC SP_EXECUTESQL @SQLStatement, N'@Var INT', @Var

This approach is safer than using “SET FMTONLY OFF” at beginning of the SQL batch which actually executes the given SQL batch to get the metadata. Apart from the performance perspective, actually executing the entire SQL batch or stored procedure for preview purpose only, might outcome some serious problem especially if the code modifies some table data.

Approach 2 – SQL Server 2012 and above versions of SQL Server

If we are dealing with SQL Server 2012 or after versions of SQL Server, the contract query approach will not help and we would need to use the “WITH RESULT SETS” clause as below:

DECLARE @Var INT = 100
DECLARE @SQLStatement NVARCHAR(1000)
SET @SQLStatement = N'CREATE TABLE #tbl 
 (
 Value INT
 )

 INSERT INTO #tbl (Value)
 VALUES(@Var)

 SELECT * FROM #tbl'
EXEC SP_EXECUTESQL @SQLStatement, N'@Var INT', @Var
--Use WITH RESULT SETS to declare metadata
WITH RESULT SETS
(
 (
 Value VARCHAR(100)
 )
)

If you want to read more about “WITH RESULT SETS”, you can visit my previous blog post “Change stored procedure output column names and data types“.

Thanks for the reading. Please share your inputs.