Export Data to Excel

Discussion in 'microsoft.public.sqlserver.dts' started by James, May 21, 2007.

  1. James

    James Guest

    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.
    James, May 21, 2007
    1. Advertisements

  2. 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

    Francisco A. Gonzalez, May 22, 2007
    1. Advertisements

  3. James

    Red Guest

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


    '' 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
    Red, May 23, 2007
  4. James

    Rinia Wood Guest

    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
    Rinia Wood, Mar 16, 2011
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.