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 : 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) ) )
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.
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.
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:
I am not sure if it supports a similar query, but you can read more about this component in the following links:
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.
User contributions licensed under CC BY-SA 3.0