Dynamically creating Excel Sheets and populating data from SQL server tables

I came across this very interesting question on Microsoft Forums where in the developer wanted to Create an SSIS package which Loops thought a set of tables and create Excel files on the fly and Load data into it.

There can be many ways to solve this including building a dot net custom component which creates excel sheet on the fly and then dynamically loads the data into it. Though I came across this cool SP that does this job really well and in very quick steps.

 

Here is how you can do this —

 

—        Enable SP_MAKEWEBTASK
3	exec sp_configure ‘Web Assistant Procedures’, 1 — Configuration option ‘Web Assistant Procedures’ changed from 0 to 1. Run the RECONFIGURE statement to install. RECONFIGURE
4	RECONFIGURE
5
6	–RUN THIS TO CREATE A FILE DUNAMICALLY AND LOAD DATA INTO IT
7
8	EXEC sp_makewebtask
9	@outputfile = ‘d:\testing.xls’,
10	@query = ‘Select * from sys.tables’,
11	@colheaders =1,
12	@FixedFont=0,@lastupdated=0,@resultstitle=‘Testing details’
13
14
15	–NOW WE KNOW HOW TO DYNAMICALLY CREATE A XLS FILE AND LOAD DATA FROM TABLES LETS TRY PUTTING THIS IN A SP AND RUNNING IT BY PARAMETERS
16
17	ALTER PROCEDURE CREATE_AND_LOAD_EXCEL
18
19	@ioutputfile       VARCHAR(2000)
20	,@itable            VARCHAR(MAX)
21	,@iresultstitle     VARCHAR(2000)
22	AS
23	declare @sql varchar(max),@excelfile varchar(max)
24	set @sql =‘Select * from ‘+CHAR(9)+@itable
25	set @excelfile = ‘d:\’+@ioutputfile
26	EXEC sp_makewebtask
27	@outputfile = @excelfile,
28	@query = @sql,
29	@colheaders =1,
30	@FixedFont=0,@lastupdated=0,@resultstitle=@iresultstitle
31
32	–EXECUTE THIS SP
33
34	EXEC CREATE_AND_LOAD_EXCEL
35	@ioutputfile = ‘NewFile.xls’,
36	@itable = ‘sys.tables’,
37	@iresultstitle=‘Testing details’

Now that we have seen that you can do this by passing table name and excel file name dynamically
this proc can be run with a execute sql task in a FOR EACH LOOP container where in you pass the
table where you want to load data from and name of excelfile you want to create dynamically.