dts dynamic query & automap columns question

[cfaheybestpitch]

Hello,

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

[Marin]

The easiest way would be to change your SQL statement that feeds DTS package. It should have a maximum number of columns that can be populated, then you can assign NULL for all empty columns.

[cfaheybestpitch]

Hi Marin,

Thanks for the great suggestion. It's a very good idea.

The only downside is that it would require manual formatting of the excel file after the fact. The reason that is a problem is because the end intention is for a person to receive a file they can print and easily understand. They may also need to forward it on to someone else.

Any thoughts regarding those challenges?

Thanks again.

- Charles

[Marin]

Hi Charls,

You can create an Excel template spreadsheet with VBA code to massage it (clean up, apply formatting...) after data export.

Cheers, Marin