-1

I have a SSIS package which uses a stored procedure with dynamic SQL as OLEDB source. When I execute the package, I get this error

Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'exec sp_executesql @sqlStr' in procedure 's_offc_cap_st_rpt_efile' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.".

The Oledb source configuration looks like this:

enter image description here

What is the reason for this error? Could someone help me find a solution for this issue?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bmsqldev
  • 2,627
  • 10
  • 31
  • 65

1 Answers1

1

SSIS is always required select column list to create the meta data so that column names in SSIS can be mapped to appropriate output\destination.

Here you are using dynamic sql which will compile and run at run time it self. If you want to remove this error. I would recommend create table variable and get output of your dynamic sql into this table variable and write select * from @tablevaribale. So that procedure will return the column list to the ssis.

Rahul Neekhra
  • 780
  • 1
  • 9
  • 39
  • the dynamic sql is created inside a stored procedure..if i create a table variable inside the stored procedure will it work – bmsqldev Jan 03 '17 at 15:32
  • 1
    @bmsqldev yes once you create table variable and dump output of dynamic sql and then write select statement on that table variable that will work. Please let me know in case you find still issue – Rahul Neekhra Jan 04 '17 at 06:42