This will help you to understand how to make use of the different options available in the target table while loading data.

Below is the descriptions of all the options with the sample data.

Auto Correct Load:

Auto correct loading ensures that the same row is not duplicated in a target table

Input Data Screen Short:

IMG36.PNG

Target Option Window: Following steps need to be done

IMG1.png

Uncheck the Delete sata from Table before Loading

Before Updating in Update Control:

IMG2.png

After Updating:

IMG3.png

Now go to Source Data and add two Duplicate rows

Before Updating:

IMG4.png

After Updating:

IMG5.png

After Creation of Jobs in Data Services Designer

IMG6.png

Now Apply Auto Correct Load on Target Tables

IMG7.png

Note: After apply Auto Correct Load Duplicate values are removed from Target Tables

Now Execute the Job

IMG8.png

Output:

IMG9.png

Ignore Column Option:

If a matching row exists, the row is updated depending on the values of Ignore columns with value, and Ignore columns with null:

Ignore Columns Options are Worked with Auto Correct Load:

When the Ignore columns with null option is set to Yes and the column data from the source is NULL, then the corresponding column in the target table is not updated. Otherwise, the corresponding target column is updated as NULL since the source column is NULL.

Now Go To Target Table Properties:

Before Update in the Target Table Properties:

IMG19.png

After Update in the Target Table Properties:

IMG16.png

Now Source Data Information:

IMG29.png

Now create Jobs in Data Services Using Job-Work Flow->Data Flow-> Source & Target and make EMP_ID as a Primary key

IMG22.png

View of Source table Before Loading:

IMG14.png

View of Source table After Null Values Before Loading:

IMG15.png

Now update in the Target Table before loading the data

IMG11.png

Then Finally Execute the Job

IMG8.png

Output after executing the job:

IMG18.png

Ignore Column with Value Option:

If a matching row exists, the row is updated depending on the values of Ignore columns with value, and Ignore columns with null:

When the column data from the source matches the value in Ignore columns with value, the corresponding column in the target table is not updated. The value may be spaces. Otherwise, the corresponding column in the target is updated with the source data.

Now Go To Target Table Properties:

Before Update in the Target Table Properties:

Before Update in the Target Table Properties:

IMG19.png

After Update in the Target Table Properties:

IMG20.png

Now Source Data Information:

IMG21.png

Now create Jobs in Data Services Using Job-Work Flow->Data Flow-> Source & Target and make EMP_ID as a Primary key

IMG22.png

View of Source table Before Loading:

IMG23.png

Now update in the Target Table before loading the data

IMG20.png

Then Finally Execute the Job

IMG8.png

Output after executing the job:

IMG26.png

Update Key Columns in Update Control:

Now Go To Target Table Properties:

Before Update in the Target Table Properties:

IMG19.png

After Update in the Target Table Properties:

IMG28.png

Now Source Data Information:

Before Update:

IMG29.png

After Update in Source Table:

IMG30.png

Now create Jobs in Data Services Using Job-Work Flow->Data Flow-> Source & Target and make EMP_ID as a Primary key

IMG31.png

View of Source table Before Loading:

IMG23.png

Now update in the Target Table before loading the data

IMG33.png

Then Finally Execute the Job

IMG8.png

Output after executing the job:

IMG35.png

To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

  1. dilip kumar

    Good explanation ankit ..keep post this kind of document’s

    and one thing i’m not able to get “COMMENT” option in all post…Y?

    (0) 
  2. dilip kumar

    I know how to generate “.bat” files and where it store…,here my dought is y bat file require.

    checked more blogs they were mention executing the job through designer instead of scheduler.

    Once complete the job we can execute in designer itself right then y again exporting from management console.

     

    (0) 

Leave a Reply