Preventing Data Duplicating when Job get failed

[Former Member]

Hello,

How to prevent data duplicating when Job get failed in Data Services.

By using table comparison, Auto correct load we can avoid data duplicating but it would be performance issue.

What are the possible ways are existed to handle such kind of scenarios.

Santhosh

[former_member190054 · Accepted solution]

Hi Santhosh,

Use try catch functionality and inside catch write a sql statement to delete data for current load.

I hope this will helpfull for you.

Regards

Asgar

[former_member315492 · Accepted solution]

Hi,

Possible options

1. Add a script to the dataflow that prevents loading  duplicate records while re excuting a job.

2. Use option as "delete  data before loading option".

3. Identify the staging tables,delete the data in that tables and re run the job.

But we are using first option in my current project.

Regards,

Narasimha.

[Former Member · Accepted solution]

Hi Narasima,

Can you please explain the first option in Brief .

Santosh

[former_member315492 · Accepted solution]

Hi Santosh,

Add a script to Workflow and Write the below code

SQL('Data store name','alter table Table name activate not logged initially with empty table');

Regards,

Narasimha.

[Former Member]

Adding to the above points, there are some more methods including:

Data flow design

You can design the replication job to completely replace the target table each time the job is executed. You might choose to use this method if the number of rows changed in the target table is a significant percentage of the target table. You can also use techniques such as bulk loading options to improve the overall performance of the replication job

Pre-load SQL commands

You can use pre-load SQL commands to remove partial database updates that have occurred during

an incomplete job execution. Typically, the pre-load SQL command deletes rows based on a variable

set before the partial insertion began.

Thanks.

[Former Member]

Hi,

Kindly refer the chapter 19 "Recovery Mechanisms" in Designer guide 'sbo411_ds_designer_en' (page no: 645).

Thanks,

Parthiban