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.
Advertisement
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 :