(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 PetersonConvenient?
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@.comcast.net> wrote in message
news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> (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
>|||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@.no-spam.sqldts.com> wrote in message
news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> 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@.comcast.net> wrote in message
> news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > (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
> >
> >
>|||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@.comcast.net> wrote in message
news:eLrkGpeYEHA.4008@.TK2MSFTNGP09.phx.gbl...
> 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@.no-spam.sqldts.com> wrote in message
> news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > 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@.comcast.net> wrote in message
> > news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > > (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
> > >
> > >
> >
> >
>|||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@.no-spam.sqldts.com> wrote in message
news:uuTVX3eYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> 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@.comcast.net> wrote in message
> news:eLrkGpeYEHA.4008@.TK2MSFTNGP09.phx.gbl...
> > 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@.no-spam.sqldts.com> wrote in message
> > news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > 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@.comcast.net> wrote in message
> > > news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > > > (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
> > > >
> > > >
> > >
> > >
> >
> >
>|||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@.comcast.net> wrote in message
news:e2ms47iYEHA.2216@.TK2MSFTNGP10.phx.gbl...
> 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@.no-spam.sqldts.com> wrote in message
> news:uuTVX3eYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > 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@.comcast.net> wrote in message
> > news:eLrkGpeYEHA.4008@.TK2MSFTNGP09.phx.gbl...
> > > 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@.no-spam.sqldts.com> wrote in message
> > > news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > > 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@.comcast.net> wrote in message
> > > > news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > > > > (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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||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@.no-spam.sqldts.com> wrote in message
news:OqDHpFlYEHA.1764@.TK2MSFTNGP10.phx.gbl...
> 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@.comcast.net> wrote in message
> news:e2ms47iYEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > 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@.no-spam.sqldts.com> wrote in message
> > news:uuTVX3eYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > 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@.comcast.net> wrote in message
> > > news:eLrkGpeYEHA.4008@.TK2MSFTNGP09.phx.gbl...
> > > > 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@.no-spam.sqldts.com> wrote in message
> > > > news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > > > 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@.comcast.net> wrote in message
> > > > > news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > > > > > (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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||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@.comcast.net> wrote in message
news:OQ7woYpYEHA.3716@.TK2MSFTNGP11.phx.gbl...
> 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@.no-spam.sqldts.com> wrote in message
> news:OqDHpFlYEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > 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@.comcast.net> wrote in message
> > news:e2ms47iYEHA.2216@.TK2MSFTNGP10.phx.gbl...
> > > 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@.no-spam.sqldts.com> wrote in message
> > > news:uuTVX3eYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > > 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@.comcast.net> wrote in message
> > > > news:eLrkGpeYEHA.4008@.TK2MSFTNGP09.phx.gbl...
> > > > > 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@.no-spam.sqldts.com> wrote in message
> > > > > news:%23KVU0EeYEHA.2736@.TK2MSFTNGP10.phx.gbl...
> > > > > > 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@.comcast.net> wrote in message
> > > > > > news:u4974pdYEHA.2672@.tk2msftngp13.phx.gbl...
> > > > > > > (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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment