Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    17

    Unanswered: Creating excel file from DTS package

    Hi,

    I have a created a DTS packges which is reading data from sql server table, manipulate this data as required and then create a text file with that data. I created the text file using FileSystemObject. I was writing one field at a time to the text file.

    I need to same thing but instead of creating text file, I need to create a excel file with each column from database going to separate column in excel sheet. I tried to do this with FileSystemObject, but it was wrting all the columns from database to one cell in excel sheet. How can I fix this problem?

    Thanks!

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Am I missing something or can't you just create a data transformation task going from your database to a excel spreadsheet?

    Create a conenction to a spreadsheet and a connection to a database and build your transformation. DTS will take care of everything else. If you need to play with filesnames etc you can do that after the package has been created (using the filesystem object)

  3. #3
    Join Date
    Nov 2003
    Posts
    17

    Creating excel file from DTS package

    is there any sample to show that how to ceate an excel file after reading data from sql server?

    Thanks

  4. #4
    Join Date
    Oct 2004
    Posts
    25
    start>programs>Microsoft SQL Server>Import and Export Data

    follow the wizard. select your db>Select your output file (excel)>hit next>run immediately>done

  5. #5
    Join Date
    Nov 2003
    Posts
    17

    Creating excel file from DTS package

    Sorry, I didn't explain my question properly. I can create an excel file and write data after reading from database but I also need to write totals, tax and grand totals after I finish writing all the records from the database. How can I add totals to the last line? I need to do all of this from a DTS package.

    Thanks!
    Last edited by dhaliwam; 10-21-04 at 11:38.

  6. #6
    Join Date
    Nov 2003
    Posts
    17

    Creating Excel File from DTS package

    I can create an excel file and write data after reading from database but I also need to write totals, tax and grand totals after I finish writing all the records from the database. How can I add totals to the last line? I need to do all of this from a DTS package.

    Thanks!

  7. #7
    Join Date
    Oct 2003
    Posts
    60
    I 've create a template with the functions for addign totals, etc already built in. using VB SCRIPT, copy the template and populate it every time you run the DTS package.
    jaraba

  8. #8
    Join Date
    Nov 2003
    Posts
    17

    Creating Excel File from DTS package

    Could you please explain how can I create this template and copy it every time I need this?

    Thanks

  9. #9
    Join Date
    Oct 2003
    Posts
    60
    '************************************************* *********************
    ' Visual Basic ActiveX Script
    '************************************************* ***********************

    Function Main()

    Dim objFSO, objFileTemplate, strFile

    Set objFSO = CreateObject ( "Scripting.FileSystemObject" )
    strFile = DTSGlobalVariables("strRootPath").Value & "Template.xls"
    Set objFileTemplate = objFSO.GetFile ( strFile )
    strMonth = Right ( "0" & DatePart ( "m", DTSGlobalVariables("dtStart").Value ), 2 )
    strFile = DTSGlobalVariables("strPath").Value & _
    DatePart ( "yyyy", DTSGlobalVariables("dtStart").Value ) & _
    strMonth & _
    " - " & _
    DTSGlobalVariables("ID").Value & " " & DTSGlobalVariables("Id").Value & _
    ".xls"
    DTSGlobalVariables("FileName").Value = strFile

    If ( objFSO.FileExists ( strFile ) ) Then
    Main = DTSTaskExecResult_Failure
    Exit Function
    End If

    objFileTemplate.Copy strFile, 0

    Main = DTSTaskExecResult_Success

    End Function



    Note: This is basically the code. You have a template in a directory. This vbscrip takes the template, copies it to a different directory. The name is obtained by using the 'Id' field and concatenating month and year.

    If you want, we can take this offline, just let me know your email address.
    jaraba

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •