Hi, we are receiving a file where the order of the rows is important information that needs to be maintained for our transformation process. When TimeXtender loads a text file, are the rows loaded in order? (I.e. will the DW_Id in the Business Unit table act as row numbers of the source table).
Thanks,
Mark
Best answer by rory.smith
TimeXtender will load text files in file order (at least when using the Business Unit approach). In practice this means order from a text file is preserved. This is not guaranteed however, so if there is no field that can be used to order the data you are depending on the current behaviour of SQL Server's bulk import implementation.
As DW_Id is a sequence number, this can be used to sort after data ingestion. It would be better if the source system can output an explicit sorting criterion as that does not depend on implicit behaviour and would be robust against parallel ingestion scenarios.
I do not know if Azure Data Factory can split the transfer of a text file across threads in a pipeline, that could result in out-of-order extraction if ADF supports that.
+7TimeXtender will load text files in file order (at least when using the Business Unit approach). In practice this means order from a text file is preserved. This is not guaranteed however, so if there is no field that can be used to order the data you are depending on the current behaviour of SQL Server's bulk import implementation.
As DW_Id is a sequence number, this can be used to sort after data ingestion. It would be better if the source system can output an explicit sorting criterion as that does not depend on implicit behaviour and would be robust against parallel ingestion scenarios.
I do not know if Azure Data Factory can split the transfer of a text file across threads in a pipeline, that could result in out-of-order extraction if ADF supports that.
Thanks Rory.
To anyone reading - have you dealt with this in the past? We are unfortunately not able to get the vendor providing the extracts to include a row number and the order of the rows is very important in our processing of the data.
We are considering using a shell script to add line numbers before loading but testing this is proving to be very slow (using powershell in a windows environment).
Cheers, Mark
Hi Mark - was this finally resolved?
I was just "passing by" and wondered if each source row does not have a full DTG (of the activity carried out in creating each row), thereby creating a unique row ID? Each row would have granularity down to the "second" of the DTG value, if that's enough?
Just a thought.
Cheers,
Jon.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
