0

I have a execute SQL task, that needs two input variables and a output variable to run. The SP returns a table that i'm storing in ResultSet parameter. I want to store the values in this ResultSet parameter in a excel file. From my internet research(similar SO question, Export data from SQL Server to Excel), i found a few articles where they create a ADO.NET sources. Run a simple select query inside it and use excel destination. In my case, the parameters to SP have to be passed as variable.Hence, i can't use the harcoded SQL queries. Can anyone please point me in the current direction? My knowledge of SSIS is very limited to be doing a good google search.

Arpit Chinmay
  • 313
  • 5
  • 16
  • 2
    The easiest way to do this is in a data flow. Use a SQL Server source (you can use a question mark (?) as a placeholder to map variables) and an excel destination (although I would recommend csv). example select * from table where column = ?, Parameters are set in order of appearance in the sql – KeithL May 19 '21 at 16:48
  • 1
    @KeithL would it work for `Exec [StoredprocedureName] ?, ? ? OUTPUT` as well. Also, do you have any link to help me with this?`code` – Arpit Chinmay May 19 '21 at 17:22

1 Answers1

1

If the question is, how to store values from ResultSet parameter to Excel, you have to create a custom data source: https://www.timmitchell.net/post/2015/04/20/using-the-ssis-object-variable-as-a-data-flow-source/

Gigga
  • 555
  • 2
  • 10