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.
dts dynamic query & automap columns question Reply to Thread
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.
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.
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
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
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.
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.
#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