Extract result set from a execute sql task and store it in a excel file?

Question

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.

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
– KeithL, Commented May 19, 2021 at 16:48
@KeithL would it work for Exec [StoredprocedureName] ?, ? ? OUTPUT as well. Also, do you have any link to help me with this?code — Arpit Chinmay
– Arpit Chinmay, Commented May 19, 2021 at 17:22

Gigga · Accepted Answer · 2021-05-22 10:17:01Z

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/

answered May 22, 2021 at 10:17

Gigga

5772 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract result set from a execute sql task and store it in a excel file?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related