SSIS Excel error column

1
votes

I am importing an excel file and grabbing data from it. In a handful of string columns, some rows have the value of #VALUE!. How would I filter these rows out in SSIS? I have tried checking for NULL values, as well as checking to see if that row's column is equal to #VALUE!

([ALT_TYPENAME] == "#VALUE!")

Attempt to filter out "#VALUE!"

However, the rows pass right through and are not filtered at all. Is it even possible to filter for these? The columns are imported as DT_STR.

2
sql-serverexcelssis
When the rows pass through, what do they get written as at the destination? - Tab Alleman
They do not make it to the destination, due to a conflict with a foreign key. #VALUE! is not in the db as a foreign key, and neither is NULL, so it fails and aborts. - Acorn
Can you add a DataViewer (on the path immediately out of the excel source) and check what value shows up there? This way you will know if it actually shows up as "#VALUE!" or something else and take appropriate action This is how you add a dataViewer -- msdn.microsoft.com/en-us/library/ms140318.aspx - whereisSQL
@whereisSQL I have that, and the value that appears is #VALUE! - Acorn

2 Answers

2
votes

Ok, you need to change the order in your conditional split. First you are checking if ISNULL == True, then ISNULL == False.

One of those two conditions will always be true, so the row will be sent down that path, and the third condition ( == "#VALUE!") will never be evaluated.

Try evaluating your last condition first.

2
votes

You can do this by using a Conditional Split transform in between your Excel source and your destination.

Create an object variable (I name mine Discard) and a Recordset Destination based on that variable. Set your Conditional Split's condition to Column == "#VALUE!" and direct anything that meets that criteria to the Recordset to discard it, while everything else follows the default path to your Destination.

If you need to discard based on multiple columns potentially containing "#VALUE!" just expand the condition to an OR that encompasses all of the columns.

Example Output

An added benefit of this technique is you can use the Discard Recordset at the end of the job to create a fall out report if you need one.