|
|
|
date: Mon, 23 Jun 2008 14:57:00 -0700,
group: microsoft.public.sqlserver.dts
back
Re: Transfer related tables in one Dataflow
On Jun 23, 11:57 pm, Yuriy Al
wrote:
> Hello All,
>
> Is there any way to transfer related tables in one Dataflow? I found a
> couple of articles in Internet, but they recommend using 2 dataflows or raw
> files. My dataload is within distributed transaction, so it can not be raw
> files.
>
> I have following requirement. There are 2 tables related in one-to-many
> relationship with FK constraint. I need to transfer data from them from one
> DB server to another as is without any transformations. Records to be
> transferred should be selected using SQL Command as the requirement is to
> manages bulks and ID from which to start transferring.
>
> In test environment I was able to create dataflow were 2 data sources - one
> for parent table and one for child. Data from parent flows to "multicast"
> transformation, and from there one flow goes to parent destination and second
> is joined (inner join) with data from child source and goes to child
> destination. It works perfect in test, but I am not sure that insert into
> parent will be always done prior to insert into child. Is there any way to
> explicitly configure the sequence?
Hi Yuriy,
don't exaclty know if this can work in a distributed transaction...
but for a similar problem (two tables related with a fk constraints)
- I created an execute sql task, in which i select all and only the PK
column of the parent table and put the result set in an object
variable
- loop through each item of that variable with a for each loop
container (i built another variable containing the "current" value ok
the PK)
- create a data flow inside that each loop container
- select and insert the ROW corresponding to the current value (second
var) on the destination parent table
- select and insert the ROWS corresponding to the current value
(second var) on the destination child table
Let me know if this could help you
M.
date: Tue, 24 Jun 2008 08:38:27 -0700 (PDT)
author: matteus
Re: Transfer related tables in one Dataflow
Hi Matteus,
Thank you for your response.
From what I understand that you within the same dataflow insert ONE ROW for
parent and appropriate ROWS for child. How do you ensure that parent row is
always inserted/written to the destination prior to the child rows?
Thanks,
Yuriy
"matteus" wrote:
> On Jun 23, 11:57 pm, Yuriy Al
> wrote:
> > Hello All,
> >
> > Is there any way to transfer related tables in one Dataflow? I found a
> > couple of articles in Internet, but they recommend using 2 dataflows or raw
> > files. My dataload is within distributed transaction, so it can not be raw
> > files.
> >
> > I have following requirement. There are 2 tables related in one-to-many
> > relationship with FK constraint. I need to transfer data from them from one
> > DB server to another as is without any transformations. Records to be
> > transferred should be selected using SQL Command as the requirement is to
> > manages bulks and ID from which to start transferring.
> >
> > In test environment I was able to create dataflow were 2 data sources - one
> > for parent table and one for child. Data from parent flows to "multicast"
> > transformation, and from there one flow goes to parent destination and second
> > is joined (inner join) with data from child source and goes to child
> > destination. It works perfect in test, but I am not sure that insert into
> > parent will be always done prior to insert into child. Is there any way to
> > explicitly configure the sequence?
>
> Hi Yuriy,
> don't exaclty know if this can work in a distributed transaction...
> but for a similar problem (two tables related with a fk constraints)
> - I created an execute sql task, in which i select all and only the PK
> column of the parent table and put the result set in an object
> variable
> - loop through each item of that variable with a for each loop
> container (i built another variable containing the "current" value ok
> the PK)
> - create a data flow inside that each loop container
> - select and insert the ROW corresponding to the current value (second
> var) on the destination parent table
> - select and insert the ROWS corresponding to the current value
> (second var) on the destination child table
>
> Let me know if this could help you
> M.
>
date: Tue, 24 Jun 2008 09:14:02 -0700
author: Yuriy Al
Re: Transfer related tables in one Dataflow
On Jun 24, 6:14 pm, Yuriy Al
wrote:
> Hi Matteus,
>
> Thank you for your response.
> From what I understand that you within the same dataflow insert ONE ROW for
> parent and appropriate ROWS for child. How do you ensure that parent row is
> always inserted/written to the destination prior to the child rows?
>
> Thanks,
> Yuriy
>
> "matteus" wrote:
> > On Jun 23, 11:57 pm, Yuriy Al
> > wrote:
> > > Hello All,
>
> > > Is there any way to transfer related tables in one Dataflow? I found a
> > > couple of articles in Internet, but they recommend using 2 dataflows or raw
> > > files. My dataload is within distributed transaction, so it can not be raw
> > > files.
>
> > > I have following requirement. There are 2 tables related in one-to-many
> > > relationship with FK constraint. I need to transfer data from them from one
> > > DB server to another as is without any transformations. Records to be
> > > transferred should be selected using SQL Command as the requirement is to
> > > manages bulks and ID from which to start transferring.
>
> > > In test environment I was able to create dataflow were 2 data sources - one
> > > for parent table and one for child. Data from parent flows to "multicast"
> > > transformation, and from there one flow goes to parent destination and second
> > > is joined (inner join) with data from child source and goes to child
> > > destination. It works perfect in test, but I am not sure that insert into
> > > parent will be always done prior to insert into child. Is there any way to
> > > explicitly configure the sequence?
>
> > Hi Yuriy,
> > don't exaclty know if this can work in a distributed transaction...
> > but for a similar problem (two tables related with a fk constraints)
> > - I created an execute sql task, in which i select all and only the PK
> > column of the parent table and put the result set in an object
> > variable
> > - loop through each item of that variable with a for each loop
> > container (i built another variable containing the "current" value ok
> > the PK)
> > - create a data flow inside that each loop container
> > - select and insert the ROW corresponding to the current value (second
> > var) on the destination parent table
> > - select and insert the ROWS corresponding to the current value
> > (second var) on the destination child table
>
> > Let me know if this could help you
> > M.
Ok... In the above depicted scenario I cannot ensure it.
At the moment I cannot figure out how to ensure this sequence.
I would check Lookup or Conditional Split data flow tasks, build a
single Data Source and exploit the error output too.
Bye,
M.
date: Wed, 25 Jun 2008 00:36:01 -0700 (PDT)
author: matteus
|
|
