DTS - Excel Question

Question

DTS - Excel Question

annejo

Ten Centuries

Points: 1080
More actions
November 25, 2003 at 1:35 pm

#106642

Is it possible to export the results from a SQL table to an Excel spreadsheet and then clear the results in the Excel spreadsheet before running the export a second time?
Thanks for any information you can provide!
Anne

Viewing 7 posts - 1 through 6 (of 6 total)

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

Definitely.

This is easiest to set up with the Import/Export Wizard, choosing an Excel destination and, in the "transformations" dialog, chossing to drop and recreate destination table.

Cheers,

- Mark

Cheers,
- Mark

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 2

Or work the other way around ? Create a view and get external data in excel ?

Jeff

JV

wildh SSCarpal Tunnel Points: 4370 More actions · Answer 3

We do it all the time, some of our users want a history of data rather than using XL to get external data, which I would prefer as it’s less of a hassle for me. But it’s what the user wants that counts.

A brief guide for DTS

Create a SQL server connection

Create a XL connection

Create a data pump between the two , in destination click create (Copy SQL Statement, you’ll need it later)

Create a DTS task (use the XL connection in Existing connection), in the SQL statement ‘drop table Test_Table Go’ and paste your clipboard.

Create a precedence workflow to the SQL server connection

End result

SQL task drops the XL sheet in XL book then recreates it

Data pump populates XL sheet

StephenLRobson SSC Journeyman Points: 91 More actions · Answer 4

I have used external data queries in Excel with much success. When I have need to schedule this or trigger it automatically, I have used an ActiveX task, or an execute command task that runs a VBScript to open the Excel file, refresh the data, save and close it. Doing this means you can also refresh the data on an ad-hoc basis, without having to have access to the server running DTS - so general users can update the data when they want it, or open the file knowing that the data is refreshed regularly.

taffy Ten Centuries Points: 1067 More actions · Answer 5

quote:
I have used an ActiveX task, or an execute command task that runs a VBScript to open the Excel file, refresh the data, save and close it.

How do you go about doing this???

data_czar Ten Centuries Points: 1147 More actions · Answer 6

Use an Execute SQL task against the Excel connection:

DROP TABLE table_name

Then In a second Execute SQL task against the same connection:

CREATE TABLE `table_name` (

`ColumnName1` VarChar (8) ,

`ColumnName2` DateTime )

You can also apply formatting to this Excel file after the first load and it will maintain the formatting even with the DROP and CREATE.

Hope this helps.

-Corey