Export single sql table into multiple Excel sheets with single Excel file using ssis

Question

I'm trying to Export single sql table into multiple excel sheets with single Excel file using ssis. Scenario:

have region wise data in SQL table. Every day I'll get data from client region wise.
have to export this data into different Excel sheets like region1,region2 in same Excel file
also everyday while running ssis package previous day data should get truncated and new value has to be inserted.

So what have you tried already? What, exactly, are you stuck on? — Rich Benner, Nov 29 '16 at 14:06
i have used data flow task.in that i have added source as sql server,then added conditional splitting for categorizing sheets(region1,region2...) and then added three excel destination which points out same excel connection manager....after running package i can able to get the data region wise in different sheets(region1,region2,region3) in single excel file.now the problem is when i run next time data getting appended with the previous run data.i want to truncate the old data and have to insert new data when i run package daily basis. — Jagan, Nov 29 '16 at 14:54
I trimmed urgent begging from your prior question. Having had to do it again, and trim txtspk too, I have downvoted. Please take more care when writing your questions, use real words as much as you are able, and don't beg. Thanks. — halfer, Nov 29 '16 at 15:19

score 0 · Answer 1 · edited Nov 29 '16 at 23:32

0

INSERT INTO OPENROWSET('MICROSOFT.ACE.OLEDB.12.0', 'EXCEL
12.0;DATABASE=D:\DOWNLOADS\XLFILE.XLSX;', 'SELECT COLUMNNAMES FROM [HT$]') 
SELECT COLUMNNAMES 
FROM [DBRD].[DBO].[TABLE]

edited Nov 29 '16 at 23:32

CDspace

2,639
18
30
36

answered Nov 29 '16 at 14:15

Krish

39
8

can u plz elaborate wher i hav to do this – Jagan Nov 29 '16 at 14:56
It's always better to give some explanation/context for code samples provided in answers to make them more understandable. – EJoshuaS - Stand with Ukraine Nov 29 '16 at 16:17
In code [HT$] is a sheet name ypu can change as region1 – Krish Nov 30 '16 at 12:23
You can use where statement in Select query like where columvalue='region1' – Krish Nov 30 '16 at 12:24
Thanku Krish ill do and let you know – Jagan Dec 01 '16 at 03:37

score 0 · Answer 2 · edited May 23 '17 at 12:01

Create an Excel file named testing having the headers same as that of table columns and use these queries

1 Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use

EXEC sp_makewebtask 
    @outputfile = 'd:\testing.xls', 
    @query = 'Select * from Database_name..SQLServerTable', 
    @colheaders =1, 
    @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

(Now you can find the file with data in tabular format)

Finally, this is pretty comprehensive.

How do you transfer or export SQL Server 2005 data to Excel

SP not found in SQL Server 2012. Any alternative SP in SQL'12. — Pugal, Jul 26 '19 at 08:46

Export single sql table into multiple Excel sheets with single Excel file using ssis

2 Answers2