3

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)
 )
)
Hadi
  • 36,233
  • 13
  • 65
  • 124
Suhail Abdul Rehman Chougule
  • 2,369
  • 2
  • 12
  • 23
  • I really recommend you properly quote your dynamic object names (use `QUOTENAME`). Also [Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) – Thom A Feb 22 '20 at 21:24
  • @Larnu "I tried by giving lengths to the variables like below, Still the result and please kindly let me know how to use dynamic object names i.e. QUOTENAME – Suhail Abdul Rehman Chougule Feb 24 '20 at 14:58
  • It works fine in MSSQL Query the problem is in OLEDB Command – Suhail Abdul Rehman Chougule Feb 24 '20 at 15:00
  • 1
    What are you trying to do with this code? As it sits, it's just returning the two variable values you declare at the top. Surely there's more to it, but what? – Eric Brandt Feb 25 '20 at 19:15

2 Answers2

3

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:

Community
  • 1
  • 1
Hadi
  • 36,233
  • 13
  • 65
  • 124
0

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.

Rider Harrison
  • 441
  • 6
  • 12