I have created this report generator where the user should be able to
export the result to an Excel file. The users can pick their own
columns from a wide range of columns and a sourcetable(report_table)
will be created every time a user choose 'Export to Excel'. Is it
possible to create a DTS task which exports my
sourcetable(report_temp) to an Excel file independent on the
tablestructure is the sourcetable?

Thanks in advance!

What you would ideally need to do if using DTS is;

1. Use an ExecuteSQL task to create the Table/named range in Excel
2. Drop all existing transformations
3. Create your SourceSQLStatement based on your user choices
4. Recreate the transformations
5. Pump the data.

You would do all this in an Active Script task up front of the DataPump.
DTS is not very nice to you when you change the metadata.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Trond" <trond_wahlstrom (AT) hotmail (DOT) com> wrote

Shees Trond, Are you sure you don't work for the same company I do

I must do essentially the same thing, but I was thinking more along the line of using a predefined view for this task, (Not creating a throw away temp table. and calling a stored procedure. passing in the Where clause information
O
Once I create the pump package on this view would I be able to save the package and reuse it passing in the statements for the where clause
Is this something I can do

I'm still designing my solution so I'm open for suggestion

If anyone has any suggestions that would be great.