CODEWITHSUNDEEP
sqlsql-serverssisetl
Rahul Neekhra
Rahul Neekhra

Reputation: 810

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.

Upvotes: 0

Views: 384

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

You can split your data using a Conditional Split :

enter image description here

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 :

enter image description here

Then you can redirect the output to a flat file for example : enter image description here

Upvotes: 1

Related Questions