Data Integration Studio Compare Tables transformation compare columns containing missing values

[dtjon]

I'm comparing 2 tables with the Compare Tables transformation in Data Integration Studio.

The source table ia a SQL Server table. The comparison table is an Oracle table.

The match key is the ID column (primary key in both tables). With this I can find new records to insert.

In the section "Compare columns" I select all other fields to compare. With this I can find changed records that I want to update in the Oracle table.

My problem is that the "Compare columns" always mark ALL RECORDS as changed. I think it's because of null values (=missing values). The comparison sees a null values in the SQL Server table as different from a null value in the Oracle table.

As a test I have loaded both tables into a physical SAS dataset an compare both SAS datasets. But the result is still: all records differ.

So how to solve the problem of comparing a SQL Server table with an Oracle table containing null values?

[LinusH]

I default NULL behaviour is in place, they would be stored as MISSING in SAS. If this is the same from both sources, there is no difference. So it's probably something else that differs/happens. Try the HEX./$HEX. formats to see what is actually in those columns.

Then I wonder about the "Compare tables" transformation, I've never used it. If you need to find out what records to update/insert you would be fine with Table Loader or SCD Type 2 Loader transformations.

[dtjon · Accepted solution]

Thanks for your advice Linus.

I compared 1 records between the table in SQL Server and Oracle in HEX./$HEX.

The HEX comparison showed no difference, but the transformation incorrectly marked the record as 'changed'.

Then I excluded the columns containing null values in the comparison.

Still the transformation marked the record incorrectly as changed.

So the null values were not the cause for marking the records as 'changed'.

The next step was debugging by comparing the columns one by one.

The result from debugging was that this comparison is the cause of incorrect marking as 'changed':

   Sql Server column WERKVOORRAADSPECIALARRANGEMENTS

   compared with …

   Oracle WERKVRDSPECIALARRANGEMENTS

In my EXTRACT transformation I changed target column name WERKVOORRAADSPECIALARRANGEMENTS (31 characters) into WERKVRDSPECIALARRANGEMENTS (26 characters), and ... now the compare mark the record correctly as 'unchanged'.

So this is definately a bug within DI Studio.

[LinusH]

God that you found the cause.

If you think it's a bug report it to SAS tech support.