DTS - SQL Statement to Excel

Question

DTS - SQL Statement to Excel

gjwg

SSC-Addicted

Points: 444
More actions
September 7, 2006 at 7:45 am

#169674

Hi,
I have created a view (SQL Server 2000) and want to publish the results of the view to excel.
I have set up a DTS with this in mind and scheduled the task to run every week. I have just tested the DTS before the schedule time but it appends the data to the existing data. Is there any option within DTS to force an create or overwrite?
If theer is not - How do you add a Step 1 to the DTS to 'master..xp_cmdshell "del filename"' so that the excel file is deleted.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello,

Click on "Execute SQL Task" icon and place it on the designer window in DTS. Here you need to have the existing connection selected and give the required SQL statement to delete the existing excel file before proceeding with the data transformation. Then you need to set Workflow properties by selecting all the task items, so that when the DTS is executed then the first step executed is the file deletion and then the data transformation.

Hope this helps you.

Thanks

Lucky

Wayne Coles SSC Enthusiast Points: 121 More actions · Answer 2

Hi

I achieve this in a different way.

Once I have saved the view i open Excel and under the DATA heading in the main menu, you will see Import External Data from this select New Database Query. This will bring up a list of all of the databases you can access.

Select the database you require, and a list of all of the table and views will appear, if you do not see the views, click the option button below which has a tick box to display them. Select the view you require and open it, and pass across the fields you wich to see.

Follow through the NEXT buttons, these offer you additional options of sorting and filtering, when you get to finish, make sure Return Data to Excel is selected and select the cell where you wish your results to start.

Once all the data has appeared, at any time you can right mouse click on any cell containing your results and select Refresh Data, from that same list you can also select Data Range Properties that will provide you with many other options, such as Refresh on Opening, fill down adjacent formulas, keep cell formatting etc

Wayne

gjwg SSC-Addicted Points: 444 More actions · Answer 3

Thanks to both of you...

I have already adopted the New Database Query method on previous project and this has worked well.

The reason for doing it the DTS method is that I can schedule the task at a specific time and also guaranteeing a snap shot of that data.

I have successsfully tested and completed the DTS method without duplication.

DavidP-340734 Right there with Babe Points: 737 More actions · Answer 4

You can also use an Active X Script task in DTS to delete the file:

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

Dim fso, filespec

Set fso = CreateObject("Scripting.FileSystemObject")

filespec = \\Server\path\file.xls

If (fso.FileExists(filespec)) Then

fso.DeleteFile(filespec)

End If

Main = DTSTaskExecResult_Success

End Function

gjwg SSC-Addicted Points: 444 More actions · Answer 5

Thanks again for keeping me informed...

I have been been misleading myself. I thought that setting up a DTS with a connection and a defined Excel file via a view would be easy.

The stages are...

1. It needs the ActiveX script to delete the Excel file

2. It needs the SQL Statement to run the View

3. It also needs transform to copy the data to excel

But...

Stages 1 and 2 are successful but it fails on stage 3 on 'The Microsoft Jet Database could not find object...'

What am I doing wrong

ROBERT DOWSON Grasshopper Points: 17 More actions · Answer 6

same thing here: after deleting a file it wont find a worksheet as the file no longer exists. is there a way to create a file with preformatted worksheet before transferring data from database? or what is the correct scenario?

thanks,

Rob

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 7

when u click on transformation and go to destination tab it will pop up a "Create table script" copy that and add a step following your delete Excel script.

Step1: Delete Excel using active x script

Step2. Create Destination(Connection pointing to Excel)

Step3.Transformation from SQL to Excel.

Hope this helps

Thanks

Sreejith

lba Grasshopper Points: 12 More actions · Answer 8

How did you create destination? workflow tasks will not work between these types

TTFN

shalom halevy-332989 Grasshopper Points: 11 More actions · Answer 9

the following worked for me:

no need to delete or create anything !

use txt file instead of xls from the DTS objects

and call the file myfile.csv.

DTS is overwritting txt files on one hand (and csv is txt file)

and excell knows how to read csv files perfectly.

SQL-DBA Hall of Fame Points: 3051 More actions · Answer 10

I tried the csv route but just could not get it to do what I want (send via email attachment with Blat). I am using an Execute Process Task instead that runs a .bat file with these two lines in it:

del "C:\UserLog.xls"

copy "C:\UserLogBlank.xls" "C:\UserLog.xls"