Export Data to Excel

Discussion:

(too old to reply)

James

2007-05-21 19:37:01 UTC

I am new to dts packages.

I need to export data on a daily basis from SQL to Excel and would like to
use dts to accomplish it. The data structure will not be changing.

I have the option of creating a new Excel file each day or appending a new
worksheet into 1 main Excel file. My preference would be to create a new
spreadsheet every day. In either case the file name or the worksheet name
needs to include the current date.

I am using the CREATE TABLE command to create the worksheet in Excel but I
cannot change the table name (worksheet name)

I also can use ftp task but again am having trouble dynamically naming the
destination file. The source file is pipeline.xls, the destination file
should be pipeline_052107.xls for example.

Any help would be appreciated. Thanks.

Francisco A. Gonzalez

2007-05-22 14:01:13 UTC

Permalink

You can workaround the problem having a process task that creates or copy
the excel file with metadata and the name you want, and a dynamic property
that changes the
connection manager to the file name and path of file you just created or
copy.

cheers
--
Francisco A. Gonzalez

Post by James
I am new to dts packages.
I need to export data on a daily basis from SQL to Excel and would like to
use dts to accomplish it. The data structure will not be changing.
I have the option of creating a new Excel file each day or appending a new
worksheet into 1 main Excel file. My preference would be to create a new
spreadsheet every day. In either case the file name or the worksheet name
needs to include the current date.
I am using the CREATE TABLE command to create the worksheet in Excel but I
cannot change the table name (worksheet name)
I also can use ftp task but again am having trouble dynamically naming the
destination file. The source file is pipeline.xls, the destination file
should be pipeline_052107.xls for example.
Any help would be appreciated. Thanks.

Red

2007-05-23 11:08:40 UTC

Permalink

Add an activex script to your page with something like the following.

Red.

'**********************************************************************
'' Create output Excel workbook(s)
'**********************************************************************

Function Main()

Dim fso, oConn, xlsFolder, xlsTemplate

Set fso = CreateObject("Scripting.FileSystemObject")

'Set folder and template names
xlsFolder = "\\server\directory\"
xlsTemplate = "pipeline.xls"

'Set datestamp for filenames
xlsDate =Format(Date, "YYYYMMDD")

'Copy excel files
xlsFrom = xlsFolder & xlsTemplate
xlsTo = xlsFolder & Replace(xlsTemplate, ".xls", xlsDate & ".xls")
fso.CopyFile xlsFrom, xlsTo

Set oConn = DTSGlobalVariables.Parent.Connections("Conection Name")
oConn.DataSource = xlsTo

Set oConn = Nothing
Set fso = Nothing

Main = DTSTaskExecResult_Success

End Function

Function format(dt, fm)

'Function for formatting dates to ISO formats

If Len(Month(dt)) = 1 Then m = "0" & Month(dt) else m = Month(dt)
If Len(Day(dt)) = 1 Then d = "0" & Day(dt) else d = Day(dt)
y = Year(dt)

If fm = "YYYYMMDD" Then format = y & m & d
If fm = "YYYYMM" Then format = y & m

End Function

Rinia Wood

2011-03-16 04:07:40 UTC

Permalink

I find a blog which focuses on exporting data. It has several articles about exporting data to Excel. About your question, there is a good article in this blog provides a good method to solve. You can read the article from
http://janewdaisy.wordpress.com/2011/03/09/24/

Post by Francisco A. Gonzalez
You can workaround the problem having a process task that creates or copy
the excel file with metadata and the name you want, and a dynamic property
that changes the
connection manager to the file name and path of file you just created or
copy.
cheers
--
Francisco A. Gonzalez

Post by Red
Add an activex script to your page with something like the following.
Red.
'**********************************************************************
'' Create output Excel workbook(s)
'**********************************************************************
Function Main()
Dim fso, oConn, xlsFolder, xlsTemplate
Set fso = CreateObject("Scripting.FileSystemObject")
'Set folder and template names
xlsFolder = "\\server\directory\"
xlsTemplate = "pipeline.xls"
'Set datestamp for filenames
xlsDate =Format(Date, "YYYYMMDD")
'Copy excel files
xlsFrom = xlsFolder & xlsTemplate
xlsTo = xlsFolder & Replace(xlsTemplate, ".xls", xlsDate & ".xls")
fso.CopyFile xlsFrom, xlsTo
Set oConn = DTSGlobalVariables.Parent.Connections("Conection Name")
oConn.DataSource = xlsTo
Set oConn = Nothing
Set fso = Nothing
Main = DTSTaskExecResult_Success
End Function
Function format(dt, fm)
'Function for formatting dates to ISO formats
If Len(Month(dt)) = 1 Then m = "0" & Month(dt) else m = Month(dt)
If Len(Day(dt)) = 1 Then d = "0" & Day(dt) else d = Day(dt)
y = Year(dt)
If fm = "YYYYMMDD" Then format = y & m & d
If fm = "YYYYMM" Then format = y & m
End Function
Submitted via EggHeadCafe
Win a 2 Year Personal Class Hosting Account From Arvixe.com
http://www.eggheadcafe.com/tutorials/aspnet/828f2029-b7be-4d15-877c-0d9e9ab74fc5/win-a-2-year-personal-class-hosting-account-from-arvixecom.aspx