dts dynamic query & automap columns question

Hello All,

I am sorry if this has been asked - I tried searching but the search kept timing out.

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.

- CharlesI would use a sproc with dynamic sql, bcp and xp_cmdshell personally|||Hi Brett,

Thanks for the response. I have to admit I'm fairly new to the more complex aspects of SQLServer (Stored Procedures, cmd_shell, etc). What I have so far was built using the Package Designer - to give you an idea of my level of expertise with DTS. But I'm not opposed to learning if that's what it takes.

Are you aware of any other "simpler" options?|||DTS is such a flukey thing...see it's a dangerous drug...you get used to the GUI, the you start to push it...which means you need to start using ActiveX and such.

What happens then is that DTS acts like a cursor and has to affect rows, one by one. Which slows things down dramatically.

If you could give me/us and example of what you are trying to do, I'm sure we can get you an elegant/effecient solution.

Read the sticky at the top of this thread (ok second sticky) and post what it asks for.|||lol Brett - you have described my addiction exactly. It all started so innocently...

I will try as best as possible to meet your requirements.

The Question: How can I get the result set from an ad-hoc query to export to any format supported by DTS?

This question does not deal with any specific tables, data or queries. I will use examples.

User Interface:
A web-based interface allows a user to create a report from a number of factors, including choosing which fields should display. This allows for variations in the number of fields being generated. The user may also choose from a number of formats.

The web application then dynamically assembles the query based on the user's selections.

Example Query A (5 columns / fields in record-set):
select name, address, state, zip_code, phone_number
from clients
where zip_code like '06%'

Example Query B (8 columns / fields in record-set):
select name, address, state, zip_code, phone_number, billing_number, last_invoice_date, billing_cycle
from clients
where zip_code like '06%'

In the DTS Package:
1) Connection 1 is set to work from a query. The query is set using the Dynamic Properties Task. It could use Query A one time, and Query B another.

2) Connection 2 is set as the output file type. For our purposes it will be xls.

3) The transformation between Connection 1 and Connection 2 exports the query results into the xls file.

The Error:
When Query A is exported, the DTS has 5 columns to map. When Query B is exported, it has 8 columns to map. The one DTS package errors out if the number of columns are different than it expects to find. It is not possible to limit users to a specific number of columns.|||How about as a cheap solution for the 5 column solution, just return empty strings for the last three columns, and that way all of the calls will return 8 columns, but in Excel, it will show as 5.

Will that work for you?|||OK, this is what I would do...

#1. Make sure all access to the database is done through strored procedures
#2. Dynamically build a view based on your users requirements. make it so it's a single column concatenated as a tab or comma delimited and you can even add a headr
#3. bcp out the view with xp_cmdshell

Let me work up a sample|||Wow Brett! Thanks, I'm intruiged. I can't wait to see it.

Thanks again.

- Charles|||What form do you collect the requirements in? Do formulate the query in the front end? Sounds like you do...|||Hi Brett,

You are correct the query if built first in ColdFusion. The user interface is a form through which the fields and associations are made.

The query is created, then saved in a sql table, then loaded as a dynamic property at runtime.

Thanks again,

- Charles