Dynamically set up DTS Transformations?

#1

Hi,

I have a DTS that transform data between two databases on the same SQL
Server, as shows below:

table A ====> table B
(database_1) (database_2)

Using Dynamic property of DTS, I can set up the source and destination of
the transformation dynamically via "global variable". I also need the
"transformations" part for the "Transform data task"to be set dynamically,
otherwise this DTS will fail when I transform another table (let's say table
C, its structure is different from table A) to table D (The structure of
table D is the same as table C, but not the same as thable B).

So is there a way that the traformations of "Transform data task" between
two data sources (I mean the mapping from the source columns to the columns
of destination) can be set dynamically?

Thanks a lot in advance,

Jamie

#2

You will have to code this yourself. You can do it but you just need to
code it. If you want some code to get a handle on doing it then ping me
offline

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote

Quote:

Hi,

I have a DTS that transform data between two databases on the same SQL
Server, as shows below:

table A ====> table B
(database_1) (database_2)

Using Dynamic property of DTS, I can set up the source and destination of
the transformation dynamically via "global variable". I also need the
"transformations" part for the "Transform data task"to be set dynamically,
otherwise this DTS will fail when I transform another table (let's say
table C, its structure is different from table A) to table D (The
structure of table D is the same as table C, but not the same as thable
B).

So is there a way that the traformations of "Transform data task" between
two data sources (I mean the mapping from the source columns to the
columns of destination) can be set dynamically?

Thanks a lot in advance,

Jamie

#3

Jamie, One possibility is to set up multiple transformations (i.e. A ->B,
C->D, J-K, etc) when you know the table structure of A, C and J are
different. Then you can use the multiple paths in a workflow technique
to select whether transformation A->B, C->D or J-K is executed based on
some deterministic criterion of your choice. This link is a good one,
http://www.sqldts.com/default.aspx?218.

"Jamie" wrote:

Quote:

Hi,

I have a DTS that transform data between two databases on the same SQL
Server, as shows below:

table A ====> table B
(database_1) (database_2)

Using Dynamic property of DTS, I can set up the source and destination of
the transformation dynamically via "global variable". I also need the
"transformations" part for the "Transform data task"to be set dynamically,
otherwise this DTS will fail when I transform another table (let's say table
C, its structure is different from table A) to table D (The structure of
table D is the same as table C, but not the same as thable B).

So is there a way that the traformations of "Transform data task" between
two data sources (I mean the mapping from the source columns to the columns
of destination) can be set dynamically?

Thanks a lot in advance,

Jamie

#4

Hi, Allan,

I would like to learn some code of yours. I sent the same message to you
yesterday, but our system stopped it and says "You don't have permission to
send to this recipient". Do you have other e-mail other than
allan (AT) no-spam (DOT) sqldts.com? Or you can just send to me directly.

A little bit clarification of my posted message: The two tables (A, C and B,
D), which served as data source (A, C)and data detination(B, D) are located
on different SQL server, let's day SQL1.database_1.A and SQL2.database_2.B

(We have 30 such tables like A and C.)

I really appreciate your help,

Jamie

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

Quote:

You will have to code this yourself. You can do it but you just need to
code it. If you want some code to get a handle on doing it then ping me
offline

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote in message
news:%23WjneMyhFHA.3256 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Hi,

I have a DTS that transform data between two databases on the same SQL
Server, as shows below:

table A ====> table B
(database_1) (database_2)

Using Dynamic property of DTS, I can set up the source and destination of
the transformation dynamically via "global variable". I also need the
"transformations" part for the "Transform data task"to be set
dynamically, otherwise this DTS will fail when I transform another table
(let's say table C, its structure is different from table A) to table D
(The structure of table D is the same as table C, but not the same as
thable B).

So is there a way that the traformations of "Transform data task" between
two data sources (I mean the mapping from the source columns to the
columns of destination) can be set dynamically?

Thanks a lot in advance,

Jamie

#5

Forgot to tell you. My email address is xzhang (AT) cce (DOT) umn.edu

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote

Quote:

Hi, Allan,

I would like to learn some code of yours. I sent the same message to you
yesterday, but our system stopped it and says "You don't have permission
to send to this recipient". Do you have other e-mail other than
allan (AT) no-spam (DOT) sqldts.com? Or you can just send to me directly.

A little bit clarification of my posted message: The two tables (A, C and
B, D), which served as data source (A, C)and data detination(B, D) are
located on different SQL server, let's day SQL1.database_1.A and
SQL2.database_2.B

(We have 30 such tables like A and C.)

I really appreciate your help,

Jamie

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:uFrUhmyhFHA.1044 (AT) tk2msftngp13 (DOT) phx.gbl...
You will have to code this yourself. You can do it but you just need to
code it. If you want some code to get a handle on doing it then ping me
offline

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new
stuff.
www.konesans.com - Consultancy from the people who know

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote in message
news:%23WjneMyhFHA.3256 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Hi,

I have a DTS that transform data between two databases on the same SQL
Server, as shows below:

table A ====> table B
(database_1) (database_2)

Using Dynamic property of DTS, I can set up the source and destination
of the transformation dynamically via "global variable". I also need the
"transformations" part for the "Transform data task"to be set
dynamically, otherwise this DTS will fail when I transform another table
(let's say table C, its structure is different from table A) to table D
(The structure of table D is the same as table C, but not the same as
thable B).

So is there a way that the traformations of "Transform data task"
between two data sources (I mean the mapping from the source columns to
the columns of destination) can be set dynamically?

Thanks a lot in advance,

Jamie

#6

Sent

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote

Quote:

Forgot to tell you. My email address is xzhang (AT) cce (DOT) umn.edu

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote in message
news:ekQTYn6hFHA.1148 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Hi, Allan,

I would like to learn some code of yours. I sent the same message to you
yesterday, but our system stopped it and says "You don't have permission
to send to this recipient". Do you have other e-mail other than
allan (AT) no-spam (DOT) sqldts.com? Or you can just send to me directly.

A little bit clarification of my posted message: The two tables (A, C and
B, D), which served as data source (A, C)and data detination(B, D) are
located on different SQL server, let's day SQL1.database_1.A and
SQL2.database_2.B

(We have 30 such tables like A and C.)

I really appreciate your help,

Jamie

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:uFrUhmyhFHA.1044 (AT) tk2msftngp13 (DOT) phx.gbl...
You will have to code this yourself. You can do it but you just need to
code it. If you want some code to get a handle on doing it then ping me
offline

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new
stuff.
www.konesans.com - Consultancy from the people who know

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote in message
news:%23WjneMyhFHA.3256 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Hi,

I have a DTS that transform data between two databases on the same SQL
Server, as shows below:

table A ====> table B
(database_1) (database_2)

Using Dynamic property of DTS, I can set up the source and destination
of the transformation dynamically via "global variable". I also need
the "transformations" part for the "Transform data task"to be set
dynamically, otherwise this DTS will fail when I transform another
table (let's say table C, its structure is different from table A) to
table D (The structure of table D is the same as table C, but not the
same as thable B).

So is there a way that the traformations of "Transform data task"
between two data sources (I mean the mapping from the source columns to
the columns of destination) can be set dynamically?

Thanks a lot in advance,

Jamie

#7

Allan, I have been stuck on this exact issue for 2 days and hav
hopefully found someone in yourself who can help me. Please can yo
send some of the code so I can get a handle on how to have
transformation where it is dynamic allowing me to use the sam
package each time but set the source table and destination text fil
to be different each time. My email address i
justin.d.williams (AT) aib (DOT) ie. Hopefully I will be able to have som
success tomorrow. Really like the literature you have provide
throughout the internet

Quote:

Allan Mitchellwrote
You will have to code this yourself. You can do it but you just nee

to

Quote:

code it. If you want some code to get a handle on doing it the
ping me
offlin

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP
www.SQLDTS.com - The site for all your DTS needs
www.SQLIS.com - You thought DTS was good. here we show you the ne
stuff
www.konesans.com - Consultancy from the people who kno

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote in message
news:%23WjneMyhFHA.3256 (AT) TK2MSFTNGP12 (DOT) phx.gbl..
Hi

I have a DTS that transform data between two databases on the sam
SQ
Server, as shows below

table A ====> table
(database_1) (database_2

Using Dynamic property of DTS, I can set up the source an
destination o
the transformation dynamically via "global variable". I also nee
th
"transformations" part for the "Transform data task"to be se
dynamically
otherwise this DTS will fail when I transform another table (let'
sa
table C, its structure is different from table A) to table D (Th
structure of table D is the same as table C, but not the same a
thabl
B)

So is there a way that the traformations of "Transform data task
betwee
two data sources (I mean the mapping from the source columns to th
columns of destination) can be set dynamically

Thanks a lot in advance

Jami
[/quote:08301de9da

#8

Have sent the package to your account

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

"justinw" <justin_d_williams (AT) hotmail-dot-com (DOT) no-spam.invalid> wrote in
message news:qNSdnfd7FKAs1WXfRVn_vQ (AT) giganews (DOT) com...

Quote:

Allan, I have been stuck on this exact issue for 2 days and have
hopefully found someone in yourself who can help me. Please can you
send some of the code so I can get a handle on how to have a
transformation where it is dynamic allowing me to use the same
package each time but set the source table and destination text file
to be different each time. My email address is
justin.d.williams (AT) aib (DOT) ie. Hopefully I will be able to have some
success tomorrow. Really like the literature you have provided
throughout the internet.

Allan Mitchellwrote:
You will have to code this yourself. You can do it but you just need
to
code it. If you want some code to get a handle on doing it then
ping me
offline

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new
stuff.
www.konesans.com - Consultancy from the people who know

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote in message
news:%23WjneMyhFHA.3256 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Hi,

I have a DTS that transform data between two databases on the same
SQL
Server, as shows below:

table A ====> table B
(database_1) (database_2)

Using Dynamic property of DTS, I can set up the source and
destination of
the transformation dynamically via "global variable". I also need
the
"transformations" part for the "Transform data task"to be set
dynamically,
otherwise this DTS will fail when I transform another table (let's
say
table C, its structure is different from table A) to table D (The
structure of table D is the same as table C, but not the same as
thable
B).

So is there a way that the traformations of "Transform data task"
between
two data sources (I mean the mapping from the source columns to the
columns of destination) can be set dynamically?

Thanks a lot in advance,

Jamie
[/quote:08301de9da]

#9

Hello Allan, Ihave exatly the same problem, could you send me the answer
directly here or send it to my email: mehdi.achour (AT) free (DOT) fr. I'm blocked and
it's urgent
Thanks a lot
Mehdi

"Allan Mitchell" wrote:

Quote:

Have sent the package to your account

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

"justinw" <justin_d_williams (AT) hotmail-dot-com (DOT) no-spam.invalid> wrote in
message news:qNSdnfd7FKAs1WXfRVn_vQ (AT) giganews (DOT) com...
Allan, I have been stuck on this exact issue for 2 days and have
hopefully found someone in yourself who can help me. Please can you
send some of the code so I can get a handle on how to have a
transformation where it is dynamic allowing me to use the same
package each time but set the source table and destination text file
to be different each time. My email address is
justin.d.williams (AT) aib (DOT) ie. Hopefully I will be able to have some
success tomorrow. Really like the literature you have provided
throughout the internet.

Allan Mitchellwrote:
You will have to code this yourself. You can do it but you just need
to
code it. If you want some code to get a handle on doing it then
ping me
offline

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new
stuff.
www.konesans.com - Consultancy from the people who know

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote in message
news:%23WjneMyhFHA.3256 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Hi,

I have a DTS that transform data between two databases on the same
SQL
Server, as shows below:

table A ====> table B
(database_1) (database_2)

Using Dynamic property of DTS, I can set up the source and
destination of
the transformation dynamically via "global variable". I also need
the
"transformations" part for the "Transform data task"to be set
dynamically,
otherwise this DTS will fail when I transform another table (let's
say
table C, its structure is different from table A) to table D (The
structure of table D is the same as table C, but not the same as
thable
B).

So is there a way that the traformations of "Transform data task"
between
two data sources (I mean the mapping from the source columns to the
columns of destination) can be set dynamically?

Thanks a lot in advance,

Jamie
[/quote:08301de9da]

#10

On its way

"Mehdi" <Mehdi (AT) discussions (DOT) microsoft.com> wrote

Quote:

Hello Allan, Ihave exatly the same problem, could you send me the answer
directly here or send it to my email: mehdi.achour (AT) free (DOT) fr. I'm blocked
and
it's urgent
Thanks a lot
Mehdi

"Allan Mitchell" wrote:

Have sent the package to your account

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new
stuff.
www.konesans.com - Consultancy from the people who know

"justinw" <justin_d_williams (AT) hotmail-dot-com (DOT) no-spam.invalid> wrote in
message news:qNSdnfd7FKAs1WXfRVn_vQ (AT) giganews (DOT) com...

Allan, I have been stuck on this exact issue for 2 days and have
hopefully found someone in yourself who can help me. Please can you
send some of the code so I can get a handle on how to have a
transformation where it is dynamic allowing me to use the same
package each time but set the source table and destination text file
to be different each time. My email address is
justin.d.williams (AT) aib (DOT) ie. Hopefully I will be able to have some
success tomorrow. Really like the literature you have provided
throughout the internet.

Allan Mitchellwrote:

You will have to code this yourself. You can do it but you just
need
to

code it. If you want some code to get a handle on doing it then

ping me

offline

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new

stuff.

www.konesans.com - Consultancy from the people who know

"Jamie" <gsadd (AT) yahoo (DOT) com> wrote in message
news:%23WjneMyhFHA.3256 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Hi,

I have a DTS that transform data between two databases on the same

SQL

Server, as shows below:

table A ====> table B
(database_1) (database_2)

Using Dynamic property of DTS, I can set up the source and

destination of

the transformation dynamically via "global variable". I also need

the

"transformations" part for the "Transform data task"to be set

dynamically,

otherwise this DTS will fail when I transform another table (let's

say

table C, its structure is different from table A) to table D (The
structure of table D is the same as table C, but not the same as

thable

B).

So is there a way that the traformations of "Transform data task"

between

two data sources (I mean the mapping from the source columns to the
columns of destination) can be set dynamically?

Thanks a lot in advance,

Jamie
[/quote:08301de9da]