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!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)|||is there any sample to show that how to ceate an excel file after reading data from sql server?

Thanks|||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|||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!|||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!|||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.|||Could you please explain how can I create this template and copy it every time I need this?

Thanks|||'************************************************* *********************
' 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.