Transfer related tables in one Dataflow

#1

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?

#2

On Jun 23, 11:57 pm, Yuriy Al <Yuri... (AT) discussions (DOT) microsoft.com>
wrote:

Quote:

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.

#3

On Jun 23, 11:57 pm, Yuriy Al <Yuri... (AT) discussions (DOT) microsoft.com>
wrote:

Quote:

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.

#4

On Jun 23, 11:57 pm, Yuriy Al <Yuri... (AT) discussions (DOT) microsoft.com>
wrote:

Quote:

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.

#5

On Jun 23, 11:57 pm, Yuriy Al <Yuri... (AT) discussions (DOT) microsoft.com>
wrote:

Quote:

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.

#6

On Jun 23, 11:57 pm, Yuriy Al <Yuri... (AT) discussions (DOT) microsoft.com>
wrote:

Quote:

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.

#7

On Jun 23, 11:57 pm, Yuriy Al <Yuri... (AT) discussions (DOT) microsoft.com>
wrote:

Quote:

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.

#8

On Jun 23, 11:57 pm, Yuriy Al <Yuri... (AT) discussions (DOT) microsoft.com>
wrote:

Quote:

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.

#9

On Jun 23, 11:57 pm, Yuriy Al <Yuri... (AT) discussions (DOT) microsoft.com>
wrote:

Quote:

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.

#10

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:

Quote:

On Jun 23, 11:57 pm, Yuriy Al <Yuri... (AT) discussions (DOT) microsoft.com
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.