I want to migrate the data to a target table. However, I want to make a reject file for null values and values whose size exceeds 20 characters. As I do with Conditional Splitting? I did that but it doesn’t work:
"if len(mail)>10 caractère"
i will export this values to reject file
How can i do this Please ?
Advertisement
Answer
Design
You can do this directly in a conditional split but I advise against doing so. Instead, compute the boolean (true/false) condition in a Derived Column and add that to your data flow. Then, if you get unexpected results, you can add a data viewer between the Derived Column step and the Conditional Split
Implementation
Add a Derived Column to the data flow. Add a new column called BadMail. If it’s true, then we’ll route to the bad file. If it’s true, it will proceed to the destination.
The Expression language for SSIS will use the ternary operator (test) ? true_condition : false_condition
I am going to test for null ISNULL(mail), longer than 20 len(mail) > 20 and zero length len(mail) == 0.
The || is a logical or so if any of those three conditions are true, then we need to set the BadMail to true
(ISNULL(mail) || len(mail) > 20 || len(mail) == 0) ? true : false
You could simplify that to eliminate the ternary operator but I find being explicit in my intentions helpful in these situations. As a side note, if you are still having issues with unexpected results, add a preceding Derived Column transformation and add a column in for each criteria (null, 0 or greater than 20 character) and then you can inspect them individually.
Now, we add the Conditional Split
The expression here is just our new column BadMail and that will route to Output Path 1 or whatever you name it. The good mail will pass through to the default output path.