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


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'


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
 tablename varchar(max), tablevalue varchar(1)
asked on Stack Overflow Feb 22, 2020 by Suhail Abdul Rehman Chougule • edited Feb 25, 2020 by Hadi

2 Answers


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:

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:

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

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:

answered on Stack Overflow Feb 25, 2020 by Hadi • edited Feb 25, 2020 by Hadi

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.

answered on Stack Overflow Feb 25, 2020 by Rider Harrison

User contributions licensed under CC BY-SA 3.0