Exporting to Excel for backup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Exporting to Excel for backup

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-14 : 02:27:38

Iam trying to export data from sql server to excel for back up because they want it in a excel spread sheet..Now its a 10 days backup.

Query and everything is fine...the only problem is I want to run the dts everyday and it should export to different excel files in the same folder.When i first runs its okay.. now when I again want to do the same process the next day..I have to recreate another excel file and all the tables and mapping for the seond excel..i want a script which can automatically create different files each time when I run the dts.the query and mapping is same for all these 10 days..can we do something with data driven query task and execute sql task...

Please do help me..

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-14 : 07:48:08

Why not output to an excel file and then use xp_cmdshell to rename or copy and rename the file?

to rename the file with current date on the end...


declare @cmd varchar(1000)
declare @NewFile varchar(50)
set @NewFile = ' afile' + replace(replace(convert(varchar(10), getdate(), 20), '-', ''), ' ', '') + '.txt'
SET	@Cmd = 'ren \\uncpath\afile.txt' + @NewFile 
EXEC	MASTER..xp_cmdshell @Cmd, NO_OUTPUT

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-15 : 12:13:46

Please help me with the DTS code to use for this transformation

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-16 : 04:43:01

You'd output to a standard file like you do at the moment, then run the above command after the transformations are complete...

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-16 : 13:06:09

Hi RickD,
I don't know how to create this one along with the dts.I have the connection and the data is pumping from the source to destination to a excel.Now what task should I select and bind to use this procedure u mentioned above.Is it the Execute Task process and what format should I save the above in that case

regards,
Sqllearner

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-16 : 16:13:30

quote:
Originally posted by sqllearner

Hi RickD,
I don't know how to create this one along with the dts.I have the connection and the data is pumping from the source to destination to a excel.Now what task should I select and bind to use this procedure u mentioned above.Is it the Execute Task process and what format should I save the above in that case

regards,
Sqllearner

Are you just using the wizard?

Open the package, and add an EXECUTE SQL Task....

Brett

8-)

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-06-16 : 18:11:59

Okay I will add the execute sql task but then when I use it there is an option saying win32 process.For that what I will have to show the path of the corressponding file right.Now what is the extension of the file in that case and can I save the above shown into a text file and then modify it to .cmd or .exe and bind it with the work flow where Iam exporting the data to the excel file

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources