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

    cheers
     
    Francisco A. Gonzalez, May 22, 2007
    #2
    1. Advertisements

  3. James

    Red Guest

    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
     
    Red, May 23, 2007
    #3
  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
    http://janewdaisy.wordpress.com/2011/03/09/24/
     
    Rinia Wood, Mar 16, 2011
    #4
    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.