SSIS An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005, The metadata could not be determined

Question

I am getting the following error though I have given the with result sets the dynamic SQL. Yet it asks for the same.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'EXEC sp_executesql @ExecSQL' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.".

Error: 0xC0202080 at Ingest The CSV File - Flow, OLE DB Command 1 [281]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

The below code works fine in MS SQL Management Studio Query. But it gives error in SSIS

DECLARE @tablename nvarchar(200),  @tablevalue nvarchar(1)

SELECT @tablename =CAST(FLOOR(RAND()*(100000-5+1)+5) as nvarchar)

Select @tablevalue='0'

DECLARE   @ExecSQL NVARCHAR(max)

SET @ExecSQL = 'SELECT d.tablename, d.tablevalue INTO mws_ssis_cust_senti_integration'+ @tablename+
+ ' from  ( select ''' + 'mws_ssis_cust_senti_integration' + @tablename + ''' as tablename, ''' + @tablevalue + ''' as tablevalue ) as d '


EXEC sp_executesql @ExecSQL

Set @tablename = N'mws_ssis_cust_senti_integration'+ cast(@tablename as nvarchar)

Set  @ExecSQL = 'select * from  ' + @tablename

EXEC sp_executesql @ExecSQL
WITH RESULT SETS
(
 (
 tablename varchar(max), tablevalue varchar(1)
 )
)

Answer 1

Why you should use this command within an OLE DB Command?!

Referring to the official documentation:

The OLE DB Command transformation runs an SQL statement for each row in a data flow. For example, you can run an SQL statement that inserts, updates, or deletes rows in a database table.

It is not used for select statements. I think you are looking for Execute SQL Task or an OLE DB Source with SQL Command access mode or Lookup transformation.

1- Execute SQL Task

You can store the query result within an SSIS variable of type object. There are plenty of articles illustrating this process:

• SSIS Basics: Using the Execute SQL Task to Generate Result Sets
• Execute SQL Tasks in SSIS: Output Parameters vs Result Sets

Note that if you have the @tablename and @tablevalue variables in SSIS, you can use expressions to build this statement in a simpler way and to execute it later.

2 - OLE DB Source

You can generate rows from a similar command using OLE DB Source, you can check the following answer since it provides some helpful information about using resultset within OLE DB source:

• Execute Stored Procedure with multiple result sets

Also, you can check the following article for some helpful information:

• SSIS OLE DB Source: SQL Command vs Table or View

3 - Lookup transformation

I am not sure if it supports a similar query, but you can read more about this component in the following links:

• An Overview of the LOOKUP TRANSFORMATION in SSIS

Answer 2

Have you tried wrapping the entire statement inside a stored procedure and then calling that stored procedure from SSIS?

Pure shot in the dark here but in the past when I have moved complex sql statements, especially those involving the creation of temporary tables, outside of SSMS into production environments (e.g. inside an application or a ssrs report) the queries would fail for no clear reason. Once I moved the actual sql into a sp the errors magically disappeared with 0 changes to the actual mechanics of the query.