Conditional Data Validation In SSIS

I have a situation where table has two main columns DataFieldName & DataFieldValue along with some identifier such as OrderNumber.

Now in DataFieldName there is value named as “OrderDate” and respective dates are coming in DataFieldValue.

But some of the value for “OrderDate” are coming as non date values. I need to validate such non date value based on the condition where DataFieldName has value as “OrderDate” then validate the DataFieldValue for valid date in SSIS.

Answer

You can split your data using a Conditional Split :

or a query with a condition if you are using SQL :

SELECT DataFieldName, DataFieldValue FROM yourTable
WHERE DataFieldName LIKE 'OrderDate'

If you are using SQL Server :

SELECT   
    CASE WHEN TRY_CONVERT(date, DataFieldValue) IS NULL   
    THEN 'Cast failed'  
    ELSE 'Cast succeeded'  
END AS Result 
FROM yourTable

If you are using Oracle :

SELECT cast(DataFieldValue AS NUMBER DEFAULT NULL ON CONVERSION ERROR) FROM yourTable

Or, you can use a Data Conversion Transformation component :

Then you can redirect the output to a flat file for example :