Hello,

I am very new to DTS, so excuse me if my question has already been
asked a million times. I have a view that is a combination of many
tables, and on a periodic basis, I would like to export this view to
an excel document automatically. The problem lies in that the fields
in this view change periodically depending on records in certain
tables. (An asp script updates the view automatically). When I
generate the the dts package, it seems to use all of the fields in the
view that were there when the package is created. Is it possible to
dynamically generate the DTS package based on the fields in the view
when the backup to excel is performed? Thanks so much, I really
appreciate any help!

-Nelson Colwell

Why not have a view purely for you and have the definition never change
so

CREATE VIEW SelRows(col1,col2,col3)
AS
<Definition>



This way the definition might change but your metadata for DTS will not.

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Nelson Colwell" <nelson (AT) laser (DOT) net> wrote

Allan,

Thanks for the reply, but my issue is that I need the metadata to
changed based on the fields currently in the view when I want to run
my export. Is there any way to dynamically create the DTS package when
I need to run it? My query is a very simple "SELECT * From MYVIEW",
but the metadata will vary. Is there any way to accomplish this, maybe
with a 3rd party application? Thanks!

-Nelson


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You can do this but it involves retrieving the definition from the View,
redoing the SourceColumns property and then the transformation Object.
Does you destination also change? If it doesn't then the example I posted
is good. Just because you change WHERE you get the data you still expose
the same metadata to DTS and that is ALL it cares abut.

If on the other hand you are saying you might be going here, there,
everywhere from here, there and everywhere then it may be worth loking at
building a dynamic package. The only thing here is that how do YOU know
what needs to go where. if you change all Source columns then how do you
know where they now need to map ?




--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Nelson Colwell" <nelson (AT) laser (DOT) net> wrote

Allan,

Thanks again so much for taking the time to help me out. What I think I
need to do is to retrive the defintion from the view and rebuild the
source columns property and the transformation object like you
mentioned. How would I go about retriving the defintion from the view
dynamically? Would I then just edit the .bas DTS package file
accordingly?

Cheers,
Nelson

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sort of

You can read the view by reading the definition by

select column_name,data_type from information_schema.columns where
table_name = 'NAME OF VIEW'

I would then rebuild the package accordingly.

I have a package where I do this

I have a SQL Query (Source)
I have an Excel spreadsheet (Destination)

The SQL Query is dynamic
I want to create a new Worksheet in my Excel Spreadsheet for each Query.

I dynamically create the Excel named range and then create the SourceColumns
and DestinationColumns property and transformations.

I can let you have it if you mail me privately with the request as I it is
at home and I am not.


--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Nelson Colwell" <nelson (AT) laser (DOT) net> wrote

Allan,

That package is exactly what I'm looking for. I'd like to email you
privately, but your email address isn't listed in your post. My email
address is nelson (AT) laser (DOT) net.

Thanks a million times over!

-Nelson

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

OK I'll send it later

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Nelson Colwell" <nelson (AT) laser (DOT) net> wrote