One of the regular issues that is discussed on the SSIS forum is what to do when a source file changes the order of columns. You build your SSIS package to handle Col1, Col2, Col3 and you get the files with the following order Col2, Col3, Col1. Or worse, you get Col2, Col1. How do you handle this case? It usually involves some nasty .Net code.
But why not process the file using PowerShell? In previous posts (listed at the end), I suggest that one can use PowerShell to preprocess a comma delimited file, sorting the result, removing columns and filtering the results. The same technique can reorder the columns, even add a column.
Here is my base file.
ID,Name,State,Country 1,Russ,Sandys,Bermuda 2,Katie,Texas,USA 3,Gail,Sandys,Bermuda 4,Melanie,Oregon,USA 5,Stephanie,Arizona,USA
I expect to find columns in the following order: ID, Name, State, Country.
But sometimes the downstream process provides you with the following formats:
Country,ID,Name,State Bermuda,1,Russ,Sandys USA,2,Katie,Texas Bermuda,3,Gail,Sandys USA,4,Melanie,Oregon USA,5,Stephanie,Arizona
Or worse:
ID,Name,State 1,Russ,Sandys 2,Katie,Texas 3,Gail,Sandys 4,Melanie,Oregon 5,Stephanie,Arizona
Either of these will break the import.
Using PowerShell, I can very easily reformat the data to expected format:
cd C:\MyFolder\ Import-Csv -Path SampleData.csv ` | SELECT ID, Name, State, Country ` | ConvertTo-CSV -NoTypeInformation ` | % {$_ -replace ` '\G(?^|,)(("(?