Generate Excel File after Data Extraction

Question

Generate Excel File after Data Extraction

Little Nick

SSCrazy

Points: 2202
More actions
January 30, 2011 at 9:20 pm

#234218

I intent to perform SSIS.
Once extract is successful. How to generate Excel file into specific folder?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Little Nick (1/30/2011)
I intent to perform SSIS.
Once extract is successful. How to generate Excel file into specific folder?

Do you mean creating a new Excel file?

A script task should be able to do this.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Marco Terzolo Grasshopper Points: 24 More actions · Answer 2

DECLARE @strsql NVARCHAR(255)

set @strsql='namefile.xls'

EXEC sp_makewebtask

@outputfile =@strsql,

@query = 'Select * from View1',

@colheaders =1,

@FixedFont=0,@lastupdated=0,@resultstitle='objecttitle:'

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 3

Hi, sp_makewebtask is nice but is a a deprecated feature.

The recommendation is to use SSRS. See:

http://msdn.microsoft.com/en-us/library/ms175576%28v=SQL.90%29.aspx

Regards,

Iulian

Little Nick SSCrazy Points: 2202 More actions · Answer 4

Marco Terzolo (1/31/2011)
DECLARE @strsql NVARCHAR(255)
set @strsql='namefile.xls'
EXEC sp_makewebtask
@outputfile =@strsql,
@query = 'Select * from View1',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='objecttitle:'

I hope you can explain line by line. I'm too junior about this script

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Little Nick (1/31/2011)
Marco Terzolo (1/31/2011)
DECLARE @strsql NVARCHAR(255)
set @strsql='namefile.xls'
EXEC sp_makewebtask
@outputfile =@strsql,
@query = 'Select * from View1',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='objecttitle:'
I hope you can explain line by line. I'm too junior about this script

Forget about that script.

Try the following link and see if that works for you (a .NET script can be found at the bottom):

http://dbaspot.com/forums/ms-sqlserver/357322-excel-template-required-export-ssis.html

If it is all too complicated, you can just create an empty excel file (a template) and at the beginning of your package copy that Excel file to your destination folder. Then you can write to this Excel file with the Excel Destination in the data flow.

If in doubt, google on the following keywords: SSIS Excel Destination

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP