New columns dont show up in SCD Type 2 -> Detect Changes

[dirks]

I am using Data Integration Studio 4.9. I am using a SCD Type 2 transformation and the source table has new columns, but I can't see them in the "Detect Changes" tab of the transformation.

Do I need to create them manually in the target table first?

[RahulG]

Answer is yes.

Ensure your key column list is correct.

[dirks]

Thanks!

Is there a fast and easy way to do it or do I need to write a PROC SQL statement by hand? I have to add a lot of new columns and writing them manually would be very tedious.

[RahulG]

If you want to add new columns in Table metadata.

Go to Table properties ---> Columns Tab ( you will see all columns present in table)

In column Tab, there are two option, 1. Add new columns. 2. Import columns.

Use these two options to add column in Table metadata. It will speed your work.

[RahulG]

First two options highlighted in yellow should help you.

[dirks]

I somehow doesnt work.

This is what I am doing:

1. Open Target Table

2. Columns -> Import Columns

3. Select Source Table -> Right Arrow to add all columns

4. Get a warning because of existing columns.

5. Click OK

6. Properties of SCD Type 2 Transformation -> Detect Changes -> Put all columns from left to right

7. Run SCD Type 2 Transformation

Now I get a warning for every new column I just added:

WARNING: The variable ACCOUNTID in the DROP, KEEP, or RENAME list has never been referenced.

Did I miss anything?

[RahulG]

Have you listed right set of column in business key tab.

I am guessing ACCOUNTID should be listed in business key

[dirks]

The business key is SERVICEID. ACCOUNTID was just an example. I get a warning for every new column.

The imported columns are gone now aswell. They dont show up anymore in the "Detect Changes" tab.

[Patrick · Accepted solution]

There are 2 parts to this question.

1. On metadata level:

 - propagate and map the new columns from source to target.

 - Once done you will have these columns available for change tracking.

2. On physical table level: 

 - if you can drop the physical table. The DIS job will then re-create the table based on your table metadata definition with all the new columns as soon as you run it again.

- if you can't drop the table: The SCD2 loader doesn't add new columns "automatically" to the physical table so you will need to write a once off job with a SLQ alter table statement and add all these columns scripted. For all new columns which then are under type 2 change tracking a new version will be added to the target table as soon as you load new data (because the new columns will be NULL/missing initially).