1

Problem is as simple Client want to store executable sql task queries result in Excel file.I have set full result set as a object but cant consume that object anywhere.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Ankush
  • 347
  • 2
  • 10

2 Answers2

1

You need to export data from SQL Server to Excel using SSIS, right? In SSIS, you need to create a data flow task. Inside the data flow task you need an OLEDB data source or an ADO.NET data source. Then you need an Excel destination. Connect the source and destination and configure the mappings and other settings. More detailed instructions can be found in this tutorial: https://codingsight.com/export-data-from-sql-server-to-excel-and-text-file-via-using-ssis-package/

dangeruss
  • 102
  • 6
  • thanks for help but question is little different ..I want to store ExecutableSQL task result set in Excel table not SQLServer data in Excel file... – Ankush Sep 06 '19 at 12:00
  • OK, it sounds like you have an Execute SQL Task on the control flow of your SSIS package, and you are storing the results in an object variable, right? And you want to write the object variable to an Excel file? You may be able to find a way to make this approach work, but it is not the best practice to write data to Excel. It will probably require referencing a 3rd party Excel library and creating the Excel file in C# using a Script Task. – dangeruss Sep 06 '19 at 12:19
  • The best practice for writing data from SSIS to Excel, is to use a data flow task and the Excel destination like I suggested above. If this is a new package you are creating, I would definitely recommend going that route. – dangeruss Sep 06 '19 at 12:20
  • @Ankush exporting is the job of the dataflow task, no matter the destination. It's not a matter of best practice, it's a matter of using the correct tool. The *primary* job of SSIS is to execute dataflows. – Panagiotis Kanavos Sep 06 '19 at 12:32
  • @RussW:If i explain the problem in more simplier way to store more than query (one select statement) result in excel destination thats it.....yes you understood me right we want to store executable sql task resultset object in Excel – Ankush Sep 06 '19 at 17:46
1

Add a Data Flow Task that contains a Script Component Source where you generate output rows from the recordset and an Excel Destination:

On the other hand, you can simply use the SQL Command that you are executing in the Execute SQL Task in an OLE DB Source which is more simple.

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
  • :Yes I already done same thing take OLEDB souce as a sql command and write one script there and store result in Excel destination ..it was perfect...there is no problem but In this way we can only execute single sql command (Select statement) not more than one sql command...thats by forcefully we have to use ExecuteSql task to execute more than one query at a time... – Ankush Sep 06 '19 at 17:23
  • But unable to store Executesql task object in Excel file destination.Thanks Yahfoufi for giving precious time – Ankush Sep 06 '19 at 17:29
  • 1
    @Ankush have uou checked the links mentioned in this answer? – Hadi Sep 06 '19 at 19:40