I want to import a CSV file to a database table using the SSIS package. The problem is the CSV file does not have trailing commas if the cell value is blank/null.
Id,City,State,Zip
1,NYC,New York,12345
2,LONDON
3,PARIS
4,LA,California,67890
Ideally, it should have been:
2,LONDON,,
3,PARIS,,
SSIS Flat file connection only checks {CR}{LF} on the last column. Does anyone know any workaround to import this data using SSIS (i.e. without a manual workaround)?
My personal choice would be to do what Bill suggested in his comment. Pre-process the input file with a script task, which will add any missing commas to act as placeholders for your Flat File Source.
There are scenarios where this is not possible however. In such a case you could use the Script Component tool in your data flow task set up as a Source. A very good approach to what needs to be in that script can be found here:
This method is very flexible as it would deal with rows that have less number of columns coming in, as well as the cases where more columns might appear.