Sequential Order In Data Flow

Question

Sequential Order In Data Flow

Anonymous

Hi Support Team,

In the azure data flow, I have a scenario as below, where the data is been read parallelly from the source, but for me it should read sequentially in order to have a proper data in the target table.

Reading the data from the two or more tables and writing into a one target table.
Reading the data from the two or more tables and (also exclude the data which is already written in the step 1 process ) and then write the data into same target table.

For example:
Assume A1, A2, A3 as source table and T1 as target table.

Now in the 1st flow we will read the data from A1 and A2 and load the data into T1 and in the second flow we should read the data from A2 and A3 (and also exclude already loaded data in T1 ) and then load the data into the T1.

What is happening here is when we run the data flow, simultaneously parallel reads will happen i.e.

Reading the data A1 and A2
Reading the data A2 and A3 ( exclude which are not in T1).

So, in the step 2 there wont be any records to which are to be excluded from the T1 table.

Question here is, how to read the data in Sequential order from the source in the data flow like we have custom Sink order?

Please let me know if this question is able to understand and provide me the solution for the same.

Reach out to me for any clarifications
Mail id : ******@thomsonreuters.com

Thanks
Bharath

MartinJaffer-MSFT 26,236 Reputation points

2020-10-22T20:27:36.463+00:00

@Anonymous Are you still facing this issue? Did any of the answeres help you, or did you find your own solution. If you found your own solution, please share with the community.

2 answers

Your answer

MartinJaffer-MSFT 26,236 Reputation points

2020-10-22T20:27:36.463+00:00

@Anonymous Are you still facing this issue? Did any of the answeres help you, or did you find your own solution. If you found your own solution, please share with the community.

Answer 1

MarkKromer-MSFT 5,226 Microsoft Employee Moderator

Setting "Sink Ordering" is on the data flow general properties and may work for you here.

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-sink#custom-sink-ordering

MartinJaffer-MSFT 26,236 Reputation points

2020-10-15T19:13:50.773+00:00

@Anonymous did @MarkKromer-8019 's answer solve your issue? If so, please mark it as accepted answer, otherwise please tell us how we may better assist.
jhanns syr 6 Reputation points

2022-09-15T21:47:53.623+00:00

no it does not. at least not with TSQL via SSMS with a working azure synapse MSSQL connection. this is what is needed.

it does appear there are serious shortcomings with azure synapse MSSQL, Microsoft. and i say this having benefits from use of all versions of MSSQL since this product was invented in the late 1980s / early 1990s. i need to successfully load data in an azure synapse DW and I am not getting results i should be able to expect. i know i could expect them with nearly all other versions. it does make me feel like the GA release of azure synapse MSSQL was rushed; i.e. that it wasn't ready. there are many other shortcomings i am observing that compel me to share this feedback. and all of them have significant consequences to your customers.

this claim cannot be relied at all with azure synapse MSSQL:
" [table[column]] Has an IDENTITY property. The next incremental identity value is used."
there is no recognizable incremental identity value used in azure synapse MSSQL. Microsoft has already admitted this.

in azure synapse MSSQL it appears the ROW_NUMBER() function is not reliable. so this can't be relied on with azure synapse MSSQL to maintain an ordered data set either

i need data loaded in the sequential order it is provided to azure synapse MSSQL. it doesn't do this and it needs to. this should be trivial to accomplish and so far it is not.
i need order data loaded into azure synapse MSSQL in the order that is provided. can azure synapse MSSQL do this Microsoft? with a TSQL INSERT STMT ? how ?
jhanns syr 6 Reputation points

2022-09-15T22:14:03.087+00:00

re "sink ordering" not sure this helps. at least not with TSQL via SSMS with a working azure synapse MSSQL connection. this is what is needed.

it does appear there are serious shortcomings with azure synapse MSSQL, Microsoft. and i say this having benefits from use of all versions of MSSQL since this product was invented in the late 1980s / early 1990s. i need to successfully load data in an azure synapse DW and I am not getting results i should be able to expect. i know i could expect them with nearly all other versions. it does make me feel like the GA release of azure synapse MSSQL was rushed; i.e. that it wasn't ready. there are many other shortcomings i am observing that compel me to share this feedback. and all of them have significant consequences to your customers.

this claim cannot be relied at all with azure synapse MSSQL:
" [table[column]] Has an IDENTITY property. The next incremental identity value is used."
there is no recognizable incremental identity value used in azure synapse MSSQL. Microsoft has already admitted this.

in azure synapse MSSQL it appears the ROW_NUMBER() function is not reliable. so this can't be relied on with azure synapse MSSQL to maintain an ordered data set either

i need data loaded in the sequential order it is provided to azure synapse MSSQL. it doesn't do this and it needs to. this should be trivial to accomplish and so far it is not.
can azure synapse MSSQL do this Microsoft? with a TSQL INSERT STMT ? how ?

Answer 2

Kiran-MSFT 696 Microsoft Employee

You will need make 2 disconnected flows within the same dataflow. This can be done by making another source A2' from the same table and specifying sink order.

A1, A2 -> T1

A2', A3, T1 -> T1

Share via

Sequential Order In Data Flow

2 answers

Your answer