473,574 Members | 2,465 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Exporting data to Excel from a DTS

Hi all,

I've seen this noted in many posts, but nothing I've checked out gives
me any clue on how to do this.

Basically as my topic says, I have a DTS and I simply need to export
some data from a table in MS SQL 2000 to an Excel spreadsheet. I also
need to automate this process so it can run nightly and each new day a
new spreadsheet will be on a network share for us to pick-up.

Can someone point me to the right direction? This needs to be done
totally through the DTS script, so no ImportExport wizard or anything
manual.

Thanks --

Sam

Jul 23 '05 #1
1 10493
Alex wrote:
Hi all,

I've seen this noted in many posts, but nothing I've checked out gives
me any clue on how to do this.

Basically as my topic says, I have a DTS and I simply need to export
some data from a table in MS SQL 2000 to an Excel spreadsheet. I also
need to automate this process so it can run nightly and each new day a
new spreadsheet will be on a network share for us to pick-up.

Can someone point me to the right direction? This needs to be done
totally through the DTS script, so no ImportExport wizard or anything
manual.

Thanks --

Sam


I have dozens of DTS packages that create Excel reports. Here is the method I use:

1. FTP a template workbook from source folder to reports folder.
2. Create worksheet (table) in the report workbook
3. Data pump from SQL Server to the workbook.
4. E-mail the report to recipients

Here's the setup details:
1. Create a source folder on the server. This keeps all my report templates.
2. In the source folder, create a template workbook with just one worksheet.
In my company, this sheet has the company name, the name of the report and
standard boiler-plate text about confidentiality , etc. This is the primary
reason I use this approach, since it does not require that I re-create the
standard title worksheet every time.
3. Copy the template workbook from source folder to reports folder. You need
to do this only for the first time. The file must exist in order to create the
connection. SQLAgentCmdExec will need write access to the reports folder.
4. Create DTS package:
5. Create two connections, one to SQL Server, the other to the workbook in the
reports folder.
6. Task 1 - FTP task to copy template from source to reports, with overwrite
7. Task 2 - Execute SQL Task to CREATE TABLE (worksheet) in the report. I
create the table every time the package runs rather than keeping it in the
template because the report may change over time. When this happens, I just
change the data pump and leave the template alone.
8. Task 3 - Data Transformation task to pump data into the worksheet. The
source would be your SQL statement that selects the data from your five tables.
9. Task 4 - ActiveX task to e-mail the report.

The easiest way to create the CREATE TABLE statement is to set up the data pump
task and click on the Create Table button. Copy the Create `New Table`....
statement to the clipboard and paste it into the Execute SQL task, changing the
table name as appropriate. The table name becomes the worksheet name. If you
edit this satement, be careful not to use single-quote character. The delimiter
in the statement is the left-leaning accent mark (the one at upper-left of
keyboard, on same key as tilde).

HTH,

Ed
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
7989
by: Chris | last post by:
Could someone please provide me an effective means of exporting data from a data set (or data grid) to Excel?
3
9232
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works only exporting to single worksheet. but i need to export data to multiple worksheets. it is very urgent to us. so please help me in code.
2
3053
by: deko | last post by:
I use a complied query to export to Excel like this: SELECT * INTO . FROM tblExcelData; But I have a situation where I need to export several tables into the same worksheet. The idea is to have each contiguous block of data on the worksheet separated by 50 or so rows so a graph can be inserted between each set of data.
2
1919
by: Mike P | last post by:
How do you go about exporting data in excel to a C# dataset? Any help would be really appreciated. Cheers, Mike
1
2305
by: ad | last post by:
I use the code below to export the content of a data set to Excel, the code come form http://www.dotnetjohn.com/articles.aspx?articleid=36 But it always use the web form's name as the default name of excel. Can we specify a meaningful filename before exporting to EXCEL? ...
2
2300
by: pmud | last post by:
Hi, I am exporting data from an EDITABLE DATA GRID EXCEL. But the 1st column in data grid is Edit Column. I want to display all columns in Excel except for the Edit column. The following code which I am using allows exporting only from text data from data grid & not from Edit columns which are link buttons. How to leave this column...
1
3151
by: Mustufa Baig | last post by:
I have an ASP.NET website where I am showing off crystal reports to users by exporting them to pdf format. Following is the code: ---------------- 1 Private Sub ExportReport() 2 Dim oStream As System.IO.MemoryStream = 3 myReport.ExportToStream( ExportFormatType.PortableDocFormat) 4 Response.Clear() 5 ...
6
2536
by: Opa | last post by:
Hi, I have a DataGrid, whose sourceI am exporting to Excel. This works fine except for the Column ordering. My datasource is not a datatable, with a typical SELECT statement where I can select the column orders. Instead the datasource is a class which implements IList, containing a collection of my data. My problem again is that I...
1
1848
by: Jason.A.Oliver | last post by:
Hi everyone, Quick question. I'm very much a beginner with Access so I'm sorry if there's an easy way that I missed. I have several fairly large tables that I am trying to export from Access into SPSS for analysis. I haven't found a way to go directly from Access to SPSS so what I have been doing is exporting to Excel and then opening...
4
2501
by: Tom | last post by:
I have a gridview on all of my web pages in my web app and they all export to excel. I have one page where the gridview is binding to a datatable that i created and only the first column is exporting to excel. How can I get the entire grid to export to excel?
0
7710
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8040
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8225
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6437
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5610
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3738
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3745
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1332
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1051
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.