Base and data columns are different in Data Integration Studio, But really are not.

[DavidPhillips2]

I am setting up a slowly changing type 2 dimension in Data Integration Studio.  The first load comes from a union of two tables that have different lengths for a few columns.  I joined the tables together using a set operator and adjusted the length to 3.  When the final table is updated the warning WARNING: Variable DEGREE_LEVEL_CAT_CODE has different lengths on BASE and DATA files (BASE 2 DATA 3).  Is displayed and The table only displays the column with a length of 2.  All points of the datastep have an adjusted length of 3.  The two tables of the union have a character length of 2 and 3 but I set the length to 3.  I found a number of posts about this error in proc sql.  But I haven’t found one that relates to Data Integration Studio.  Its as if the length that I set in the job is being overridden back to the length of the characters in the smaller of the two unioned columns.

NOTE: Appending WORK.ETLS_MATCH to myschema.LOOKUP_952_DEGREE_LEVEL_TEMP.
WARNING: Variable DEGREE_LEVEL_CAT_CODE has different lengths on BASE and DATA files (BASE 2 DATA 3).

There seems to be some correlation with a source table having a longer length.  Each time I'm attempting to run this job i'm clicking fix

warning on the creation of a set operator.  It appears to actually do nothing but the job goes from stating that a table does not exist to running and creating the table with a length of 2 rather than three.  This appears to be a serious bug with Data Integration Studio.

I'm wondering if the bug is related to using a SCD from a chain of tables where the length of the column does not match the original source.

This might not even be related to the source length since one source length is 63 and the other source length is 3.  Where as the length is being truncated to the size of the actual data from the first unioned table which is 2.

[Patrick]

@DavidPhillips2 

Assuming there is no user written code involved then I can think of two scenarios why this happens.

A) Physical permanent data not in sync with metadata

If you applied the metadata change after the physical data got created and your process doesn't recreate this physical data then you can hit such a situation when executing the code (which executes against the physical data).

To resolve this issue you either need to drop your physical tables so that DIS recreates them based on the current metadata definitions or if that's not possible then you need to write and execute a SQL ALTER table statement against your physical data to bring them back into sync with what's in metadata.

B) A change to DIS metadata doesn't "stick"

I've encountered situations with DIS where changes in metadata didn't "stick". You can test this by closing and reopening your DIS job. If after reopening the metadata is still in the state as before then you don't have this problem. If it changed back to before then you can try to fix metadata via SMC (analyze/repair). DIS in its current version shouldn't really have such glitches anymore but.... I've had situations with previous versions where I've ended up to just delete the affected transformation and then rebuild stuff.

[DavidPhillips2]

A) I can't alter a transaction table.  

B) I remade the DIS job.  After I remade it the job threw warning messages similar to before but for different columns and with rather crazy random base lengths.  For example.  I got the message:

WARNING: Variable DEGREE_CODE has different lengths on BASE and DATA files (BASE 24 DATA 6).

However the data has a max length of 6 and transaction_table_1.degree_code has a length of 63 and transaction_table_2.degree_code has a length of 4.  The 24 seems random.

I'm wondering if I'm using the set operator wrong.  My setup is Transaction_table _1 and Transaction_table_2 feed into set operator.  Since the column lengths are different in the two transaction tables but the actual data is the same length.  I set the length in the set operator.  Set operator is fed into a slowly changing type 2 dimension.  The type 2 dimension feeds the actual dimension table.

I could experiment with creating intermediate real tables after unions rather than work tables.  Although if that's the route that I need to take its highly inefficient to build the process that way. I've also tried using substr with no luck.

When you say these things are fixed now what do you mean?  I'm on version 4.902.

[DavidPhillips2 · Accepted solution]

SAS explained the scenario.  The final target of my process was created the first time as length 2 so the length was truncated.  To lower the length of the union I turned off auto propagation and used substr in a expression.