Process multiple derived-column functions

Question

I have an SSIS task which imports a flat file into sql.

I have two types transformations to make, the first removes a hyphen from a column, and the second removes a decimal place from a different column.

The issue I'm facing is that the hyphen operation needs to be applied to 50 columns, and the decimal place operation to 100 columns.

Is there a quicker way to do this than simply creating an expression for all 150 columns?

The columns are sequentially named, i.e. col1, col2, col3 etc

Thanks

You can create a script component to do the modifications. It is easy to navigate through those columns by code. However i am not sure if there is a way to create the output column automatically, which is painful to do for such a high number of columns — cfrag
I just need to replace the column.. column1 = Replace(Column1, "-",""). Do you have a link on how to create a script component — Matt

cfrag cfrag · Accepted Answer · 2012-02-29T13:03:58

If you fancy doing some programming, you can create a custom transformation component.

In the following link they explain how to do so: http://www.sqlis.com/sqlis/post/The-Script-Component-as-a-Transformation.aspx

However, keep in mind that you will need to define all the output columns and i do not know if there is an easy way to create those automatically. This might be a problem for you since you have a table with several tens of columns

Process multiple derived-column functions

1 Answers