DTS export from SQLServer 2000 to Excel

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)

DTS export from SQLServer 2000 to Excel

Author

Topic

Gil
Starting Member

3 Posts

Posted - 2003-02-28 : 08:46:19

I would like to call a DTS package on demand from ASP that exports some data from SQLServer into an Excel spreadsheet. The data to be exported will be from one table but a different subset of the data every time it is called.

Is this possible in DTS ?

If so are there any examples available or useful sources of information related to this kind of functionality.

thanks for any help

Gil

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-28 : 09:30:19

As another option, consider in Excel using a linked data range or pivot table to your SQL data.

Then the data is refreshed when user wants it to be; they can just right-click and choose REFRESH data.

Just create a spreadsheet, create a linked range or pivot table to a SQL VIEW, and save it.

OR: if you really need to control the timing and the data in an export and a VIEW won't work (or is really slow), just create a table specifically for Excel to link to and just update that table as needed.

I think it just doesn't make sense to export data INTO an Excel spreadsheet .... Excel is good at collecting and displaying and analyzing data from other sources, but not a STORING data if that makes sense.

- Jeff

Edited by - jsmith8858 on 02/28/2003 09:34:41

Gil
Starting Member

3 Posts

Posted - 2003-03-10 : 06:39:31

I realise that Excel is not ideal storage for data, but the reason I need to export to excel is that the file is then automatically read in by an instrument external to my software which can only read in files in an excel format ( somewhat restricting but that's what I'm stuck with ).

thanks for any further assistance

Gil

RooHoo
Starting Member

11 Posts

Posted - 2003-03-20 : 13:17:22

Did you get this to work? I need to do basically this same thing. Export a table to an excel spreadsheet on a shared drive.
Thanks

tacket
Starting Member

47 Posts

Posted - 2003-03-26 : 10:33:08

I just exported a table to an excel spreadsheet. It will create a new tab and default it to whatever name you give it and put the data in the tab.

As far as executing this from ASP.. I don't see why you couldn't. Just make sure the permissions aren't messing you up on the package.

Phil

jemm
Starting Member

6 Posts

Posted - 2003-03-26 : 12:37:40

quote:

I would like to call a DTS package on demand from ASP that exports some data from SQLServer into an Excel spreadsheet. The data to be exported will be from one table but a different subset of the data every time it is called.

Hi!
I once did a code that lists and executes DTS-packages.

Here is a sub that executes the DTS-packages by their names from localhost (same machine).


Sub ExecuteDTS(strDTS)
Response.Write "<p><strong>Executing DTS-package...</strong></p>" & vbCrLF
Response.Flush 	
Dim oPackage

Set oPackage = Server.CreateObject("DTS.Package")
	
'Loading and executing the package
oPackage.LoadFromSQLServer "localhost", , , 256, , , , strDTS
oPackage.Execute
oPackage.UnInitialize
Set oPackage = Nothing
Response.Write "<p><strong>Package " & strDTS & " has been executed.</strong> "
End Sub

I also did a code that lists all the packages in a html listbox. I did it with regular ADO using query:
SELECT DISTINCT name FROM sysdtspackages.

Notice that the names have to be fetched from the msdb -database, so that has to be remembered when doing the connection string.

-Jemm

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-26 : 12:52:16

Gil,

This is a perfect task for DTS.
You have two options you can set up the transformation in a DTS package and then either pass in global variable, store the variables in table and use an activex task to modify the DTS at run time and once completed with the transformation you can use another activex task to manipulate your spreadsheet.

Another alternative would be simply use a activex task all the way to move the data from sql server to excel by creating an ADO Recordset Object and and Excel.Application Object and dumping the recordset into the excel spreadsheet and saving it.

Jemm is refering to the fact that you can use the DTS/DMO objects and use VB to execute your packages and also pass global variables.

Try searching this site and sqldts.com for some great examples.

Let us know if you need help with more specifics.

Edited by - ValterBorges on 03/26/2003 12:53:23

Edited by - ValterBorges on 03/26/2003 12:53:51

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources