Exporting data to Excel from a DTS
Alex
I've seen this noted in many posts, but nothing I've checked out gives
me any clue on how to do this.
Basically as my topic says, I have a DTS and I simply need to export
some data from a table in MS SQL 2000 to an Excel spreadsheet. I also
need to automate this process so it can run nightly and each new day a
new spreadsheet will be on a network share for us to pick-up.
Can someone point me to the right direction? This needs to be done
totally through the DTS script, so no ImportExport wizard or anything
manual.
Thanks --
Sam
Ravi
Alex
I've checked at www.sqldts.com and many other SQL sites and newsgroups
-- even downloaded the latest MS BOL for MS SQL 2000, but nothing. I
find lots of folks saying they're doing this or they give snippets of
code saying 'this is what to do', but I have no idea how to empliment
this. I even found this site on MS's website -
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319951 - but I
don't think this method can be emplimented automatically plus I've yet
to find any 'DTS Import/Export Wizard'.
My DTS is basically some transform data tasks importing data from about
5 SQL databases into one table, and I need this table exported to
Excel. It sounds simple enough, but with my limited experience of VB
I'm at a loss. I have been working with DTS's for several years, but I
guess I've only done more basic tasks. I also have the Wrox
Professional SQL Server 2000 DTS book (ISBN: 1-861004-41-9) that has
helped me quite abit -- but once more no 'Export to Excel' to be found
in here.
Thanks for any suggestions or ideas from anyone ---
Sam
Ravi
file . It will overwrite everytime. But excel appends the records to the same
file.
Steps
1. Create a SQL Connection
2. Create a Excel Connection
3. Create the transformation.When define destination. it will give you the
CREATE TABLE statement for Excel make a copy of that.
4. Put the CREATE TABLE statment in SQl Task. Make sure to use the Excel
connection Excel (not SQl Server connection)
5. use the following ActiveX script delete the file
' Delete File
Option Explicit
Function Main()
Dim oFSO
Dim sSourceFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile = "C:\Work\Actual.xls"
' Check if file exists to prevent error
If oFSO.FileExists(sSourceFile) Then
oFSO.DeleteFile sSourceFile
End If
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
Executeion order is
1. Delete the file
2. Create the excel table
3. Transformation
Hope this will help.
--
Thanks & Rate the Postings.
-Ravi-
Ed Enstrom
I have dozens of DTS packages that create Excel reports. Here is the method I use:
1. FTP a template workbook from source folder to reports folder.
2. Create worksheet (table) in the report workbook
3. Data pump from SQL Server to the workbook.
4. E-mail the report to recipients
Here's the setup details:
1. Create a source folder on the server. This keeps all my report templates.
2. In the source folder, create a template workbook with just one worksheet.
In my company, this sheet has the company name, the name of the report and
standard boiler-plate text about confidentiality, etc. This is the primary
reason I use this approach, since it does not require that I re-create the
standard title worksheet every time.
3. Copy the template workbook from source folder to reports folder. You need
to do this only for the first time. The file must exist in order to create the
connection. SQLAgentCmdExec will need write access to the reports folder.
4. Create DTS package:
5. Create two connections, one to SQL Server, the other to the workbook in the
reports folder.
6. Task 1 - FTP task to copy template from source to reports, with overwrite
7. Task 2 - Execute SQL Task to CREATE TABLE (worksheet) in the report. I
create the table every time the package runs rather than keeping it in the
template because the report may change over time. When this happens, I just
change the data pump and leave the template alone.
8. Task 3 - Data Transformation task to pump data into the worksheet. The
source would be your SQL statement that selects the data from your five tables.
9. Task 4 - ActiveX task to e-mail the report.
The easiest way to create the CREATE TABLE statement is to set up the data pump
task and click on the Create Table button. Copy the Create `New Table`....
statement to the clipboard and paste it into the Execute SQL task, changing the
table name as appropriate. The table name becomes the worksheet name. If you
edit this satement, be careful not to use single-quote character. The delimiter
in the statement is the left-leaning accent mark (the one at upper-left of
keyboard, on same key as tilde).
HTH,
Ed