Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Automating the exporting of data to Excel...

Status
Not open for further replies.

SqlHunter

Programmer
Jun 3, 2004
166
US
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..
 
See thread961-853251

It explains how to manipulate the Properties of the tasks/connections in an active x script task

The excel files can also be created in the active x script task

Hope this helps
[flowerface]

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!
 
I checked and It isn't working..Would u please explain me how to do it.Where is the automated renaming of the excel done..File name could be like

Day1(june-11-2004).xls
Day2(june-12-2004).xls
Day3(june-13-2004).xls
 
Sorry for only replying now .. our internet lines were down for a while.

Still struggeling or have you found a solution?

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!
 
Just to make absolutely sure that I know what you are trying to do....

You are making sql backups to excel ... thus exporting the data in the tables to an excel spreadsheet...

* Add a Global Varaible to the DTS ... this will be the path where your excel files reside ... and you will pass the parameter to the DTS when you run it. This will be referred to later as FilePath

* Add an Active X script task - Here you will change the connection and the Data Source. First you will have to give the Excel Sheet for the particular day a name ...
Exapmle if it must be the day of the week
FileName = "DAY" & Weekday(now, [firstdayofweek]) & "(" & now & ")"
(Just note that this is not tested ... and that you must decide how you want to name the file.)

Say you are using the Text File (Source) connection you will the set the DataSource property from the Axtive X Task ... like this
DTSGlobalVariables.parent.Connections("Import").DataSource = FilePath & FileName
If you use the Data Transformation task to get the data across you will then set the Source Object Name property of the task... like this
DTSGlobalVariables.parent.Tasks("DTSTask_DTSDataPumpTask_1").Properties("SourceObjectName").Value = FilePath & FileName

Hope this helps,
Good luck
[flowerface]


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top