I have a flat file in Excel which I want to import through the SSIS Wizard or create my own SSIS package from scratch.
I need to transform this file in two ways into a table:
1) Add a column called 'Region' to the right of a column called 'Code' and based on the type of code in that row add the specific region. For example, if code is '2452' then add region 'North West'.
2) Add a column called 'Type' to the right of a column called 'Amount' and if the amount for that row is a negative figure then add 'Liability' for type and if that amount for that row is a positive figure then add 'Asset' for type.
I'm even thinking about the logistics. Is it better to import the flat file and then perform the transformation in the table or perform it during the import?