Create a Package for export the query results to Excel

Hi,

In SQL Server,

does anybody provide the steps for create a Package for exporting the query results to Excel environment?

I just know that click the "Export", then choose the query file to export to Excel.

I want to know how to create the Package to export it.

Please let me know, thanks.1. Create new package

2. Connection from Connection menu (or right click)
* Add SQL server connection, specify the server name, authentication, and dbname
* Add Excel connection, specify the path where u want to place the excel file

3. Transform data task from Task menu (or right click)
* Add Transform data task, select the source and destination
* Double click the task
- Source tab : select the table/view or write down the query
- Destination tab : just click ok on create table script window (for sheet & column name)
- Transformation tab : Map the required columns

4. Save the DTS

5. Right click - Execute package or u can schedule it as job|||Question:

I created the package and execute the package, it works,
but, I got another problem, if I execute it for 2 times, the values will be output double in the same table,

eg.
1st execute:
Name:
Jim
Mary

2nd execute:
Name:
Jim
Mary
Jim
Mary

I want to execute the package which values will replace the previous file's values if the filename is same.

How should I do? Please let me know, thanks.|||em....i dunno how to delete data in excel destination file. but u can do it if the destination is ms access file, with additional task and workflow :
- Add a task (Execute SQL Task) as 1st step in the DTS
Existing connection : choose the ms access connection which was already created
SQL statement : DELETE * FROM <destination_tablename>

- Add on success workflow from above task to sql connection

Create a new database from a .bak backup

Hi
You don't need to start with a database when restoring. Click on the
database leaf in the tree and then enter your own database name in the
database name box, then go on and choose the file to restore from using the
restore from device option.
If you know that there is only one backup in the file and the database
doesn't exist you could use something like the following from a query window
if you change the file path/name and database name:
RESTORE DATABASE NewDatabase
FROM DISK = 'D:\Backups\DBBACKUP.bak'
GO
John
"GM" <guidomarche@.libero.it_n0sp4m> wrote in message
news:XbvAj.10735$q53.6897@.tornado.fastwebnet.it...
> Hi, i have a problem:
> I have a .bak file from a database backup, but i don't have the database
> anymore.
> How can i create the database from the backup file?
> If i try to restore the database with management studio, i should already
> have a database to put the data in.. but i don't have it anymore.
>
> If i create a new database, and try to put the backed up data into it, i
> got
> an error:
> Msg 3154, Level 16, State 4, Line 1
> The backup set holds a backup of a database other than the existing 'XXX'
> database.
>
> Is there a way to re-create the database from the backup?(it's a full
> backup)
> I'm working with MSSQL Server 2005 and Management Studio.
> Thank you for any advice!
>
>
"GM" <guidomarche@.libero.it_n0sp4m> wrote in message
news:7VxAj.10913$q53.947@.tornado.fastwebnet.it...
> Hi, thank you for the answer!
> the .bak file contains only one backup
> If i use the restore option, and type a new database name, when i execute
> i get te following error:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "D:\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\XXX_Data.mdf" failed with the operating system
> error 3(The system cannot find the path specified.).
>
> I've already tryed
> RESTORE DATABASE NewDatabase
> FROM DISK = 'D:\Backups\DBBACKUP.bak'
> (with the right location/dbname)
> but if the database does not exists, i got an error:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "D:\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\XXX_Data.mdf" failed with the operating system
> error 3(The system cannot find the path specified.).
> Msg 3156, Level 16, State 3, Line 1
> File 'XXX_Data' cannot be restored to 'D:\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\XXX_Data.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "D:\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\XXX_Log.ldf" failed with the operating system
> error 3(The system cannot find the path specified.).
> Msg 3156, Level 16, State 3, Line 1
> File 'XXX_Log' cannot be restored to 'D:\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\XXX_Log.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Msg 3119, Level 16, State 1, Line 1
> I've tryed to use "WITH MOVE " tu point to another folder, but since i
> don't have the mdf file, it's pointless...
>
Umm, that's not pointless at all. That's the exact whay you do this.
RESTORE DATABASE NewDatabase
from disk='D:\Backups\DBBACKUP.bak'
with move 'XXX_Data' to 'C:\newlocation_fordata\XXX_LOG.MDF',
move 'XXX_log' to 'C:\newlocation_forlogs\XXX_LOG.LDF'

> If i create a new database with the same name of the database i want to
> restor,e before trying to restore, i got the error i reported in the
> previous post..
|||RESTORE DATABASE [NewDatabaseName]
FROM DISK='BackupNameAndPath'
WITH MOVE 'OldDatabaseName' TO 'NewDatabaseNameAndPath.mdf',
MOVE 'OldDatabaseName_log' TO 'NewDatabaseNameAndPath.ldf'
NB the MOVEd strings are the logical database names. Use
RESTORE HEADERONLY FROM DISK = 'BackupNameAndPath'
to get a listing of the contents of the backup file to determine the
old database name.
sql

Counting Average Value

i want to count average on one of the fields in my table
i am doing a right click on it and expression and am typing the following
but getting 0 as an answer
=(( Fields!valueactiveaccounts.Value+ Fields!valueskipaccounts.Value)/( Fields!numactiveaccounts.Value+ Fields!numskipaccounts.Value))
is there any other way to get average value?Raj wrote:
> i want to count average on one of the fields in my table
> i am doing a right click on it and expression and am typing the
> following
> but getting 0 as an answer
> =(( Fields!valueactiveaccounts.Value+
> Fields!valueskipaccounts.Value)/( Fields!numactiveaccounts.Value+
> Fields!numskipaccounts.Value))
> is there any other way to get average value?
Try the inbuilt avg aggregate
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_10pz.asp
roland