Duplicated derived column logic

Question

I need to import a series of CSV files. They have about 30 columns, most of which are supposed to be numeric. The process that generates them uses "NA" to indicate NULL values and "Inf" for infinite (divide by zero issues), which should be converted to 99999.

I created a derived column transform for the first column, which was simple enough, but now the only way that I can think of to use it for the other columns is to copy/paste then alter the code 30 times. Then I would need to remap everything of course.

Is there a simpler way? Some way to just say, "Apply this logic to all of these columns". I'm at the point where I just want to import the data into a table of all NVARCHAR() columns and do the work in T-SQL, but it seems like SSIS should be able to handle this more elegantly.

Thanks!

Answer 1

Without BIML, there's no easy way to programatically apply a rule to all columns. Although, you could use a script task inside the data flow, I think this would be more work than needed.

Why not use 1 derived column transform with all 30 columns inside it? You're not limited to just 1 per transform. If I apply the same logic to multiple columns, I do it all in one transform. And in most of my cases, I can REPLACE the column (that's an option in the transform) instead of "Add as New Column" which would most likely prevent you from having to remap everything.