Hi All,
I am new to DM.
I have a process flow with 2 data flows and 1 procedure to update CUST_NAME column in target table.

In FIRST_DATA_FLOW , I am getting source data from oracle and load the data in MS SQL server stage table Table_stg_1 (new target) .

In SECOND_DATA_FLOW, I am using Table_stg_1 table and loading the data into production table Table_Target_1(existing target) in the same MS SQL server .

After SECOND_DATA_FLOW, I have a procedure to update CUST_NAME column as "None" for null values using below code.

  
SQL SQLMSS SET SERVER CONN_1
SQL SQLMSS
UPDATE  Table_Target_1
SET
	CUST_NAME = 'None'
WHERE 
	YEAR_MTL >= '201901' and  (CUST_NAME IS NULL OR CUST_NAME  ='')
END
SQL SQLMSS COMMIT WORK
END

Previously this flow worked fine. But now I have a issue.

My issue is,
After running the above process flow, CUST_NAME column has only 'None' for all the rows in Table_Target_1 table . When I check the source table (Table_stg_1), it's showing the valid values for CUST_NAME.

Then I checked the sample data option from SECOND_DATA_FLOW >> Column Selection window(SQL Columns), Sample data also showing values for CUST_NAME column.
But I couldn't figure out why Table_Target_1 table has only "None" in CUST_NAME column.

My assumption is CUST_NAME filed is loaded as null for all rows while loading the target table.

I Can't rerun this process flow in production to test the data load. So I replicated the same process flows and logic with Test table and run the process flow. when I check the test target table, CUST_NAME column is updated correctly. But I don't know why it's not updated correctly in production table.

Can anyone please help me how to debug these kind of issues in DM?

This message has been edited. Last edited by: Maran,

Hi Maran

It's difficult to debug procedures, that's why I prefer to have all my logic in a data flow. Is this an intermittent issue in production?


Why does the CUST_NAME update have to happen after the SECOND_DATA_FLOW has executed? I would use COALESCE(Table_stg_1.CUST_NAME, 'None') function in the SECOND_DATA_FLOW to transform the value to what you want before inserting it in the target table

You might have to use a nullif function to cater for '' values. Try COALESCE(NULLIF(Table_stg_1.CUST_NAME, ''), 'None')

Hi Tsepe,

Thanks for your Inputs. We have some cleanup process in the CUST_NAME field after the SECOND_DATA_FLOW. after that only we do the None update.

But here the issue is CUST_NAME column not mapped correctly. I added the new column for testing and saved the flows. After that CUST_NAME column loaded correctly in target table. somehow the column remapped correctly and loaded after saving the flows. may be a glitch in column mapping and the issues is solved now.

But your point is taken. I'll try to implement my logic inside the flows as much as possible.

Thanks once aging!!!!