Export data from sql table to excel without Data flow task

Question

How can I export a table from sql to an excel using SSIS script? In addition I have to maintain an excel template so everytime it matches the header name and export data into it.

you can using sql-server's import/export wizard to export data from a table to excel file. — bmsqldev, Mar 23 '16 at 06:40

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

There are two ways (as I know) to get data into Excel file from SQL Server database.

SSIS Package
Simply import data from SQL Server to Excel file

In the first approach, after creating the SSIS package, you can add a schedule for the SSIS package execution using SQL Server agent.

OR

Import the data into Excel from SQL Server, using the tab DATA -> Existing Connection -> SQL Server. You can write specific SQL to import data or simply import the table. Adding more, using the Refresh All under the same DATA tab, you can refresh the data anytime & be in sync with the SQL Server table.

I am trying to do it using c# in script task – SaNa3819 Mar 23 '16 at 07:14 — SaNa3819, Mar 23 '16 at 07:14

score 0 · Answer 2 · edited May 23 '17 at 10:28

You can use the tsql query/stored procedure with openrowset in execut SQL task. Here is a link to another post with examples how to do it (use Openrowset)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

If this is not an option then maybe c# or Vb.net with usage of datatables. You can define the structure of the datatable, I.e. Columns their data types (.net types). Then you can save a datatable to excel. Here is a link which hopefully will help you.

Convert datatable to excel 2007(.xlsx)

Export data from sql table to excel without Data flow task

2 Answers2