BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dirks
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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).

 

 

View solution in original post

8 REPLIES 8
RahulG
Barite | Level 11

Answer is yes.

Ensure your key column list is correct.

dirks
Quartz | Level 8

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
Barite | Level 11

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
Barite | Level 11

First two options highlighted in yellow should help you.

DI_table_option.png

dirks
Quartz | Level 8

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
Barite | Level 11

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

 

I am guessing ACCOUNTID should be listed in business key

dirks
Quartz | Level 8

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
Opal | Level 21

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).

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 1702 views
  • 0 likes
  • 3 in conversation