Skip to main content

v20.10.35.64

I just noticed there’s a bug in the current version of our ERP related to the UpdateDate & UpdateTS field which impacts incremental loading. Hence, I'm investigating the possibilities to set up a semi-incremental loading infrastructure. With my TX version I can also set up the following, which is limited: 

 

My database has data ranging from 2013-today. I'm looking to find a way to load 2013-2023 just once and then only full load the data with a UpdateDate >= 01-01-2024. I'm thinking to achieve this the following way:

  1. Set up a Query Table for every table where UpdateDate >= 01-01-2024.
  2. Set up a Load task, LOAD_FULL which has the original tables
  3. Set up a Load task, LOAD_INCREMENTAL, which has the Query table with just data from 2024.
  4. Execute the LOAD_FULL task just once.
  5. Schedule & Execute LOAD_Incremental every 30 minutes. 

If I do it this way, it's not a classic incremental loading process.

  1. Can I still utilize TimeXtenders ODX/DSA incremental loading functionalities this way? 
  2. Would the scenario above work and is this the most optimal solution for this?

 

 

Hi,

 

we had a similar problem with large tables and solved it by cloning the data source and creating a “historical copy” of the old data.

  1. Data_Source_H:
    • Create Transfer job with data selection rule (no incremental rule) with data 2013 - 2023
    • Run this once and never touch it again :)
    • You can even do several “sub-runs” for example 2013 - 2018, 2019 - 2023 and then move the data into one ODX table, if these tables are really large
  2. Data_Source_I:
    • Create Transfer job with incremental selection rule with data >= 01.01.2024
    • Run this in a job to get fresh data consistently
  3. In the MDW you can just map these two tables into one table

Hope this helps!

Cheers, Kai

 


Hi @KCMT 

Does the above comment answer your question? If so please help us by marking the best answer above. If you have any follow up questions please let us know


Hi @KaiMueller and @Christian Hauggaard 

When I try to add a Data Selection rule on my DSA table I keep getting the error:

String was not recognized as a valid DateTime.

I tried multiple rules, in different formats, e.g. 2024-01-01, 01-01-2024, with quotation marks, without quotation marks, with and without timestamp but with no luck. Do you guys have an idea by any change?

The column for the rule is a DateTime type, but in reality just a date with a 0 timestamp 

Finished executing project 'MagnaTyres' Execution Package 'Execution Queue Default'
Execution failed
Start Time : 3/11/2024 2:54:16 PM End Time : 3/11/2024 2:55:09 PM on server: DXP-1538-TXS02-
-Execute Execution Package Execution Queue Default 'Failed'
    System.AggregateException: One or more errors occurred. ---> System.FormatException: String was not recognized as a valid DateTime.
       at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
       at ODX.Common.DataTypes.DataTypeConverters.ConvertToFormattedData(UniversalDataTypes universalDataType, Object rawData, Int32 subtractValue, DataTypeConverterSettings settings)
       at ODX.Common.DataTypes.DataTypeConverters.GenerateQualifiedValue(UniversalDataTypes universalDataType, Object value, Int32 subtractValue, DataTypeConverterSettings settings)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GenerateDataFlowQualifiedValue(ColumnModel columnModel, Object value, Int32 subtractValue)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpression(QueryExpression expression, ColumnModel columnModel)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionStatement(QueryExpression expression, TableModel tableModel, Dictionary`2 columnNameModelIds)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionSetStatement(QueryExpressionSet expressionSet, TableModel tableModel, Dictionary`2 columnNameModelIds)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionSetsStatement(IEnumerable`1 expressionSets, TableModel tableModel, Dictionary`2 columnNameModelIds)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GeneratDataFlowFilter(DiscoveryHubSQLExecutionModel transferModel, TableModel tableModel, Dictionary`2 columnNameModelIds)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2DiscoveryHubExecution.GenerateDataFlowScript(DiscoveryHubSQLExecutionModel transferModel, TableModel tableModel, List`1 columnMappings)
       at ODXConnectorEngine.ConnectorEngine.<>c__DisplayClass6_0.<ExecuteTable>b__0(DiscoveryHubSQLExecutionMethod transferMethod)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2StorageEngine.RunDiscoveryHubTransferMethod(Action`1 action)
       at ODXConnectorEngine.ConnectorEngine.ExecuteTable(IDataStorageEngine storageEngine, DataSourceMetaStructure sourceStructureModel, DataStorageModel storageModel, DataSourceModel dataSourceModel, DiscoveryHubSQLExecutionModel transferModel)
       at ExecutionEngine.Action.OutboundTransferAction.Execute()
       at ExecutionEngine.Action.ExecutionAction.<.ctor>b__9_0()
       at System.Threading.Tasks.Task.Execute()
       --- End of inner exception stack trace ---
    ---> (Inner Exception #0) System.FormatException: String was not recognized as a valid DateTime.
       at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
       at ODX.Common.DataTypes.DataTypeConverters.ConvertToFormattedData(UniversalDataTypes universalDataType, Object rawData, Int32 subtractValue, DataTypeConverterSettings settings)
       at ODX.Common.DataTypes.DataTypeConverters.GenerateQualifiedValue(UniversalDataTypes universalDataType, Object value, Int32 subtractValue, DataTypeConverterSettings settings)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GenerateDataFlowQualifiedValue(ColumnModel columnModel, Object value, Int32 subtractValue)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpression(QueryExpression expression, ColumnModel columnModel)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionStatement(QueryExpression expression, TableModel tableModel, Dictionary`2 columnNameModelIds)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionSetStatement(QueryExpressionSet expressionSet, TableModel tableModel, Dictionary`2 columnNameModelIds)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionSetsStatement(IEnumerable`1 expressionSets, TableModel tableModel, Dictionary`2 columnNameModelIds)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GeneratDataFlowFilter(DiscoveryHubSQLExecutionModel transferModel, TableModel tableModel, Dictionary`2 columnNameModelIds)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2DiscoveryHubExecution.GenerateDataFlowScript(DiscoveryHubSQLExecutionModel transferModel, TableModel tableModel, List`1 columnMappings)
       at ODXConnectorEngine.ConnectorEngine.<>c__DisplayClass6_0.<ExecuteTable>b__0(DiscoveryHubSQLExecutionMethod transferMethod)
       at DataStorageEngine.DataLakeGen2.DataLakeGen2StorageEngine.RunDiscoveryHubTransferMethod(Action`1 action)
       at ODXConnectorEngine.ConnectorEngine.ExecuteTable(IDataStorageEngine storageEngine, DataSourceMetaStructure sourceStructureModel, DataStorageModel storageModel, DataSourceModel dataSourceModel, DiscoveryHubSQLExecutionModel transferModel)
       at ExecutionEngine.Action.OutboundTransferAction.Execute()
       at ExecutionEngine.Action.ExecutionAction.<.ctor>b__9_0()
       at System.Threading.Tasks.Task.Execute()<---
    
    Details:
    
    System.AggregateException: One or more errors occurred. ---> System.FormatException: String was not recognized as a valid DateTime. ...
    Module: timeXtender
    System.Exception
       at TimeXtender.DataManager.StepTransferSSISExecute.DoDataWarehouse(StepSetup stepSetup, VariableResolveObject dynamicResolveObject)
       at TimeXtender.DataManager.StepTransferSSISExecute.DoAbstractStep(StepSetup stepSetup)
    
Execute Table DSA DSA.ORDR:Info:Initialized
  -Execute Table DSA DSA.ORDR
Execute DSA DSA.ORDR ODX Transfer:Error:Failed
    -Execute DSA DSA.ORDR ODX Transfer 'Failed'
        System.AggregateException: One or more errors occurred. ---> System.FormatException: String was not recognized as a valid DateTime.
           at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
           at ODX.Common.DataTypes.DataTypeConverters.ConvertToFormattedData(UniversalDataTypes universalDataType, Object rawData, Int32 subtractValue, DataTypeConverterSettings settings)
           at ODX.Common.DataTypes.DataTypeConverters.GenerateQualifiedValue(UniversalDataTypes universalDataType, Object value, Int32 subtractValue, DataTypeConverterSettings settings)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GenerateDataFlowQualifiedValue(ColumnModel columnModel, Object value, Int32 subtractValue)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpression(QueryExpression expression, ColumnModel columnModel)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionStatement(QueryExpression expression, TableModel tableModel, Dictionary`2 columnNameModelIds)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionSetStatement(QueryExpressionSet expressionSet, TableModel tableModel, Dictionary`2 columnNameModelIds)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionSetsStatement(IEnumerable`1 expressionSets, TableModel tableModel, Dictionary`2 columnNameModelIds)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GeneratDataFlowFilter(DiscoveryHubSQLExecutionModel transferModel, TableModel tableModel, Dictionary`2 columnNameModelIds)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2DiscoveryHubExecution.GenerateDataFlowScript(DiscoveryHubSQLExecutionModel transferModel, TableModel tableModel, List`1 columnMappings)
           at ODXConnectorEngine.ConnectorEngine.<>c__DisplayClass6_0.<ExecuteTable>b__0(DiscoveryHubSQLExecutionMethod transferMethod)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2StorageEngine.RunDiscoveryHubTransferMethod(Action`1 action)
           at ODXConnectorEngine.ConnectorEngine.ExecuteTable(IDataStorageEngine storageEngine, DataSourceMetaStructure sourceStructureModel, DataStorageModel storageModel, DataSourceModel dataSourceModel, DiscoveryHubSQLExecutionModel transferModel)
           at ExecutionEngine.Action.OutboundTransferAction.Execute()
           at ExecutionEngine.Action.ExecutionAction.<.ctor>b__9_0()
           at System.Threading.Tasks.Task.Execute()
           --- End of inner exception stack trace ---
        ---> (Inner Exception #0) System.FormatException: String was not recognized as a valid DateTime.
           at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
           at ODX.Common.DataTypes.DataTypeConverters.ConvertToFormattedData(UniversalDataTypes universalDataType, Object rawData, Int32 subtractValue, DataTypeConverterSettings settings)
           at ODX.Common.DataTypes.DataTypeConverters.GenerateQualifiedValue(UniversalDataTypes universalDataType, Object value, Int32 subtractValue, DataTypeConverterSettings settings)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GenerateDataFlowQualifiedValue(ColumnModel columnModel, Object value, Int32 subtractValue)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpression(QueryExpression expression, ColumnModel columnModel)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionStatement(QueryExpression expression, TableModel tableModel, Dictionary`2 columnNameModelIds)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionSetStatement(QueryExpressionSet expressionSet, TableModel tableModel, Dictionary`2 columnNameModelIds)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GetExpressionSetsStatement(IEnumerable`1 expressionSets, TableModel tableModel, Dictionary`2 columnNameModelIds)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2Helper.GeneratDataFlowFilter(DiscoveryHubSQLExecutionModel transferModel, TableModel tableModel, Dictionary`2 columnNameModelIds)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2DiscoveryHubExecution.GenerateDataFlowScript(DiscoveryHubSQLExecutionModel transferModel, TableModel tableModel, List`1 columnMappings)
           at ODXConnectorEngine.ConnectorEngine.<>c__DisplayClass6_0.<ExecuteTable>b__0(DiscoveryHubSQLExecutionMethod transferMethod)
           at DataStorageEngine.DataLakeGen2.DataLakeGen2StorageEngine.RunDiscoveryHubTransferMethod(Action`1 action)
           at ODXConnectorEngine.ConnectorEngine.ExecuteTable(IDataStorageEngine storageEngine, DataSourceMetaStructure sourceStructureModel, DataStorageModel storageModel, DataSourceModel dataSourceModel, DiscoveryHubSQLExecutionModel transferModel)
           at ExecutionEngine.Action.OutboundTransferAction.Execute()
           at ExecutionEngine.Action.ExecutionAction.<.ctor>b__9_0()
           at System.Threading.Tasks.Task.Execute()<---
        
        Details:
        
        System.AggregateException: One or more errors occurred. ---> System.FormatException: String was not recognized as a valid DateTime. ...
        Module: timeXtender
        System.Exception
           at TimeXtender.DataManager.StepTransferSSISExecute.DoDataWarehouse(StepSetup stepSetup, VariableResolveObject dynamicResolveObject)
           at TimeXtender.DataManager.StepTransferSSISExecute.DoAbstractStep(StepSetup stepSetup)
        

No child steps
Execute DSA DSA.ORDR Data Cleansing Rules:Info:Initialized


Hi @KCMT 

I am unable to reproduce the issue you describe. Please see my setup below.

I have a table in my DSA with a selection rule on the modifiedDate field

When I deploy and execute the table, I get the following data

 

Do you notice any differences in our setups? Can you please send screenshots similar to the ones I have provided above?


Hi @Christian Hauggaard,

As per requested. Setup looks the same too me.

 

 

Finished executing project 'MagnaTyres' Execution Package 'Execution Queue Default'
Execution failed
Start Time : 3/25/2024 12:41:31 PM End Time : 3/25/2024 12:42:15 PM on server: DXP-1538-TXS02-
-Execute Execution Package Execution Queue Default 'Failed'
System.AggregateException: One or more errors occurred. ---> System.AggregateException: Pipeline execution failed ---> System.Exception: {
"errorCode": "ActionFailed",
"message": "Activity failed because an inner activity failed; Inner activity name: TIMEXTENDER COPY DATA, Error: The request failed with status code '\"BadRequest\"'.",
"failureType": "UserError",
"target": "TIMEXTENDER COPY DATA LOOP",
"details": ""
}
--- End of inner exception stack trace ---
at ODXConnectorEngine.DataFactory.DataFactoryHelper.ValidatePipelineRunResult(PipelineRun pipelineRun)
at ODXConnectorEngine.DataFactory.DataFactoryHelper.ExecutePipeline(Dictionary`2 pipelineParameters, Nullable`1 transferType)
at ODXConnectorEngine.ConnectorEngine.<>c__DisplayClass6_0.<ExecuteTable>b__0(DiscoveryHubSQLExecutionMethod transferMethod)
at DataStorageEngine.DataLakeGen2.DataLakeGen2StorageEngine.RunDiscoveryHubTransferMethod(Action`1 action)
at ODXConnectorEngine.ConnectorEngine.ExecuteTable(IDataStorageEngine storageEngine, DataSourceMetaStructure sourceStructureModel, DataStorageModel storageModel, DataSourceModel dataSourceModel, DiscoveryHubSQLExecutionModel transferModel)
at ExecutionEngine.Action.OutboundTransferAction.Execute()
at ExecutionEngine.Action.ExecutionAction.<.ctor>b__9_0()
at System.Threading.Tasks.Task.Execute()
--- End of inner exception stack trace ---
---> (Inner Exception #0) System.AggregateException: Pipeline execution failed ---> System.Exception: {
"errorCode": "ActionFailed",
"message": "Activity failed because an inner activity failed; Inner activity name: TIMEXTENDER COPY DATA, Error: The request failed with status code '\"BadRequest\"'.",
"failureType": "UserError",
"target": "TIMEXTENDER COPY DATA LOOP",
"details": ""
}
--- End of inner exception stack trace ---
at ODXConnectorEngine.DataFactory.DataFactoryHelper.ValidatePipelineRunResult(PipelineRun pipelineRun)
at ODXConnectorEngine.DataFactory.DataFactoryHelper.ExecutePipeline(Dictionary`2 pipelineParameters, Nullable`1 transferType)
at ODXConnectorEngine.ConnectorEngine.<>c__DisplayClass6_0.<ExecuteTable>b__0(DiscoveryHubSQLExecutionMethod transferMethod)
at DataStorageEngine.DataLakeGen2.DataLakeGen2StorageEngine.RunDiscoveryHubTransferMethod(Action`1 action)
at ODXConnectorEngine.ConnectorEngine.ExecuteTable(IDataStorageEngine storageEngine, DataSourceMetaStructure sourceStructureModel, DataStorageModel storageModel, DataSourceModel dataSourceModel, DiscoveryHubSQLExecutionModel transferModel)
at ExecutionEngine.Action.OutboundTransferAction.Execute()
at ExecutionEngine.Action.ExecutionAction.<.ctor>b__9_0()
at System.Threading.Tasks.Task.Execute()
---> (Inner Exception #0) System.Exception: {
"errorCode": "ActionFailed",
"message": "Activity failed because an inner activity failed; Inner activity name: TIMEXTENDER COPY DATA, Error: The request failed with status code '\"BadRequest\"'.",
"failureType": "UserError",
"target": "TIMEXTENDER COPY DATA LOOP",
"details": ""
}<---

---> (Inner Exception #1) System.Exception: {
"errorCode": "BadRequest",
"message": "The request failed with status code '\"BadRequest\"'.",
"failureType": "UserError",
"target": "TIMEXTENDER COPY DATA",
"details": ""
}<---
<---

Details:

System.AggregateException: One or more errors occurred. ---> System.AggregateException: Pipeline execution failed ---> System.Exception: { ...
Module: timeXtender
System.Exception
at TimeXtender.DataManager.StepTransferSSISExecute.DoDataWarehouse(StepSetup stepSetup, VariableResolveObject dynamicResolveObject)
at TimeXtender.DataManager.StepTransferSSISExecute.DoAbstractStep(StepSetup stepSetup)

Execute Table DSA DSA.OJDT:Info:Initialized
-Execute Table DSA DSA.OJDT
Execute DSA DSA.OJDT ODX Transfer:Error:Failed
-Execute DSA DSA.OJDT ODX Transfer 'Failed'
System.AggregateException: One or more errors occurred. ---> System.AggregateException: Pipeline execution failed ---> System.Exception: {
"errorCode": "ActionFailed",
"message": "Activity failed because an inner activity failed; Inner activity name: TIMEXTENDER COPY DATA, Error: The request failed with status code '\"BadRequest\"'.",
"failureType": "UserError",
"target": "TIMEXTENDER COPY DATA LOOP",
"details": ""
}
--- End of inner exception stack trace ---
at ODXConnectorEngine.DataFactory.DataFactoryHelper.ValidatePipelineRunResult(PipelineRun pipelineRun)
at ODXConnectorEngine.DataFactory.DataFactoryHelper.ExecutePipeline(Dictionary`2 pipelineParameters, Nullable`1 transferType)
at ODXConnectorEngine.ConnectorEngine.<>c__DisplayClass6_0.<ExecuteTable>b__0(DiscoveryHubSQLExecutionMethod transferMethod)
at DataStorageEngine.DataLakeGen2.DataLakeGen2StorageEngine.RunDiscoveryHubTransferMethod(Action`1 action)
at ODXConnectorEngine.ConnectorEngine.ExecuteTable(IDataStorageEngine storageEngine, DataSourceMetaStructure sourceStructureModel, DataStorageModel storageModel, DataSourceModel dataSourceModel, DiscoveryHubSQLExecutionModel transferModel)
at ExecutionEngine.Action.OutboundTransferAction.Execute()
at ExecutionEngine.Action.ExecutionAction.<.ctor>b__9_0()
at System.Threading.Tasks.Task.Execute()
--- End of inner exception stack trace ---
---> (Inner Exception #0) System.AggregateException: Pipeline execution failed ---> System.Exception: {
"errorCode": "ActionFailed",
"message": "Activity failed because an inner activity failed; Inner activity name: TIMEXTENDER COPY DATA, Error: The request failed with status code '\"BadRequest\"'.",
"failureType": "UserError",
"target": "TIMEXTENDER COPY DATA LOOP",
"details": ""
}
--- End of inner exception stack trace ---
at ODXConnectorEngine.DataFactory.DataFactoryHelper.ValidatePipelineRunResult(PipelineRun pipelineRun)
at ODXConnectorEngine.DataFactory.DataFactoryHelper.ExecutePipeline(Dictionary`2 pipelineParameters, Nullable`1 transferType)
at ODXConnectorEngine.ConnectorEngine.<>c__DisplayClass6_0.<ExecuteTable>b__0(DiscoveryHubSQLExecutionMethod transferMethod)
at DataStorageEngine.DataLakeGen2.DataLakeGen2StorageEngine.RunDiscoveryHubTransferMethod(Action`1 action)
at ODXConnectorEngine.ConnectorEngine.ExecuteTable(IDataStorageEngine storageEngine, DataSourceMetaStructure sourceStructureModel, DataStorageModel storageModel, DataSourceModel dataSourceModel, DiscoveryHubSQLExecutionModel transferModel)
at ExecutionEngine.Action.OutboundTransferAction.Execute()
at ExecutionEngine.Action.ExecutionAction.<.ctor>b__9_0()
at System.Threading.Tasks.Task.Execute()
---> (Inner Exception #0) System.Exception: {
"errorCode": "ActionFailed",
"message": "Activity failed because an inner activity failed; Inner activity name: TIMEXTENDER COPY DATA, Error: The request failed with status code '\"BadRequest\"'.",
"failureType": "UserError",
"target": "TIMEXTENDER COPY DATA LOOP",
"details": ""
}<---

---> (Inner Exception #1) System.Exception: {
"errorCode": "BadRequest",
"message": "The request failed with status code '\"BadRequest\"'.",
"failureType": "UserError",
"target": "TIMEXTENDER COPY DATA",
"details": ""
}<---
<---

Details:

System.AggregateException: One or more errors occurred. ---> System.AggregateException: Pipeline execution failed ---> System.Exception: { ...
Module: timeXtender
System.Exception
at TimeXtender.DataManager.StepTransferSSISExecute.DoDataWarehouse(StepSetup stepSetup, VariableResolveObject dynamicResolveObject)
at TimeXtender.DataManager.StepTransferSSISExecute.DoAbstractStep(StepSetup stepSetup)


No child steps
Execute DSA DSA.OJDT Data Cleansing Rules:Info:Initialized

 


@KCMT I have created a support ticket for this.

 


@KCMT Can you please go to the Integration runtimes monitor in ADF and then click on the input and output arrows as shown below. This should display some text in json format. Can you please copy and send the input and output text in order for us to troubleshoot further?

 


closing this due to no response


Reply