0

I am trying to call a stored procedure in SSIS OLE- DB DataSource (My Datasource would be SQL Server 2012).

I tried a procedure call SQL statement under SQL Command option but when I click the preview button I am getting an error. enter image description here

Please guide me how to resolve this error. I goolged but nothing works for me.

StackUser
  • 5,370
  • 2
  • 24
  • 44
  • Does your `stored procedure` - Prc_GetAppointmentDetailsBasedOnNetwork has `SELECT` as it's last statement or putting it differently does your `stored procedure` returns a dataset? – Abhishek Mar 08 '16 at 12:33

1 Answers1

0

I think the issue you are having is in SSIS often takes the first Select statement it finds and tries to validate it for column names, this happens especially with very big procedures. The trick I have found to get this to work is, right off the bat throw something like : 

IF 1 = 0 
Begin
Select all columns you want
END

This code will never get execute but it hints SSIS to make those columns the ones in the data flow. Just be sure to update this list as you update your last select.

Holmes IV
  • 1,673
  • 2
  • 23
  • 47