DTS Export To Excel

[sandylou]

I am familiar with VBScript and can successfully export data in VB to an Excel file. I am now playing around with DTS packages and am a newbie. I have a stored procedure which contains the data I want. No parameters are passed into this sproc. I want to take the results of the sproc and export it into excel. I would like to set up my excel file dynamically as I have done in VB. I am trying to accomplish this by using Active X Scripting. I am not sure how I go from the Execute of my stored procedure to passing the result set similar to my SQL recordset:

.Range((.Cells(varCurRow, 1)), (.Cells(varCurRow, 1))).CopyFromRecordset rstDetail

I have seen some code that talks about the data pump and accessing the parent property. Is this what I need to do?

 

[JayKusch]

What is your source data coming from??? Access, SQL Server, Oracle ....

Thanks

J. Kusch

 

[sandylou]

I import data in from a few sources. They are large tables. I use a stored procedure to create a complex query which makes temporary tables and then drops then. It is from the results of my stored procedure in which I would like to export the results.

 

[JayKusch]

I will assume the query is being executed against a SQL Server database.

In the DTS designer, create a source connection being your SQL Server and needed database.

Create a destination connection being your Excel worksheet.

Create a "Transform Data Task" task that links your source connection, the SQL Server database, to you destination connection being your worksheet.

Double click on the task and in the Source tab, select "SQL query" then paste your query in.

After that, go to each of the other tabs and set the needed parameters and settings.

Click the green arrow at the top of the designer when you are ready to execute it.

That is the "short" of it. Hope this helps!



Thanks

J. Kusch

 

[sandylou]

When you create an Excel Connection, you need to identify the file name you want. I want to dynamically create this. This is why I thought I could create it in Active X, similar to how I would export queries in Access. I am using Transform Data Task, however, I am executing a stored procedure. I am only given one tab to use.

 

[sandylou]

I found something which I think answered my question. Yes the stored procedure would be the parent object. I am going to give it a try.

http://www.sqljunkies.com/Article/A8CB0AFE-D143-4B49-B865-4FBBFEDFCCD7.scuk

 

[lbrooks]

I am attempting to export data from a SQL Serve table to excel. It work fine as long as the excel spreadsheet always exists. Is there a way that once the package is execute to create the excel spreadsheet on the fly and not at the time the package is created?