mssql 2000 dts dynamic query & automap columns question

[cfaheybestpitch]

Hello All,

I have designed a DTS package which extracts a query into an excel file.

It uses a query that changes dynamically based on user preferences, so I have used the dynamic property SourceSQLStatement to feed the exact query into the DTS package.

The issue, however, is that the query can be run a multitude of ways, and return a different number of columns each time. On one run, the query could return a 3 column record-set, on the next it could return an 8 column record-set.

Currently, the DTS package errors on each attempt because it expects a certain column set.

Is there a way to tell it to auto-map the columns at the time it executes? I could not find a dynamic property which did that.

I would hate to have to set up a different DTS package for each possible column set.

I am sure I am missing something.

Thanks in advance.

- Charles

[olivthill]

Let's presume DTS is the new feature of MSSQL, whose long name is Data Transformation Services. An introduction to DTS can be read at http://www.devarticles.com/c/a/ASP/Creating-DTS-Packages-With-SQL-Server-2000/1/

The queries are typed in a box. As far as I know, they are ordinary SQL queries. They are not dynamic queries (which is not very surprising to me, because the SQL language is based on the entity-relationship model of E. Codd, where tables have a fixed number of columns). Sorry.