(SQL Server 2000, SP3a)

Hello all!

I'm considering using a Transform Data Task to move data from one server to another. I'd
like to programmatically set the query to the Transform Data Task to potentially different
(at run-time) tables. Is there a convenient way to programmatically wipe out the
transformations and reset them based on the current query?

Thanks!

John Peterson

Convenient?

There is the object model. So long as you know your source definition then
sure you can do this.

You need to loop through the Transformation Objects - Remove them (Removes
any previous mappings)
You then need to add 1 or more Transformation Object(s)
Then then need to add 1 or more SourceColumn property values to the
transformation(s)
Then you need to add 1 or more DestinationColumn property values to the
transformation(s)

You then add the 1 or more Transformation objects to the Transformations
collection.


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


"John Peterson" <j0hnp (AT) comcast (DOT) net> wrote

Thanks Allan!

I guess, then, that there's no method that is sort of like "auto map" based on the current
settings (where it would assume that the Source/Destination columns are exactly the same)?
Sort of like how the DTS Designer does it?

Barring that, do you have any examples of what you've just described below?

Thanks!


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

If you have not changed the meta data i.e.

source column names and destination column names are the same then no
remapping is required. DTS will do that for you.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


"John Peterson" <j0hnp (AT) comcast (DOT) net> wrote

Hey Allan!

I may have been unclear. I essentially want to execute a package where I would be
programmatically specifying a table name (or query) to the Transform Data Task. That is,
at run-time, my table will be different than what may have been used at design-time with
the Transform Data Task. As such, I would assume that I'd need to tear down the existing
transformations and add the new ones, no?

Thanks again for your help!

John Peterson


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

Not if the query definition remains the same and does not invalidate the
metadata

i.e.

Server 1 Source

SELECT a,b,c FROM D


So long as the Source from your dynamic changes has the same metadata as
this above query you are OK. Same goes for the destination.

If you changed your sourcestatement to

SELECT name, age, address1 FROM MyNewTable

Then everything would break.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


"John Peterson" <j0hnp (AT) comcast (DOT) net> wrote

Thanks Allan! That's exactly what I'm trying to say: I might define my query in the DTS
Designer as:

select * from A

But I want to programmatically change the query at run-time to be:

select * from B

It sounds like I need to tear down the transformations and rebuild them based on B. Do
you have some examples of such a thing?

Thanks again!

John Peterson


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

SELECT * is really bad as it may break if you start to add/remove attributes
+ it presupposes you want all attributes as well.

If you have varying attribute names then yes I have an example of this

Reads a Query in a file
Removes transformations
Creates the XL worksheet
remaps attributes

If you want it then mail me privately and you can have it.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


"John Peterson" <j0hnp (AT) comcast (DOT) net> wrote