Output A Table To Excel File With Overwrite 3

[Skittle]

Hi,

I want to be able to send an Excel file of data from an SQL server table via email using SQL server. In the data transformation packages I have found the 'Send Mail Task' which works nicely with an existing Excel sheet.

I have set up a connection to the Excel file and executed a transformation to populate it with the data I want to send via email.

The only problem I have is that each execution of the package appends data to the Excel sheet. How can I overwrite the Excel data in my transformation package?
From the options available it looks as if I will need to delete the data from the Excel sheet. Is there a way to do this?

Thanks.

 

[MaffewW]

unfortunately you cant do a 'delete from' on the existing Excel sheet from DTS, you have to drop and recreate the table which is a shame because it doesnt allow you to use formatting and formulas.

you need to run an execute SQL task on the spreadsheet connection with

drop table `tablename`

on completion you need another execute sql command to create the table eg

CREATE TABLE `tablename` (
`Name` VarChar (50)
)

and then you do the transformation. You can use the wizard to create you an example of this by clicking the drop and recreate table option behind the transform button and saving it.

If you need to use formatting you could probably do something like

step1) use xp_cmdshell to run a dos copy command to copy the template file to a folder
2) establish the connection and populate the cells with the data
3) use the send mail task to email the file
4) use xp_cmdshell again to rename the Excel file with a date on the end or something or delete the file

hope this helps, if you need anymore pls post

Matt

Brighton, UK

 

[Skittle]

Hmmm. Formatting is not a problem but I had managed to get something working iusing a .bat file in my transformation package to copy an empty excel sheet over the destination.

I like the 'DROP TABLE' idea but can't get it working.
I execute the 'DROP TABLE' SQL command against the Excel connection and get a 'completed successfully' message but the Excel sheet by the same name still exists with the
data in it. the create table action then fails.

 

[MaffewW]

skittle, not quite sure why that it. ok, try creating a test package with the wizard and saving it so you can see what it should look like.

Export your proc or a test table to a new Excel Workbook. When you get to the 'select source table and views' screen, give the destination the name of the Excel worksheet.

Click the transform button and tick the 'drop and recreate destination table' check box. click next, then select save dts package to sql server and give it a name at the next step.

When you run it it will show 4 steps. The drop table command will fail (as it doesnt exist yet) but the rest should execute ok. Open up the package in design mode and the should be what you are trying to get. if you run the package again all 3 steps should run ok, and the data in your worksheet should get overwritten

HTH

If you are still having trouble pls post more details about the problem including the order you have the steps set up, workflow properties and sql syntax (or you could email me a screen shot)

Cheers

Matt

Brighton, UK

 

[Skittle]

With regard to the package I have already set up perhaps I should explain it in more detail and what I see happening.
I am extracting data from an SQL table called SERVICE_CENTRES and exporting it to an Excel file.

I have set up a connection in the package for both SQL Server and Excel which are used by the following steps:-

1) Execute SQL Task: Drop Table
This task points to my 'Microsoft Excel 97-2000'
connection and contains the SQl statement:-
'DROP TABLE SERVICE_CENTRES'.

2) Execute SQL Task: Create Table
This task is executed on completion of 1).
It points to the 'Microsoft Excel 97-2000'
connection and contains the SQL statement:-
CREATE TABLE `SERVICE_CENTRES`
(`SCCO` VarChar (2) ,
`SCCTR` VarChar (3) ,
`SCCTRD` VarChar (30) )

3) Transform Data Task: Populate Excel File
This task is executed on success of 2).
It transforms the three fields from the
SQL SERVICE_CENTRE table to the three fields
in the Excel table created in step 2).
The source is defined as the local SQL server
and the destination is defined as the Excel
connected file.

When I run the package it works perfectly against an empty Excel sheet. If I run it again against the same Excel sheet and follow it through, step by step I find the following happens.

1) The 'DROP TABLE' command is executed successfully and
the table column names are deleted from the destination
Excel sheet. The data however remains in the Excel
sheet.

2) The 'CREATE TABLE' is executed and adds the table column
names and appends the data from the SQL server table to
the data not deleted by the 'DROP TABLE' command in
Excel.

The 'DROP TABLE' seems to remove the table, but not the data from my Excel file.

Thanks for your help. I admit I am no expert at SQL Server
but I'm trudging through books and trying to get to grips with it. I wounder how on earth I managed to learn anything I.T. related prior to the introduction on the internet!

Incidently, which wizard option do you mean?

From the Enterprise manager I select 'Tools' and then 'Wizards' from the top menu drop down.
I then expand 'Data Transformation Services' and select 'DTS Export Wizard'.

It prompts for a source and destination but I don't see anything about 'drop and recreate table'.

 

[MaffewW]

ok, thanks for extra info

it sounds like you are on the right tracks with how you have it set up. I have tried unsuccesfully replicating the problem

what syntax are you using for the drop table statement? It should be this (note the ` not the ')

DROP TABLE `SERVICE_CENTRES`

As you tried running it step by step the problem might be with that bit

when I was trying to get you to run the wizard, you got the right one, it is on about the 4th screen which says 'select source tables and views' at the top. There is a little button with says transform, you need to click that. That screen is also used to map columns. It still might be worth trying that to get a base package and you could add the send mail task to it after.



Matt

Brighton, UK

 

[Skittle]

I didn't have the '`' characters but even putting those in has not helped. I then tried the wizard method as you suggested and created a new package that does work!

I've compared the package created by the wizard with the package I created and cannot spot any difference in the way I have put it together. Still...I can use the wizard to get the job done.

Many thanks. Lots of pink stars!!